# AutoFill Question

#### golfnut019

##### Board Regular
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?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### acw

##### MrExcel MVP
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

##### Board Regular
Oh, what a stupid mistake. Thanks.

#### Zack Barresse

##### MrExcel MVP
Couldn't you just use something like this ...

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

#### golfnut019

##### Board Regular

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

Copied directly from my code.

I'm getting a compile error?

#### golfnut019

##### Board Regular
firefytr said:
Couldn't you just use something like this ...

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

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

#### golfnut019

##### Board Regular
firefytr said:
Couldn't you just use something like this ...

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

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

#### Zack Barresse

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

.. or ..

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

Replies
2
Views
109
Replies
2
Views
192
Replies
0
Views
117
Replies
0
Views
156
Replies
10
Views
827