Hi everyone,
Need help developing VBA code to do a bit of a complicated search.. well for me it was complicated. I can’t do it.
I have a spreadsheet with 8 sheets. A sheet named “Search” and then 7 others named $1 thru $7 representing the 7 days of the week.
So I need to search the 7 sheets for specific data, which I enter on the Search sheet in Row 4. The code should then go to each sheet looking for that data, and return the contents of the row where the match is found depending however on certain criteria which I also set, in row 3.
I have set up an example search in the attached spreadsheet. So in the example I would like to search for specific data in the columns.. I enter the search criteria in row 4, M thru AC. Then mark the Deviation criteria for each column… A number in the deviation cell means to deviate plus or minus that mount. A “0” means match that number exactly, and a blank cell means nothing.. no need to match or deviate.. take what is found. The main number to search for is the number is Column X, highlighted in RED. In the example, I have a deviation of .021 set for the number 1.551. So any a number in the range of 1.530 to 1.572 is found, and if the other criteria is met, like any number that match in that row then the row would be returned. The search should go to each of the 7 sheets. There could be up to 1000 entries on each sheet to search. So going by the search criteria set, first a number within the .021 deviation set for column X must be found, next the three numbers marked with a “0” columns Q, S and T must also match, and two numbers within the deviation ranges of .02 and .1 must be met before the row can be returned. Ignore the numbers with blank deviations.
After a search is complete, if another search is performed with different data, the results from second search would get added below the previous search results for each of the pages.
I hope my instructions make sense.. I just don’t know VBA well enough to make this work, Im sorry. Need expert help to do this. I greatly appreciate any all help given.. Thank you so much.
Dave
Need help developing VBA code to do a bit of a complicated search.. well for me it was complicated. I can’t do it.
I have a spreadsheet with 8 sheets. A sheet named “Search” and then 7 others named $1 thru $7 representing the 7 days of the week.
So I need to search the 7 sheets for specific data, which I enter on the Search sheet in Row 4. The code should then go to each sheet looking for that data, and return the contents of the row where the match is found depending however on certain criteria which I also set, in row 3.
I have set up an example search in the attached spreadsheet. So in the example I would like to search for specific data in the columns.. I enter the search criteria in row 4, M thru AC. Then mark the Deviation criteria for each column… A number in the deviation cell means to deviate plus or minus that mount. A “0” means match that number exactly, and a blank cell means nothing.. no need to match or deviate.. take what is found. The main number to search for is the number is Column X, highlighted in RED. In the example, I have a deviation of .021 set for the number 1.551. So any a number in the range of 1.530 to 1.572 is found, and if the other criteria is met, like any number that match in that row then the row would be returned. The search should go to each of the 7 sheets. There could be up to 1000 entries on each sheet to search. So going by the search criteria set, first a number within the .021 deviation set for column X must be found, next the three numbers marked with a “0” columns Q, S and T must also match, and two numbers within the deviation ranges of .02 and .1 must be met before the row can be returned. Ignore the numbers with blank deviations.
After a search is complete, if another search is performed with different data, the results from second search would get added below the previous search results for each of the pages.
I hope my instructions make sense.. I just don’t know VBA well enough to make this work, Im sorry. Need expert help to do this. I greatly appreciate any all help given.. Thank you so much.
Dave