excel,vba,excel-vba , Excel Search VBA macro

Excel Search VBA macro


Tag: excel,vba,excel-vba

I have been given the task of searching through a large volume of data. The data is presented identically across around 50 worksheets. I need a macro which searches through all these sheets for specific values then copies certain cells to a table created in a new workbook. The macro also needs to create the table headings when it is run.

It must Search column G For the Value 9.1 Then certain information must be copied to corresponding columns in the table

If it is a hassle to create the new workbook with these column headings then I would be quite happy to create the headings myself in the worksheet and just have the macro search for and copy the data to the rows corresponding to the headings.

If any help or backup files are needed I would be more than happy to provide these.

the code I have at the moment is based on a userform also ideally I would do away with this and just search all sheets

    Public Sub createWSheet(module, srcWBook)
        Dim i

        i = 0
        srcWB = srcWBook
      For Each ws In Workbooks(srcWBook).Worksheets
            i = i + 1
            If ws.Name = module Then
                MsgBox ("A worksheet with for this module already exists")
                Exit Sub
            End If
        Next ws

        Worksheets.Add after:=Worksheets(i)
        ActiveSheet.Name = module
        Cells(2, 2) = "FHA Ref"
        Cells(2, 3) = "Engine Effect"
        Cells(2, 4) = "Part No"
        Cells(2, 5) = "Part Name"
        Cells(2, 6) = "FM ID"
        Cells(2, 7) = "Failure Mode & Cause"
        Cells(2, 8) = "FMCN"
        Cells(2, 9) = "PTR"
        Cells(2, 10) = "ETR"

        Range(Cells(2, 2), Cells(2, 10)).Font.Bold = True
        Range(Cells(1, 2), Cells(1, 10)) = "Interface"
        Range(Cells(1, 2), Cells(1, 10)).MergeCells = True
        Range(Cells(1, 2), Cells(1, 10)).Font.Bold = True
    End Sub
Dim mainWB, srcWBook
Dim headerLeft, headerTop, headerBottom, headerRight
Dim nTargetFMECA, nPartID, nLineID, nPartNo, nPartName, nQTY, nFailureMode, nAssumedSystemEffect, nAssumedEngineEffect
Dim item As String
Dim mDest
Dim selections(100)

Public Sub controlCopyFMs(mWB, sWB, module)
    Dim i

    mainWB = mWB
    srcWBook = sWB
    mDest = 2

    nTargetFMECA = 0
    nPartID = 0
    nLineID = 0
    nPartNo = 0
    nPartName = 0
    nQTY = 0
    nFailureMode = 0
    nAssumedSystemEffect = 0
    nAssumedEngineEffect = 0

    For i = 0 To TestForm.LBSelected.ListCount - 1
        Call copyFMs(module, selections(i))
    Next i
End Sub

    Public Sub copyFMs(module, comp)
        Dim mSrc

        If exploreHeader(comp) = 0 Then
            Exit Sub
        End If

        mSrc = headerBottom + 3

        While Cells(mSrc, nSrc).Text <> ""
            If Cells(mSrc, nIndication).Text <> "-" Then
                If Cells(mSrc, nIndication).Text <> "" Then
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 2) = Cells(mSrc, nTargetFMECA).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 3) = Cells(mSrc, nPartID).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 4) = Cells(mSrc, nLineID).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 5) = Cells(mSrc, nPartNo).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 6) = Cells(mSrc, nPartName).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 7) = Cells(mSrc, nQTY).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 8) = Cells(mSrc, nFailureMode).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 9) = Cells(mSrc, nAssumedEngineEffect).Value
                    Workbooks(mainWB).Worksheets(module).Cells(mDest, 10) = Cells(mSrc, nAssumedSystemEffect).Value
                    mDest = mDest + 1
                End If
            End If
            mSrc = mSrc + 2
    End Sub

    Public Function exploreHeader(comp)
        Dim m, n

        m = 1
        n = 1

        While ((InStr(1, Cells(m, n).Text, "Engine Programme:", vbTextCompare) <= 0) Or (InStr(1, Cells(m, n).Text, "BR700-725", vbTextCompare) <= 0)) And n < 10
            If m < 10 Then
                m = m + 1
                n = n + 1
                m = 1
            End If

        headerTop = m
        headerLeft = n

        While StrComp(Cells(m, n).Text, "ID", vbTextCompare) <> 0 And StrComp(Cells(m, n).Text, "Case No.", vbTextCompare) <> 0
            m = m + 1
        headerBottom = m - 1

        While Cells(m, n).Borders(xlEdgeBottom).LineStyle = xlContinuous
            n = n + 1
        headerRight = n - 1

        m = headerTop
        n = headerLeft
            If n > headerRight Then
                n = headerLeft
                m = m + 1
            End If

            If InStr(1, Cells(m, n).Value, "Item No.:", vbTextCompare) > 0 Then
                item = Right(Cells(m, n).Value, Len(Cells(m, n).Value) - InStr(1, Cells(m, n).Value, ":", vbTextCompare))
                Cells(m, n).Select
                Exit Do
            End If

            n = n + 1
        Loop While m <= headerBottom

        m = headerBottom + 1
        n = headerLeft
        While n <= headerRight
            If StrComp(Cells(m, n).Value, "ID", vbTextCompare) = 0 Then
                nID = n
            End If

            If StrComp(Cells(m, n).Value, "Mitigation", vbTextCompare) = 0 Then
                nMitigation = n
            End If

            If StrComp(Cells(m, n).Value, "Remarks", vbTextCompare) = 0 Then
                nRemarks = n
            End If

            If StrComp(Cells(m, n).Value, "FMCN", vbTextCompare) = 0 Then
                nFMCN = n
            End If

            If StrComp(Cells(m, n).Value, "Indication", vbTextCompare) = 0 Then
                nIndication = n
            End If

            If StrComp(Cells(m, n).Value, "Crit", vbTextCompare) = 0 Then
                nFMCN = n
            End If

            If StrComp(Cells(m, n).Value, "Detect", vbTextCompare) = 0 Then
                nIndication = n
            End If

            If StrComp(Cells(m, n).Value, "Functional Description", vbTextCompare) = 0 Then
                nMitigation = n
            End If

            n = n + 1
        exploreHeader = 1
    End Function

    Public Sub initSelections()
        For i = 0 To 99
            selections(i) = ""
        Next i
    End Sub

    Public Sub loadSelection(comp, i)
        selections(i) = comp
    End Sub

    Public Sub deleteSelection(i)
        While selections(i) <> ""
            selections(i) = selections(i + 1)
            i = i + 1
    End Sub


I hope this can help more. This code may not work 100% but it should be good enough to guide you. Let me know if you have questions.

Dim WS As Worksheet
Dim Results(7, 1000000) As String ''Didn't know what is a good data type or how many possible results
Dim ColValue() As Variant
Dim I, II, ResultCt As Long

ResultCt = 0

For Each WS In ActiveWorkbook.Worksheets ''This should get all your result and information into the Results Array

    ColValue = ActiveSheet.Range(Cells(2, 7), Cells(WS.UsedRange.Rows.Count, 7)).Value ''This put all of column G into an array

    For I = 0 To UBound(ColValue)
        If ColValue(I, 1) = "9.1" Then
            Results(0, ResultCt) = Cells(I + 1, 7).Value ''I think it is off by 1, but if not remove the +1
            Results(1, ResultCt) = Cells(I + 1, 6).Value
            Results(2, ResultCt) = Cells(3, 10).Value
            Results(3, ResultCt) = Cells(2, 3).Value
            Results(4, ResultCt) = Cells(I + 1, 2).Value
            Results(5, ResultCt) = Cells(I + 1, 3).Value
            Results(6, ResultCt) = Cells(I + 1, 3).Value
            ResultCt = ResultCt + 1
        End If

Next WS

''At this point us your code to create the worksheet and name it ''starting from the line Workbooks(srcWBook).Activate

''Then Set the Active cell to where ever you want to start putting the data and have something like

For I = 0 To UBound(Results, 2)
    For II = 0 To UBound(Results)
        ActiveCell.Offset(I, II).Value = Results(I, II) ''This assumes you put the information into Result in the order you want it printed out


Comparing cell contents against string in Excel

Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

Using a cell's number to insert that many rows (with that row's data)

I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

VBA how to initialize vCPath

How do I initialize vCPath? VBA Run-Time Error 1004 @Garry's Student says I "must somehow use the info you get from Application.FileDialog(msoFileDialogOpen) to generate the full filespec of the file you wish to open". What's the simplest way to do this? I am a VBA beginner: I have been programming...

VBA data type to store Range().Characters()

I can't find a data type to store Characters from a Range. I already tried the following code, but nothing happened. I think the problem is because of wrong data type. Dim chars As Characters chars = Range("A2").Characters(0, 4) MsgBox chars.Text ...

VBA - Unable to pass value from Private to Public Sub

I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

How to insert excel formula to cell in Report Builder 3.0

There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

adding variables into another variable vba

Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

Excel - Pulling data from one cell within a list

I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

Creating a Range in VBA

I'm working on my first VBA function. The goal is to have a function that accepts two integers as inputs, and outputs an array containing all the integers in between the two inputs (end-points included). Example: If I input 5 and 9, the output should be an array of 5,...

Converting column from military time to standard time

I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

Using VLOOKUP formula or other function to compare two columns

I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

How do I do to count rows in a sheets with filters? With a suppress lines

I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

Excel VBA Loop Delete row does not start with something

I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file

I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

12 Characters Including leading and following zeros

I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

Excel - select a cell based on adjacent cell value

I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

I need help setting the RecordSource of a Report within a VBA Function

Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

Replace reference with its value in Excel VBA workbook

I have an Excel workbook to manage my Delivery Notes, which creates another workbook with actual delivery note and stores it in the folder for me, then places new record in my main workbook so that the Delivery Number for the next delivery note can be increased by 1. My...

Activecell not in Array

Having difficulty comparing my activecell against a predefined array. After importing data from another source I want to trim out the columns I don't need I've trued the array as both undefined, string and variant And tried activecell / activecell.value <> NeededColumns No joy :( Sub Trim() Dim NeededColumns As...

Extract All Named Ranges Into A Class

I have a workbook with a very large amount of named ranges (well over 200). I really need a way to work quickly and easily with all of the named ranges so I can then work with / populate them using VBA. My solution up until now has been to...

Using date in CreateQueryDef

I have a table on which I run a query that I export to Excel. Here is an example: Table: Food Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 |...

VBA “Compile Error: Statement invalid outside Type Block”

I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Removing Alert When Using DeleteFile API

I'm writing a VBA application which involves looping a large number of directories recursively. I am using the FindFirstFile API to to achieve this, as it offers a substantial performance boost over the FileSystemObject. In order to remove the FSO from my code entirely, I need a routine to delete...

Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

All I want to do is copy a range from Excel and then paste this range into PowerPoint. When my range is manually copied from Excel to the clipboard... If I right click on a blank slide when pasting into PowerPoint, it gives me the option to paste "using destination...

Interface Controls for DoEvent in Excel

I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

Identifying cell in Openpyxl

I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

How do you delete favorite folders in outlook using VBA

I wish to delete all the folders from the outlook favorites then subsequently replace them, but the delete doesn't seem to work. What's wrong with my code. Setup Objects works fine ' Get the "Favorite Folders" navigation group Set favGroup = Application.ActiveExplorer.NavigationPane.Modules.GetNavigationModule(olModuleMail).NavigationGroups.GetDefaultNavigationGroup(olFavoriteFoldersGroup) This works Set inboxFldr = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox) favGroup.NavigationFolders.Add (inboxFldr)...

Copying sheet to last row of a sheet from another workbook

I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

Userform is not unloading once command button is pressed

Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i...

Excel VBA Program Code by Using Randomize Timer

What is wrong in below program code, why not returning correct result. Whenever i put any number in excel cell (1,1) and run the program, Numbers changed automatically, also not throwing a correct result in cell (1,2). Please Help as i learning VBA. Private Sub Sheet2_1() Dim num1 As Integer...

Converting ADODB Loop into DAO

Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

If cell value starts with a specific set of numbers, replace data

My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

excel search engine using vba and filters?

I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

Using a stored integer as a cell reference

Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

timestamp SQL to Excel

If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Excel-VBA: create named Range in row until end of cell content

Imagine an Excel sheet with some rows and some content in each row (i.e. different column-length for each row). In Excel-VBA: How can I create a range-X within column-X that goes from row-cell-2 to the end of the content of this X-column ?? i.e. I would like to create a...

Exit Sub And Call another Sub

I have two subs, sub1 and sub2. I want to exit sub1 completely and start sub2 if a condition is satisfied. My attempt is (running from : sub1 . . If x=y Then Exit Sub And Call sub2 End If . . End Sub ...