Returning a Value in VBA with a nested formula

Chefsohail

Board Regular
Joined
Oct 3, 2020
Messages
90
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0
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",""),"")
 
Upvote 0
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"",""""),"""")"
 
Upvote 0
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",""),"")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top