Excel Formula thru VBA Code

[blue9244]

I probably have just developed a brain clot, but I am unable to make the following work:

Range("E22&quot.Formula = "=IF(E20=0,0,VLOOKUP(List2!U80:V89,2,1))"

VBA seems to stick on the ! and using quotes or double quotes does not seem to work.

This is for a worksheet that I build on the fly populated with lots of excel coding and this is my only stickler.

Thanks for the help!!!!

 

[dan1967]

It is sticking on the ! because you didn't enter the lookup value.

Range("E22").Formula = "=IF(E20=0,0,VLOOKUP(E20,List2!U80:V89,2,1))"
[\code]
This is assuming that E20 holds the value you want to lookup on your other sheet.


Dan.

 

[blue9244]

No, that's not it. It is included in my code - I just typed it wrong here. It actually is:

Range("E22&quot.Formula = "=IF(E20=0,0,VLOOKUP(E20,List2!U80:V89,2,1))"

Sorry about the earlier post...

 

[dan1967]

Mine was exactly like yours, but I am glad you figured it out anyway.


Dan.

 

[blue9244]

I guess I wasn't very clear. I had typed it wrong initially but the code I was trying to use was just as you said it should be but it still does not work. VBA is rejecting it and I guess because of the !... Any new ideas?

Thanks very much!

 

[Zathras]

blue9244: I can't find anything wrong with the line of code as given. Just a guess, but you may not have the correct worksheet active when executing it.

Look around in other sheets in cell E22 and see what you can find.

 

[blue9244]

Thanks Zathras! Actually I thought of a good work around and it works just fine. I substituted a name for the lookup range thereby bypassing the ugly ! and VBA is happy! When VBA is happy so am I!!!!

:-D

 

[dan1967]

I'm glad you figured it out.


Dan.