Current code searches entire worksheet, need to search just A column of multiple workbooks

[Mark Dynes]

Hi Guys,

Merry Christmas to all and Happy New Year. Struggling to get a bit of code to search just column A for numbers only and then display what is offset from what it finds. This is the code I have used for specific searches such as "SM" and "DP". I have done a test search for the number "1" but it brings back everything with a 1 in it in every column. I have also tried changing the range to "A16:A36" but that I get the same result. Any and all advise very welcome.

Please Login or Register  to view this content.

[Doc.AElstein]

Hi Mark Dynes ,
Best wished to you in the New Year also.
This is probably your problem:

Set rFound = wks.Cells.FindNext(After:=rFound)

This will find the next with the same search criteria as before, but .Cells returns a Range object consisting of the entire cells in the worksheet, so that is the range that will then be further searched.

You probably want to replace that with

Set rFound = wks.Range("A:A").FindNext(After:=rFound)

Alan

[mjr veverka]

Please Login or Register  to view this content.

What do you need it for ?



Edit:

try:

Please Login or Register  to view this content.

[Norie]

If you want to search a range for numeric values try taking a look at SpecialCells.

Please Login or Register  to view this content.

[sintek]

Why not upload a sample file so that we can assist in the best possible code for your scenario...

[Mark Dynes]

Hi Doc.AElstein, thanks for the tip. This works much better for the time being.

Thanks so much for the help!! Have a great 2018. Hope to speak to you again.

Regards
Mark

[Mark Dynes]

Thanks also to everyone else. I will give the SpecialCells option a go as well, thanks Norie.

[Doc.AElstein]

Thanks Mark for the feedback and Rep thingy, ( and for marking the Thread as Solved )
Dave Alan