Find Method (Skipping Rows)

[maggie]

I am running a Find Method and need help skipping over the headings in my spreadsheet. Partial Code is Below. I want to make the Find method skip over Row 2 (which is the heading row for a table). I considered using the "After" varient for the Find Method (Find(What, After, LookIn etc)) however I can't seem to get it to work. Any help would be much appreciated.





[vba]Dim ws As Worksheet, myvar As String, val1 As Range
Dim val2 As Range, tmp As Range, cnt As Integer
cnt = 0
myvar = InputBox("Please Enter a Keyword:")
If myvar = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
Set val1 = ws.Cells.Find(What:=myvar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)
If Not val1 Is Nothing Then
cnt = cnt + 1
Application.Goto val1
ActiveCell.EntireRow.Select
Selection.Copy
Worksheets("Home").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste[/vba]

Thank You!

~Maggie

[mohanvijay]

try this

 Set val1 = ws.Range(2 & ":" & Rows.Count).Find(What:=myvar, LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)

[maggie]

Mohanvijay,

Thank you, unfortunately that doesn't seem to work for me. I have uploaded part of the file to look at. Command Button "New Search" brings up a message box. If you search 'Program' (one of the heading titles) It goes into a loop of copying the heading. What I would like to avoid. I did not add the other sheets for confidentiality purposes.

~Maggie

[shrivallabha]

You are correct about 'After' part. Use it like:
[vba]Set val1 = ws.Range(2 & ":" & Rows.Count).Find(What:=myvar, After:=ws.Range("C1"), LookIn:=xlValues, Lookat:=xlPart, MatchCase:=False)[/vba]

[p45cal]

It didn't work because
ws.Range(2 & ":" & Rows.Count)
includes row 2. Perhaps change the 2 to a 3.

but this still wouldn't work because if the string was found where you want to searcdh for it, when you do the next find:
Set val2 = ws.Cells.FindNext(After:=val1)
you're searching the whole sheet again. So this too needs to be:
Set val2 = ws.Range(3 & ":" & Rows.Count).FindNext(After:=val1)

You probably want to exclude searching the Home sheet, as the number of times the search term occurs in it is likely to increase - this might cause looping.

Your method of deciding which row to paste to on the Home sheet is a bit flaky. I suggest, since you're always starting at row 3 you have a variable such as DestRow, initially holding 3, but incremented every time you paste a row, and use that to determine which row to paste stuff to.

Some Qs:
1. I see a filter on the Home sheet, if there are filters active on other sheets, do you want to search hidden cells too? At the moment, you're not.

2. Version of Excel?

3. Do you want to skip ONLY row 2, that is are you happy that suggested code also skips row 1?

[maggie]

p45cal~

Putting ws.Range(3 & ":" & Rows.Count) on both val1= and val2= fixed my problem.

Excel 2003

I originally had multiple worksheets that it was searching through (thus the For Each) Now I only have one sheet for it to search through. Can I just take out the For Each? to make it only go through my Sheet2?

And Yes, I only want to skip row 2. Row 1 is fine to search or skip through as it is Empty. which ever is easier.

I have taken the AutoFilters off.

Thank you so much for your help

~Maggie

[p45cal]

I originally had multiple worksheets that it was searching through (thus the For Each) Now I only have one sheet for it to search through. Can I just take out the For Each? to make it only go through my Sheet2?

Replace the For Each line with:
Set ws = Sheet2
if you're using the codename of the sheet, or:
Set ws = Thisworkbook.sheets("Sheet2")
if you're using the sheet's tab name.

Also remove the corresponding Next statement lower down.

If in the future, you want to start searching multiple sheets again use:
[vba]For Each ws in ThisWorkBook.worksheets
If ucase(ws.name) <> "HOME" then

'existing loop contents here

End if
Next ws[/vba] which will skip processing the Home sheet.

And Yes, I only want to skip row 2. Row 1 is fine to search or skip through as it is Empty. which ever is easier.

If you're sure you never want to return a find on row 1 then leave it as it is.