Auto Fill formula down till last row in data set

user_19049010 0
Viewed 24

I am looking to copy multiple formulas in multiple columns down to the end of my dataset. This is what I have thus far and it is working just fine.

Sub CopyFormulas()
Dim Lastrow As Long
Application.ScreenUpdating = False
Lastrow = Range("C" & Rows.Count).End(xlUp).Row
Range("A3:A" & Lastrow).Formula = "=D3&LEFT(C3,10)"
Range("B3:B" & Lastrow).Formula = "=D3&C3"
End Sub

However, I am trying to add in an additional formula to copy down in column I and I am receiving a Syntax error - here is the additional line that is erroring out:

Range("I3:I" & Lastrow).Formula = "=IF(E3="-","-",LEFT(E3,FIND("(Primary)",E3)-2))"

Can anyone advise what it is about this formula that is causing the error? Thank you!

excel vb.net
1 Answers

You have to double the inner quotation marks:

Range("I3:I" & lastrow).Formula = "=IF(E3=""-"",""-"",LEFT(E3,FIND(""(Primary)"",E3)-2))"

Side note: if there is no "-" nor "(Primary)" the formula will return an error.

user_165784240
Related