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
 

Chefsohail

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

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Chefsohail

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

1601718229041.png


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

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

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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,683
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