Returning a Value in VBA with a nested formula

[Chefsohail]

Hi Team,

I am a rookie in VBA and am learning.

I am currently using a formula in Cell and it returns the desired value. However when i use the same formula in VBA it doesnt work.

I managed to find a thread on similar lines and for now have used their code and added the formula.

Can someone please help fix this.

Sub Test()
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "Q").End(xlUp).Row
For Each c In Range("Q2:Q" & Lastrow)
c.formula = IFERROR(IF((VLOOKUP(P2,'Data_Logic '!B:B,1,0))=(VLOOKUP(P2,'Data_Logic '!B:B,1,0)),"No",""),""))
Next
Application.ScreenUpdating = True
End Sub

Error Message: Compiled Error : Expected Expression

 

[Chefsohail]

Mark.. can we change Sheet2! which is the name of the sheet...to the exact sheet code. Because incase if someone renames that sheet tab, i still want the macro to work. I am aware this is possible,, I learnt it... but here im facing a challenge...tried but no result.

 

[MARK858]

..to the exact sheet code.

What do you mean by this? the sheets codename?

 

[Chefsohail]

I meant

To use the one highlighted in Yellow (Name) than using Name (without parenthesis). But I am grateful to you for all the expertise you provided... I will soon come up with a few more questions as I am working on a project.

 

[MARK858]

Yes the name outside of the brackets is the sheets codename. I'll post something when I get back in as I am now out for about 3 hours.

 

[MARK858]

First of all the 2 bits that you have highlighted are 2 different things.
The top one is the sheets Codename (the name outside of the brackets when you look in the Project window).
The bottom one is the sheets tab name (the name inside the brackets in the Project window and the name that appears on the tab when you look at it in Excel).

Anyway, if you had a sheet with the Codename FormulaSheet then you could use it in the formula something like the below...

Sub Test()
'Validates the URL against bogus URL's
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long, mySht As String

mySht = FormulaSheet.Name
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

With Range("C2:C" & Lastrow)
  .Formula = "=IFERROR(IF((VLOOKUP(B2," & mySht & "!A:A,1,0))=(VLOOKUP(B2," & mySht & "!A:A,1,0)),""No"",""""),"""")"
  .Value = .Value
End With

Application.ScreenUpdating = True
End Sub