SQL Error in trying to fill formula down

[mellis]

Hi,

I have an issue with my SQL code below, in that I am trying to fill series the formula in column Y within my dynamic table. For some reason it doesn't like the format of the formula despite it working in the excel sheet itself. It appears not to like the "A" which is being added within the concatenate formula.

Any help would really be appreciated.



Private Sub Workbook_Open()


ActiveWorkbook.RefreshAll

i = 2 'If you have headers i = 2
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Do Until i > LastRow
mySplit = Split(Trim(Range("B" & i).Value), " ")
If UBound(mySplit) > 0 Then
Range("B" & i).Value = mySplit(0)
bool = True
x = i
For Each Item In mySplit
If bool = False Then
Rows(i + 1).Insert
Range("B" & i + 1).Value = Item
Range("A" & i + 1).Value = Range("A" & x).Value
Range("C" & i + 1).Value = Range("C" & x).Value
Range("D" & i + 1).Value = Range("D" & x).Value
Range("E" & i + 1).Value = Range("E" & x).Value
Range("O" & i + 1).Value = Range("O" & x).Value
Range("P" & i + 1).Value = Range("P" & x).Value
Range("Q" & i + 1).Value = Range("Q" & x).Value
Range("R" & i + 1).Value = Range("R" & x).Value
Range("S" & i + 1).Value = Range("S" & x).Value
Range("T" & i + 1).Value = Range("T" & x).Value
Range("U" & i + 1).Value = Range("U" & x).Value
Range("V" & i + 1).Value = Range("V" & x).Value
Range("W" & i + 1).Value = Range("W" & x).Value
Range("X" & i + 1).Value = Range("X" & x).Value
Range("Y2:Y" & LastRow).Formula = "=IF(ISNUMBER(--LEFT(B2,1)),CONCATENATE("A",B2),B2)"

i = i + 1
End If
bool = False
Next Item
i = i - 1
End If
i = i + 1
Loop


ThisWorkbook.Save


End Sub

 

[ukmikeb]

Missing double quotes, perhaps :-

Range("Y2:Y" & LastRow).Formula = "=IF(ISNUMBER(--LEFT(B2,1)),CONCATENATE(""A"",B2),B2)"

And shouldn't you execute that line after the various loops?

hth