Looking for a value using FIND and WHAT

[mozart]

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

[Rowan]

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
>
>