Excel VBA Course

(35% Sale Ends Jan. 26)

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 (35% Discount)

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

0

How to ge the search values vertically rather than horizontally? 

The output should be :

ID

NAME 

REGION 1

Rather than 

ID  NAME REGION 1

What changes are required in the below code to get the output vertically?

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

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

Answer
Discuss

Answers

0
Selected Answer

Nigel

In the modified file attached, I've changed one variable to say which column (rather than row) the data should start from. I then let the macro gather data as before. Once that's done (for a worksheet), the macro finds the next free column in row 10 and pastes the same array datatoShowArray but it's (perhaps sneakily) transposed using the function:

Application.WorksheetFunction.Transpose(datatoShowArray)
The changes to  macro are in bold in the extract below (full code in the file):
Sub Data_Search()
' TeachExcel.com

.....
...... << code unchanged>>
......
......          
dashboardDataRowStart = 10 ' Row for the data on the dashboard
......
...... << other code unchanged>>
......
......          
        Next i

        'Find next empty column in the dashboard.
        nextColumn = dashboard.Cells(dashboardDataRowStart, Columns.Count).End(xlToLeft).Column + 1

        'Put data into the dashboard.
        'Format = Range(Cells(1,1),Cells(2,2)).Value = datatoShowArray
        dashboard.Range(Cells(dashboardDataRowStart, nextColumn), Cells(dashboardDataRowStart + dataColumnWidth, nextColumn + UBound(datatoShowArray, 1) - 1)).Value = Application.WorksheetFunction.Transpose(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
I also modified the data to clear the dashboard, as follows (change in bold):
Sub Clear_Data()

'Dashboard sheet
Set dashboard = Sheets("Dashboard")

'Data table information
dashboardDataColumnStart = 3 ' Column for the data on the dashboard
dashboardDataRowStart = 10 ' Changed for vertical headers

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

End Sub
Hope this works for you.
Discuss

Discussion

thats brilliant it works. Thank you for the quick and detailed response. I have another question for which il raise it in the forum 
Nigel (rep: 6) Dec 26, '21 at 1:59 pm
Glad it worked for you. Thanks for selecting my answer Nigel.
John_Ru (rep: 2867) Dec 26, '21 at 3:27 pm
Add to Discussion


Answer the Question

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