Returning a Value in VBA with a nested formula

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
I also tried what you posted Mark.
It opens a dialog box for me to select a file.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Change
VBA Code:
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
VBA Code:
Range("C2:C" & Lastrow).Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
 

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,966
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome (the below is shorter).
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top