expanding VBA fieldvalue to include all columns

0

Good day,

After going through the video

Fast Search Entire Excel Workbook & Return All Results into a Dashboard

(Excellent by the way), and playing with the code for a bit, it will work quite well for my needs except, in the user input, I don't have the capability of identifying fields/columns.

My data, spread out across several tabs, includes much of the same info, but organized differently depending on the worksheet.

Ex: Using your test data, the ID column in one sheet might be in the 8th column, and the 12th column in another sheet.

In this case, how would I approach this section of the code:

'Figure out by which field we will search.
If (fieldValue = "ID") Then
    searchField = 1
ElseIf (fieldValue = "Name") Then
    searchField = 2
End If

Are their special characters in VBA  that represent wildcard? Like we could say "searchfield = *", and it will look for fieldValue = "ID" across all columns of all the sheets rather than just looking up the searchField = 1 column of each sheet in the workbook?

*Edit

It didn't seem to work, got a syntax error, wondering though if I tinkered with the code too much and might just need to pull it back. This is the current state of the whole thing:

Sub Data_Search()

' TeachExcel.com



Dim ws As Worksheet

Dim dashboard As Worksheet

Dim dataArray As Variant

Dim datatoShowArray As Variant



'Application.ScreenUpdating = False 'Turning off for the macro can speed things up - not so much here though.



'Dashboard sheet

Set dashboard = Sheets("Dashboard")



'Data table information

dataColumnStart = 1

dataColumnEnd = 16

dataColumnWidth = dataColumnEnd - dataColumnStart ' Number of columns for the raw data (+1 not included because it makes lower calculations more confusing)

dataRowStart = 2

dashboardDataColumnStart = 7 ' Column for the data on the dashboard



'Get user input

searchValue = dashboard.Range("a2").Value

fieldValue = dashboard.Range("b2").Value



'Clear Dashboard

Call Clear_Data



'Loop through the worksheets

For Each ws In Worksheets



Select All

set searchFieldRange = ws.Range(1:1).Find(fieldValue)

searchField = searchFieldRange.Column



'Figure out by which field we will search.

If (fieldValue = "ID") Then

    searchField = 1

ElseIf (fieldValue = "Name") Then

    searchField = 2

End If





    'Ignore the Dashboard worksheet

    If (ws.Name <> "Dashboard") Then



        'Get the range values into a variable that can be looped through.

        'Example usage: dataArray(1,1) [row,column]

        'Simple version: ws.Range(Cells(1,1),Cells(2,2)).Value

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



        'Increase size of array that will hold the data to display to its max possible size for the current worksheet.

        ReDim datatoShowArray(1 To UBound(dataArray, 1), 1 To UBound(dataArray, 2))



        'Row increment for the final data array (datatoShowArray).

        j = 1



        'Loop through the rows in the data range.

        For i = 1 To UBound(dataArray, 1)



            'Check if the value in the row equals our search value

            If (dataArray(i, searchField) = searchValue) Then



                'MATCH FOUND! Now do something!



                'Loop through the columns in the data range so can get their values.

                For k = 1 To UBound(dataArray, 2)



                    'Add values to the array that will be used to put data into the Dashboard.

                    datatoShowArray(j, k) = dataArray(i, k)



                Next k



                'Increment the counter for the datatoShowArray

                j = j + 1



            End If



        Next i



        'Find next empty row in the dashboard.

        nextRow = dashboard.Cells(Rows.Count, dashboardDataColumnStart).End(xlUp).Row + 1



        'Put data into the dashboard.

        'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray

        dashboard.Range(Cells(nextRow, dashboardDataColumnStart), Cells(nextRow + UBound(datatoShowArray, 1) - 1, dashboardDataColumnStart + dataColumnWidth)).Value = datatoShowArray



    End If



'Go to the next worksheet.

Next ws



'Application.ScreenUpdating = True 'Turn it back on at the end of the macro!



End Sub



Sub Clear_Data()



'Dashboard sheet

Set dashboard = Sheets("Dashboard")



'Data table information

dashboardDataColumnStart = 7 ' Column for the data on the dashboard

dashboardDataRowStart = 2



dashboard.Range(dashboard.Cells(dashboardDataRowStart, dashboardDataColumnStart), dashboard.Cells(Rows.Count, Columns.Count)).Clear



End Sub


Note that I removed the Field button on the Dashboard, so perhaps their is some remaining code linked to that action that is catching on something?

Also wondering about this snippet and if anything needs to be updated here:

'Get user input

searchValue = dashboard.Range("a2").Value

fieldValue = dashboard.Range("b2").Value
Answer
Discuss

Discussion

I think you can now remove entirely this section:
'Figure out by which field we will search.
 
If (fieldValue = "ID") Then
 
    searchField = 1
 
ElseIf (fieldValue = "Name") Then
 
    searchField = 2
 

End If


I'm quite tired at the moment but I think that this update should get you closer.
don (rep: 1900) Jul 10, '20 at 3:28 pm
Add to Discussion

0

Glad you liked the tutorial! I forgot how much effort I put into that one.

This macro is very customized so thank you for trying yourself first to fix the issue and correctly identify where changes need to be made.

It seems like the best way to fix this is to add some code here that simply searches for the fieldValue using the .Find method

Find Method Excel VBA

The value returned by that method will be a range and you can store it in a variable and then use yourVariable.Column to get the returned column number and then store that in the searchField variable.

You would want to move this .Find code and the code you pasted within the worksheet loop, so after this line:

'Loop through the worksheets
For Each ws In Worksheets

Then use .Find like this:

set searchFieldRange = ws.Range(1:1).Find(fieldValue)
searchField = searchFieldRange.Column

1:1 in the range is for the row that has the field id in it.

I didn't test this and I just wrote it out here, but this should get you going.

Discuss

Discussion

Wow, you're the author of the video, that's awesome! I'm checking out a bunch of other videos too as the quality is just great.

Thank you so much for helping, i'll be trying it in a few minutes, but first just to confirm, adding the code you included, do I need to remove the snippet I quoted "'Figure out by which field we will search." ? Also other question that would help alot - Is there a way to expand this to lookup multiple values at once? For example, using your material in the video - having either multiple Search buttons, or 1 Search button that runs the macro for several values at once, something like: copy/paste a column of ID's in the Dashboard (111, 112, 113, 114, etc) run the macro, and it will display all found values across all sheets for each of those user entries. Or can you only run one query at a time? 
table10 Jul 10, '20 at 11:21 am
Yea that's me haha. I'm glad you like the videos! You can basically do anything you want but this code is setup to run for a single value at the moment. First, get it working for a single value; after that, it will be much easier to make it work for multiple values. Edit your question and upload a sample file with your macro in it and some sample data and it will be easier to help because it will be easier to see the errors.

I probably won't be able to respond for a couple days beyond the comment I am about to leave under your main question right now.
don (rep: 1900) Jul 10, '20 at 3:25 pm
Add to Discussion


Answer the Question

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