Formula being inserted as literal

[gt93grad]

Trying to create a formula with VBA that should be resolved in the cell. However, it's treating is as a literal. I developed this line by recording a macro. Here's the line.

I've tried changing the column to General and reactivating, but no luck.

ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE)),""HKN1"",VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE))"

 

[Peter_SSs]

1. What version of Excel are you using?

2. Try setting the format within the code, just to be sure.

With ActiveCell
  .NumberFormat = "General"
  .FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE)),""HKN1"",VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE))"
End With

 

[gt93grad]

That's the thing. Even setting it to general doesn't work. I have to change the formula to use "D7" instead of the RC reference. Then it works. I'm in 2010.

 

[Peter_SSs]

I'm in 2010.

In that case you could simplify your formula to
=IFERROR(VLOOKUP(RC[-1],SparesOriginsLookup,4,FALSE),""HKN1"")
However, that is not related to the problem you are having.

A bit more information might help.
When you are having the problem ..

1. What is the active cell address?

2. What value is in the cell immediately to the left of the active cell?

3. What range does SparesOriginsLookup refer to?

4. Can you confirm that the value you reported as the answer to my Q2 does actually occur in the left hand column of the SparesOriginsLookup range?

5. Can you confirm that if the VLOOKUP returns an answer you want the text "HKN1" returned and not the contents of cell HKN1?

6. Are you running this code on its own or as part of a larger code? If larger, perhaps we need to see all of it.

7. Do you have any 'event' code in the worksheet's module?

 

[gt93grad]

Answers:

1. What is the active cell address? E7.

2. What value is in the cell immediately to the left of the active cell? Dummy

3. What range does SparesOriginsLookup refer to? Four columns that look like this:

ItemID	ModelName	LocItem	OriginAssignment
1131066	Prod_Spares	UGS11131066	HKN1

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

4. Can you confirm that the value you reported as the answer to my Q2 does actually occur in the left hand column of the SparesOriginsLookup range? Yes. Absolutely.

5. Can you confirm that if the VLOOKUP returns an answer you want the text "HKN1" returned and not the contents of cell HKN1? Yes. HKN1 is an ID for a Hong Kong location.

6. Are you running this code on its own or as part of a larger code? If larger, perhaps we need to see all of it. Part of a larger code, but I've separated this for testing.

7. Do you have any 'event' code in the worksheet's module? No.

 

[shg]

Isn't this the same question as http://www.mrexcel.com/forum/excel-questions/693202-creating-formula-visual-basic-applications.html??

 

[gt93grad]

No, not really. I know how to create a formula with VBA. I'm using the same steps now. The problem is that it's not resolving, as it did before, and I can't figure out why.

 

[shg]

Did you try the suggestion I made in your other thread?

 

[gt93grad]

Yes, I did. I tried Text and Number, though many of the values are not strictly numbers (they contain alpha characters). Again, changing the reference to D7 vs an R1C1 reference worked, but not sure why.

 

[Peter_SSs]

3. What range does SparesOriginsLookup refer to? Four columns that look like this:

ItemID	ModelName	LocItem	OriginAssignment
1131066	Prod_Spares	UGS11131066	HKN1

<tbody>
</tbody><colgroup><col><col><col span="2"></colgroup>

Yes, but what sheet is this table on (same sheet as the formula?) and what range (eg K1:N50).

4. Can you confirm that the value you reported as the answer to my Q2 does actually occur in the left hand column of the SparesOriginsLookup range? Yes. Absolutely.

So you have "Dummy" as an ItemID along with values like "1131066"?


What EXACTLY appears in E7 after running the code?


I'm not sure any of these answers is going to resolve the problem, but I'm just trying to built/test a sheet as much like yours as I can get.