CODEWITHSUNDEEP
vbaexcel
jufg
jufg

Reputation: 163

Autofill formula in VBA

I have the below formula within Excel which works well. This formula is then dragged down to the bottom of the table.

=IF(OFFSET(OriginalData!$A$1,MATCH(TempTable!$A2,OriginalData!$A:$A,0)-1,2,1,1)=0,"",OFFSET(OriginalData!$A$1,MATCH(TempTable!$A2,OriginalData!$A:$A,0)-1,2,1,1))

I now want to add this to vba and have attempted the following:

Private Sub MatchViaEmployeeName()

With Worksheets("TempTable")

Set Usdrws = .Cells(.Rows.Count, 1).End(xlUp)

.Range(.Cells(1, 1), Usdrws.Offset(, 1)).FormulaR1C1 = "=IF(OFFSET(OriginalData!R1C1,MATCH(TempTable!RC[-1],OriginalData!$A:$A,0)-1,2,1,1)=0,"""",OFFSET(OriginalData!R1C1,MATCH(TempTable!RC[-1],OriginalData!$A:$A,0)-1,2,1,1))"

End With

End Sub

My logic is to select the entire range using the 'Usdrws' variable and then entering the formula.

I'm currently getting a

run-time error 1004

Upvotes: 0

Views: 102

Answers (2)

JohnyL
JohnyL

Reputation: 7152

In formula you need to change:

  1. Change "" to """" in formula
  2. Change $A$1 to R1C1
  3. Change A:A to C1

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4714

you have to escape the speech marks in your formula because the whole thing is in speechmarks in VBA

 .Range(.Cells(1, 1), Usdrws.Offset(, 1)).FormulaR1C1 = _
"=IF(OFFSET(OriginalData!$A$1,MATCH(TempTable!RC[-1],OriginalData!$A:$A,0)-1,2,1,1)=0,"""",OFFSET(OriginalData!$A$1,MATCH(TempTable!RC[-1],OriginalData!$A:$A,0)-1,2,1,1))"

Upvotes: 1

Related Questions