Dim iNumOfRows As Integer
iNumOfRows = 10
For i = 3 To iNumOfRows + 1
Sheet1.Cells(i, 6).Formula = "=SEARCH(""_"",C" & i & ",1)"
Next

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Emily" wrote:
| Hi,
|
| If I use
|
| For i = 3 To iNumOfRows + 1
| Sheet1.Cells(i, 6).Formula = Sheet1.Cells(2, 6).Formula
| Next
|
| It copies the formula for row 2:
|
| "=SEARCH("_",C2,1)"
|
| to all of these cell. Specifically I've got the following results:
|
| Sheet1.Cells(3, 6).Formula = "=SEARCH("_",C2,1)"
| Sheet1.Cells(4, 6).Formula = "=SEARCH("_",C2,1)"
| Sheet1.Cells(5, 6).Formula = "=SEARCH("_",C2,1)"
| Sheet1.Cells(6, 6).Formula = "=SEARCH("_",C2,1)"
| ...
|
| That's NOT what I want. I want the row number in the formula the same
| as the current cell.
| Specifically, I want to get the following results:
|
| Sheet1.Cells(3, 6).Formula = "=SEARCH("_",C3,1)"
| Sheet1.Cells(4, 6).Formula = "=SEARCH("_",C4,1)"
| Sheet1.Cells(5, 6).Formula = "=SEARCH("_",C5,1)"
| Sheet1.Cells(6, 6).Formula = "=SEARCH("_",C6,1)"
| ...
|
| Any advice on how to do this? Thanks!
|
| -Emily
|