Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro Search over multiple Columns, Worksheets and return the row to main page.

0

Hi 

I should start by explaining that I am new to VBA setting up Macro's on Excel.

I reviewed your Fast Search Entire Excel Workbook & Return All Results into a Dashboard (https://www.youtube.com/watch?v=DOW3SjBjg6I) video and found it be very helpful and allowed me to set up the Macro. However, I need to search over 3 columns (noting to complicated just a single word in each). I have tried playing with it but I cannot get it to work. Can you advise how I can set this up, if it is possible?

I have pasted the code that i am using in a test set up. The columns that would need to be searched are animal (3 and 4). I have attached the workbook but it just basic so I could play with the code. 

Kind regards

Ben

Sub Data_search()

Dim ws As Worksheet
Dim Main As Worksheet
Dim dataArray As Variant
Dim DatatoShowArray As Variant

Set Main = Sheets("Main")

dataColumnStart = 1
dataColumnEnd = 5
dataColumnWidth = dataColumnEnd - dataColumnStart
DataRowStart = 2
MainDataColumnStart = 2

searchValue = Main.Range("C4").Value
fieldValue = Main.Range("E4").Value

Call Clear_Data

If (fieldValue = "Number") Then
    SearchField = 1
ElseIf (fieldValue = "Person") Then
    SearchField = 2
ElseIf (fieldValue = "Animal") Then
    SearchField = 3


End If

'Loop information and transfer data
For Each ws In Worksheets
    If (ws.Name <> "Main") Then

        dataArray = ws.Range(ws.Cells(DataRowStart, dataColumnStart), ws.Cells(ws.Cells(Rows.Count, dataColumnStart).End(xlUp).Row, dataColumnEnd)).Value

        ReDim DatatoShowArray(1 To UBound(dataArray, 1), 1 To UBound(dataArray, 2))
        j = 1
        For i = 1 To UBound(dataArray, 1)
            If (dataArray(i, SearchField) = searchValue) Then
            For k = 1 To UBound(dataArray, 2)
                DatatoShowArray(j, k) = dataArray(i, k)
            Next k
            j = j + 1
        End If
    Next i
    nextRow = Main.Cells(Rows.Count, MainDataColumnStart).End(xlUp).Row + 1
    Main.Range(Cells(nextRow, MainDataColumnStart), Cells(nextRow + UBound(DatatoShowArray, 1) - 1, MainDataColumnStart + dataColumnWidth)).Value = DatatoShowArray

End If

Next ws

End Sub

excel
posted by: Kilnerb on Wed Jul 7, 2021 at 9:13 am
Answer
Discuss

Discussion

Please attach your workbook to your question so that we adjust your code to match your data setup. You can add the workbook in Edit mode.
Variatus (rep: 4889) Jul 7, '21 at 10:06 pm
Add to Discussion

Answers

0

Please find attached a revised version of your workbook. It's half done because at that point I got stuck.

The tutor thought of columns named "Number", "Person" and "Animal" which he thought would be columns 1, 2 and 3. So, by specifying a "Field" by the column caption "Animal" he would in fact specify the 3rd column to search in. He would then proceed to search that column in all worksheets and find all occurrences of "Cat" - only on that column.

You, on the other hand, have named your columns "Test1", "Test2" and "Test3". There would be no problem to expand the list to include "Test4" and "Test4" but none of your column names appear in the code. Therefore none of them can be found or searched. 

Moreover, "Cat" appears in more than one column. Therefore none of your columns would fit the description "Animal". And, of course, it isn't clear at all what you want to extract once you found a match.

I think the question is all wrong. You are asking how to adapt the code from the tutorial to your needs without describing what your needs are. The sheet names "April" and "May" open the door to your perhaps wanting to search for dates. That, or searching for numbers, would require a different method than searching for text.

Perhaps you play with the attached workbook a little, to get the feel of it. Then you should both, make your data more like what you really want to do and adapt to the tutorial's idea of specifying search "fields" (meaning columns).

posted by: Variatus (rep: 4889) on Thu Jul 8, 2021 at 6:36 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login