AutoFill Question

[golfnut019]

Range("O2").Formula = [H2] & " " & [I2] & ", " & [J2] & ", " & [K2] & " " & [L2]
With ActiveSheet
Set rngData = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Set rngFormula = .Range("O2")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row, rngFormula.Column))
End With


When I run this, the cell contains the data and not the formula hence when it autofills it increments the address and not the formula parameters.

So cell 1 = 123 Main Street, cell 2 = 124 Main Street, cell 3 = 125 Main street and so on.

It should be cell 1 = 123 Main street, cell2 = 345 Jones Ave. ( depending on what is stored in cells H through J )



Any help on this?

 

[acw]

Hi

YOu are putting in a text string and not a formula

change
Range("O2").Formula = [H2] & " " & [I2] & ", " & [J2] & ", " & [K2] & " " & [L2]

to

Range("O2").Formula = "=H2 & "" "" & I2 & "", "" & J2 & "", "" & K2 & "" "" & L2"


Tony

 

[golfnut019]

Oh, what a stupid mistake. Thanks.

 

[Zack Barresse]

Couldn't you just use something like this ...

Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=H2&I2&J2&K2&L2"

instead of all that?

 

[golfnut019]

Range("O2").Formula= "=[H2] & " " & [I2] & ", " & [J2] & ", " & [K2] & " " & [L2]"

Copied directly from my code.


I'm getting a compile error?

 

[golfnut019]

Couldn't you just use something like this ...

Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=H2&I2&J2&K2&L2"

instead of all that?

I would have if I was good at this stuff. Thanks! I'll try that.

 

[golfnut019]

Couldn't you just use something like this ...

Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=H2&I2&J2&K2&L2"

instead of all that?

If I wanted to put spaces or commas between H2 and I2 and etc, how would I go about doing that?

 

[Zack Barresse]

Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=H2&"",""&I2&J2&K2&L2"

.. or ..

Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=H2&"" ""&I2&J2&K2&L2"