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

 

[MARK858]

No that is not what I posted, look at the quotes in what I posted to what you have and the whole formula needs surrounding in quotes like I have.

 

[Chefsohail]

and

I use this code..


Sub Test()

Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For Each c In Range("C2:C" & Lastrow)
c.formula = IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),"No",""),"")
Next
Application.ScreenUpdating = True
End Sub

 

[Chefsohail]

I also tried what you posted Mark.
It opens a dialog box for me to select a file.

 

[MARK858]

If it is opening a dialog box then there is something wrong with the name of your sheet. Post the code exactly as you tried it.

 

[Chefsohail]

Yup.. You caught that right Mark... Thanx.. it works.. However is it possible to get the value here instead of formula in the cell.
Also I my goal is to auto fill the entire column

 

[Chefsohail]

Sub Test()
'Validates the URL against bogus URL's
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For Each c In Range("C2:C" & Lastrow)
c.Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
Next
Application.ScreenUpdating = True
End Sub


Now this works... but it doesnt consider B2 as a relative reference cell... it adds the same formula in all the cells.

 

[MARK858]

Change

For Each c In Range("C2:C" & Lastrow)
c.Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
Next

to

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

 

[Chefsohail]

Sub Test()
'Validates the URL against bogus URL's
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:C" & Lastrow).Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
Application.ScreenUpdating = True
End Sub

This works.. However still leaves the formula intact in the cell. any way to get only the result?

Do we have a solution for that?
Or do you suggest that we copy and paste the row as values?

 

[Chefsohail]

This Worked Mark.. Thanx for your assistance.

Option Explicit


Sub Test()
'Validates the URL against bogus URL's
Application.ScreenUpdating = False
Dim c As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("C2:C" & Lastrow).Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
Range("C2:C" & Lastrow).Formula = Range("C2:C" & Lastrow).Value
Application.ScreenUpdating = True
End Sub

 

[MARK858]

You're welcome (the below is shorter).

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