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

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
|

Actually, if the formula already exists in one cell it is easier to do:

Range("A2").Select
Selection.AutoFill Destination:=Range(Cells(2, 1), Cells(iNumOfRows + 1,
1)), Type:=xlFillDefault


"Dave Patrick" wrote:

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
|

Hi Charlie,

Could you provide me with a more correct syntax? There's error in the
syntax you sent.

Thanks!

Hi Charlie,

Could you provide me with a more correct syntax? There's error in the
syntax you sent.

Thanks!

If you copied and pasted the example the "Selection.AutoFill" line might have
wrapped to two lines. It should all be on one line. Here it is using
continuation characters:

Range("A2").Select
Selection.AutoFill Destination:= _
Range(Cells(2, 1), Cells(iNumOfRows + 1, _
1)), Type:=xlFillDefault


"Emily" wrote:

Hi Charlie,

Could you provide me with a more correct syntax? There's error in the
syntax you sent.

Thanks!

Hi Charlie,

I used your syntax as follows:

Range("M2").Select
Selection.AutoFill Destination:=Range(Cells(5, 1), Cells(iNumOfRows +
1, 1)), Type:=xlFillDefault

However, I got an error when I ran the macro, "Runtime error '1004'.
Autofill method of Range class failed".

Any advice on how to get rid of this error? Thanks!

-Emily