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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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.value = 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

I have updated the code.. was testing with .formula.....but doesnt work either
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...
VBA Code:
c.formula = "=IFERROR(IF((VLOOKUP(P2,'Data_Logic '!B:B,1,0))=(VLOOKUP(P2,'Data_Logic '!B:B,1,0)),""No"",""""),"""")"
Although I don't know what the first test is supposed to achieve.
 

Chefsohail

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

Sheet 1 Column B has certain values.
Sheet 2 Column A has certain values.
Sheet 1 Column C is where i perform a IfError, If condition Vlookup to either return a No or Blank.. But i do not need the error message if the lookup value is not available.
in Sheet 1 Column C i want only the values of the result.

Please explain how to fix this.

Hope that helps.

1601705835267.png


1601705858214.png
 

MARK858

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

ADVERTISEMENT

I am currently using a formula in Cell and it returns the desired value.
You stated the above so I do not understand your last posts as the above states that "it returns the desired value".
If that is the case then post the formula exactly as you have it in C2 if you just want it converted to VBA..
 

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
Hey Mark, Thanx for your expertise.

this is the formula that i use in C2

=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),"No",""),"")
 

MARK858

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

ADVERTISEMENT

In VBA that is
VBA Code:
c.Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
In what way does it not work? your formula goes into the cell fine for me exactly as you have posted with the code.
VBA Code:
Sub xxxx()
Range("C2").Formula = "=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),""No"",""""),"""")"
End Sub
Book1
BC
1
2 
Sheet3
Cell Formulas
RangeFormula
C2C2=IFERROR(IF((VLOOKUP(B2,Sheet2!A:A,1,0))=(VLOOKUP(B2,Sheet2!A:A,1,0)),"No",""),"")

this is the formula that i use in C2

=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
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,106
Messages
5,622,768
Members
415,926
Latest member
jerrynababa

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