[Solved] stuck in excel vba

[hannibal lecter]

hello
from your exceldemy YouTube Link:-

it's about Create a Search Box for Multiple Sheets in Excel

when I try to modifiy the range for example

Searched_Ranges = Array("B5:F23", "B5:F23")
to
Searched_Ranges = Array("B6:F28", "B5:F23")

I got an erorr 1004 or sometime error 9

what is the solution for that please

 

[shamimarita]

Hello Hannibal Lecter,

The issue likely arises from the Last_Row and Last_Column lines in the SearchMultipleSheets code. Here’s how to troubleshoot:

Adjust Last_Row and Last_Column Calculation: Ensure these lines calculate properly if your data extends to or past the specified range. You may also add If conditions to check if End(xlDown) or End(xlToRight) exceeds bounds.

Check Searched_Ranges Syntax: Double-check that "B6:F28" and "B5:F23" are set correctly. VBA may not handle range extensions consistently if any Searched_Sheets don’t match the expected dimensions.

 

[hannibal lecter]

Hello Hannibal Lecter,

The issue likely arises from the Last_Row and Last_Column lines in the SearchMultipleSheets code. Here’s how to troubleshoot:

Adjust Last_Row and Last_Column Calculation: Ensure these lines calculate properly if your data extends to or past the specified range. You may also add If conditions to check if End(xlDown) or End(xlToRight) exceeds bounds.

Check Searched_Ranges Syntax: Double-check that "B6:F28" and "B5:F23" are set correctly. VBA may not handle range extensions consistently if any Searched_Sheets don’t match the expected dimensions.

I have 3 sheets each one has random colomns number
For example sheet name has employee names and birthday second sheets has employee but with additional colomns and some sheets without the name just email and national and another sheets with different colomns so how to search on a content and show his complete table without showing more colomns on the another sheet

 

[shamimarita]

Hello,

To search across multiple sheets with varying columns in Excel VBA, follow these steps:

• Loop Through Sheets: Use a For Each loop to go through all sheets where you need to search.
• Identify Columns: Check for column positions dynamically. If column positions vary, you can use Find or Match to locate the desired columns.
• Search with Find: Instead of referencing specific columns, use the Find method to locate the search term.
• Adjust the Range: After finding the term, dynamically adjust the range to match the specific column layout of the current sheet.
• Output: Store results and display them accordingly.

This approach ensures your search adapts to varying sheet structures. If you need further assistance, let me know!