using variables in "Find Function"

[vassili]

i have this variable totalPages. it's a single digit number that varies book from book. this variable can be found in colmun BD, but the row is always different in each book.

i need to create a function that can find the string


totalPages & "OF" & totalPages


this code doesn't work

Cells.Find(What:=totalPages & "OF" & totalPages).Activate

the main point is, within the workbook, i want to find the last page based on its 'based on its page number. my program already knows how many total pages there are by parsing cell BD4. the varibiable totalPages will be asigned the last digit in BD4...thus TOTAL PAGES.

because the form is in chinese, there is no way for me to easily search strings of text. so all i can do is search by #. the form comes in 2 basic flavors. one with the last page "info sheet" as seen in the attatchment (which varies in legnth), and one without an info sheet.

i need to compare the last page to the previous ones to see if in fact it is different or not so i can add those highlighted rows accordingly. the info sheet does not get extra rows under column "BN".

the way i see it, the only difference that is easy to spot would be under column a. while the regular pages under coln a will contain either a "#" ie. "23" or "#alphabet" ie. "24a" the info sheet will only contain alphabets at this time ie. "A"

i was thinking of comparing those values to see if the last page is indeed an info page or not. i figure if i can find where the last page starts, i can then see if there's a number under "coln a" or an alphabet.

i can't count on the fact that the A in coln a on the info page is exactly 6 rows down from ("page: totalPage"OF"totalPage) as the person who inputs this stuff sometimes fudges the rows and it turns out it's only 3 rows down or 7 rows down.

can't just do a search for "A" either as it could appear elsewhere in the form. what do i do???

[rbrhodes]

Hi vassili,

The Find command is actually very versatile. This found total pages row for me. Thenit looks for letter "A" below that.



Option Explicit
Sub Of()
Dim roe As Long
Dim roeA As Long
Dim totalpages As Long
'set variable for this test
totalpages = 4
'find "4OF4"' Lookat:= xlpart
roe = Cells.Find(What:=totalpages & "OF" & totalpages, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Row
'Find letter "A" AFTER "4OF4" look for whole "word",
'note that Lookat:= xlWhole, not xlPart as above
roeA = Cells.Find(What:="A", After:=Cells(roe, 1), LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Row
End Sub



Cheers,

dr

[mikerickson]

With ThisWorkbook
.Worksheets(.Worksheets.Count).Name
End With

is another way to get the name of the last sheet in a workbook.

[vassili]

thanks man. at least i'm keeping you busy with my all question hahahah.