# 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

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### 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"``

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,994
Messages
5,834,773
Members
430,320
Latest member
Napzz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back