Looking for a value using FIND and WHAT
 

I am trying to write some code that will search a workbook for a valu
given a string. Naturally I am using "Cells.Find(What:=.....". Howeve
I need my search string to have a number that is incremented on eac
loop and hence I wanted to put my search string into a variable.

Unfortunately if I specify the variable after the 'what:' it throws u
an error. How can i get around this?

----------------------------------------------------------------

Sub Dates()
Dim Grand As String

For N = 1 To 47

Grand = "Grand Total" & N

Cells.Find(What:=Grand, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell = Format(WeekStartDate, "D-MMM")

Next N

End Su

--
mozar
-----------------------------------------------------------------------
mozart's Profile: http://www.excelforum.com/member.php...fo&userid=1331
View this thread: http://www.excelforum.com/showthread.php?threadid=39767

Looking for a value using FIND and WHAT
 

Using a variable in the What statement shouldn't cause an error but you will
get an error if the string you are searching for is not found. You can
prevent by changing your code to set a range = to the cell if it is found:

Sub Dates()
Dim Grand As String
Dim n As Integer
Dim weekStartDate As Date
Dim fndCell As Range

weekStartDate = Date '<< Change
For n = 1 To 47
Grand = "Grand Total" & n

Set fndCell = Cells.Find(What:=Grand)

If Not fndCell Is Nothing Then
fndCell = Format(weekStartDate, "D-MMM")
End If
Next n

End Sub

Hope this helps
Rowan

"mozart" wrote:


I am trying to write some code that will search a workbook for a value
given a string. Naturally I am using "Cells.Find(What:=.....". However
I need my search string to have a number that is incremented on each
loop and hence I wanted to put my search string into a variable.

Unfortunately if I specify the variable after the 'what:' it throws up
an error. How can i get around this?

----------------------------------------------------------------

Sub Dates()
Dim Grand As String

For N = 1 To 47

Grand = "Grand Total" & N

Cells.Find(What:=Grand, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell = Format(WeekStartDate, "D-MMM")

Next N

End Sub


--
mozart
------------------------------------------------------------------------
mozart's Profile: http://www.excelforum.com/member.php...o&userid=13314
View this thread: http://www.excelforum.com/showthread...hreadid=397676