MS Excel Formula in VBA

[OilEconomist]

Thanks and advance. I am getting the following error: "Run-time error '1004' "Application-definded or object defined error" on the following line:

Range("B" & i).Formula = "= IFERROR(VLOOKUP( Range(""C""" & i & "), Sheet2!$A:$A, 1, FALSE), IFERROR(VLOOKUP(Range (""B""" & i - 1 & "), Sheet2!$A:$A, 1, FALSE), ""DNE""))"

Within Column B of Sheet1, I am checking the corresponding cell value in column C by looking it up in column A of Sheet2. If it does not find it, it tries to look up the previous cell B value, if that gives an error, it goes to input "DNE" in cell B.

Option Explicit

Public Sub Frmula()

Dim i As Long

Sheets("Sheet1").Activate

                    For i = 8 To 3000
                               Range("B" & i).Formula = "= IFERROR(VLOOKUP( Range(""C""" & i & "), Sheet2!$A:$A, 1, FALSE), IFERROR(VLOOKUP(Range (""B""" & i - 1 & "), Sheet2!$A:$A, 1, FALSE), ""DNE""))"
                    Next i


End Sub

 

[RoryA]

Try:

Public Sub Frmula()

Sheets("Sheet1").Range("B8:B3000.Formula = "=IFERROR(VLOOKUP(C3,Sheet2!$A:$A, 1, FALSE), IFERROR(VLOOKUP(B2,Sheet2!$A:$A, 1, FALSE), ""DNE""))"

End Sub

 

[OilEconomist]

Try:

Public Sub Frmula()

Sheets("Sheet1").Range("B8:B3000.Formula = "=IFERROR(VLOOKUP(C3,Sheet2!$A:$A, 1, FALSE), IFERROR(VLOOKUP(B2,Sheet2!$A:$A, 1, FALSE), ""DNE""))"

End Sub

Thanks @RoryA as that worked for the most part. I just made a few minor changes to include the "), and as I working with rows i which started at 8 and then I also was using (i - 1) which would mean row 7. This was perfect because I don't have to loop it. I was looking for this method in old posts that I made and on the internet. I knew I saw this method previously, but I could not find it. I will post the modification so I can mark it as the correct one.

(1) changed the first part to include " ):
Sheets("Sheet1").Range("B8:B3000.Formula =

to
Sheets("Sheet1").Range("B8:B3000").Formula =

(2) changed this part of the (the C3 to C8):

IFERROR(VLOOKUP(C3,Sheet2!$A:$A, 1, FALSE)

to:

IFERROR(VLOOKUP(C8,Sheet2!$A:$A, 1, FALSE)

(3) changed this part of the code (the B2 to B7):

IFERROR(VLOOKUP(B2,Sheet2!$A:$A, 1, FALSE), ""DNE""))"

to:

IFERROR(VLOOKUP(B7,Sheet2!$A:$A, 1, FALSE), ""DNE""))"

Final code is as follows:

Option Explicit

Public Sub Frmula()

Sheets("Sheet1").Range("B8:B3000").Formula = "=IFERROR(VLOOKUP(C8,Sheet2!$A:$A, 1, FALSE), IFERROR(VLOOKUP(B7,Sheet2!$A:$A, 1, FALSE), ""DNE""))"

End Sub