Value error when trying to copy down a formula

[mms2277]

I know there are a lot of threads about macros to copy formulas but I haven't found an answer to my specific problem yet. So,
I've been using this macro

Sub FillDownFormula()
Dim LR As Long
LR = Range("J" & Rows.Count).End(xlUp).Row
Range("A1").AutoFill Destination:=Range("A1:A" & LR)
End Sub

to copy down a formula down column A where the number of rows varies from file to file. The problem that I'm having is that when I run it the cells come up with the #VALUE! error. When I click on the cells the correct formula displays in the formula bar and if I double click on the cells and hit enter then it works. I thought that maybe the problem was that the formula in A1 has text (column header) and rest of A has numbers but that didn't seem to be the issue.

Any insight is greatly appreciated.

Thanks!

 

[jeffmb]

What formula do you have in A1?

 

[mms2277]

=PERSONAL.XLSB!MultiCat.MultiCat(B1:S1,";")

which refers to

Public Function MultiCat( _
ByRef rng As Excel.Range, _
Optional ByVal Delim As String = "") _
As String
Dim rCell As Range
For Each rCell In rng
MultiCat = MultiCat & Delim & rCell.Text
Next rCell
MultiCat = Mid(MultiCat, Len(Delim) + 1)
End Function

Just to join the cells into one. If I click and drag the black box from a1 down the column it copies the formula and applies it just fine but I need to be able to copy down the formula as part of a macro.

 

[jeffmb]

Try:

Sub FillDownFormula()
Dim LR As Long
    Application.Calculation = False
    LR = Range("J" & Rows.Count).End(xlUp).Row
    Range("A1").AutoFill Destination:=Range("A1:A" & LR)
    Application.Calculation = True
End Sub

 

[mms2277]

Try:

Sub FillDownFormula()
Dim LR As Long
    Application.Calculation = False
    LR = Range("J" & Rows.Count).End(xlUp).Row
    Range("A1").AutoFill Destination:=Range("A1:A" & LR)
    Application.Calculation = True
End Sub

Wow it worked perfectly. Thank you so much!!