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

[ilikered100]

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

 

[Special-K99]

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]

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]

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.

 

[Fluff]

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

 

[ilikered100]

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]

Does this make any difference?

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