VBA Formula shows instead of value - Tried multiple solutions without success

ilikered100

New Member
Joined
May 29, 2013
Messages
46
I've spent hours trying to resolve this problem and read several posts about it, but there is something I am obviously missing.

Here is the VBA code for the formula that is being entered. When I run the code, the formula shows in the cell(s) and not the value(s).


Range("B2:B2000").Formula = "=iferror(if(VLOOKUP(A2,WorkingData!$A$2:$A$2000,1,FALSE)<>"""",TRUE),"""")"

Here's what I've tried so far without success:

  • Made sure the worksheet with the formulas has all cells formatted to General
  • Made sure the worksheet that the vlookup is pulling from has all cells formatted as General
  • Removed all the formulas. Saved and Closed. Re-opened and re-entered the formulas

The only thing that has been successful is if I go to each cell with a formula, select the cell and then hit enter, but there are thousands of cells and I can't find a way to do this in VBA.

Any help is much appreciated as I've spent far too long on this and I'm sure it is going to be something very simple that I have overlooked.

Thank you,
Carolyn
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Works fine in a blank sheet.
I didnt have the named sheet or the data to VLOOKUP so I just amended the IFERROR result if false to a value and got that value.
No formula showed up.

Try hitting
Ctrl ` (left of the 1 key)
see if the formula disappears and the value appears then.
 

ilikered100

New Member
Joined
May 29, 2013
Messages
46
Thank you... but I tried the Ctrl ` (left of the 1 key) also... forgot to put that in the post.

Maybe I should create a new sheet and move everything over to it as I cannot figure out the problem?

Thank you for responding.

Carolyn
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
Weird.
No other formulas show up just the VBA?

Whittle it down, change the formula to something simple and see if that displays as a formula or a result.
If a result then you need to investigate your formula.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you have any other code before the line you've shown?
 

ilikered100

New Member
Joined
May 29, 2013
Messages
46
The only code I have before the formula is the code that selects the sheet... that's it.

Sheets("ACC_Source").Select

I'll try doing a simplified formula. This has been driving me crazy.

Thank you for responding.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
Does this make any difference?
Code:
With Range("B2:B2000")
   .NumberFormat = "general"
   .Formula = "=iferror(if(VLOOKUP(A2,WorkingData!$A$2:$A$2000,1,FALSE)<>"""",TRUE),"""")"
End With
 

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,428
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top