Problem with Formula n VB Macro

[Emily]

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

 

[Dave Patrick]

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

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

 

[Guest]

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

 

[Emily]

Hi Charlie,

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

Thanks!

 

[Emily]

Hi Charlie,

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

Thanks!

 

[Guest]

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]

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

 

[Guest]

I should first explain that the AutoFill method only works starting from the
selected cell, i.e. if "M2" is where your formula is then you can only
autofill up, down, left, or right from cell "M2".

Example:

Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M15"), Type:=xlFillDefault

If you want to copy the formula in "M2" to "A5" (and beyond) you must first
copy the formula to "A5" and then autofill down the "A" column

Range("A5").Formula = Range("M2").Formula
Range("A5").Select
Selection.AutoFill Destination:=Range( <put your range in here> ),
Type:=xlFillDefault

and insert the fill range in the format "A5:A999" or Cells(5, "A"),
Cells(999, "A") etc.

I'll leave it to you to calculate the row range. I need to hit the road for
the day. Bye

 

[Emily]

Thanks!

 

[Dave Patrick]

Seems it wasn't easier after all.

--

Regards,

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

:
|I should first explain that the AutoFill method only works starting from
the
| selected cell, i.e. if "M2" is where your formula is then you can only
| autofill up, down, left, or right from cell "M2".
|
| Example:
|
| Range("M2").Select
| Selection.AutoFill Destination:=Range("M2:M15"), Type:=xlFillDefault
|
| If you want to copy the formula in "M2" to "A5" (and beyond) you must
first
| copy the formula to "A5" and then autofill down the "A" column
|
| Range("A5").Formula = Range("M2").Formula
| Range("A5").Select
| Selection.AutoFill Destination:=Range( <put your range in here> ),
| Type:=xlFillDefault
|
| and insert the fill range in the format "A5:A999" or Cells(5, "A"),
| Cells(999, "A") etc.
|
| I'll leave it to you to calculate the row range. I need to hit the road
for
| the day. Bye

 

[Guest]

If you mean the explanation of how to use .AutoFill, no it was not easier.
But if you mean using .AutoFill instead of a For-Next loop? ... well, try it