Excel Custom Format Not Working - Formula Problem?

[mazuzu]

Hi All,

I wrote a macro that makes a cell equal to a vlookup formula. So m
code looks something like this:

Range("A2") = "=vlookup(A1,A3:B50,3,False)"

Then I want the custom format of 000000.00 applied to cell A2.

So I coded:

Range("A2")..NumberFormat = "000000.00"

This does not work however, the format does not change.

I noticed the only way to solve this problem is to manually select th
cell then click on the contents of the cell and hit ENTER.

Have any of you encountered this problem? Any ideas on how to fix it?

Any help would be greatly appreciated!

Thank you

 

[Trevor Shuttleworth]

Try it with only one "." ).N

Range("A2").NumberFormat = "000000.00"

With only columns A and B in the lookup table, you can't return a result
from column 3 so that might be a problem too.

A small test:

Sub test()
Range("A2").Formula =
"=if(isna(vlookup(A1,A3:B50,3,False)),0,vlookup(A1,A3:B50,3,False))"
Range("A2").NumberFormat = "000000.00"
End Sub


Regards

Trevor

 

[mazuzu]

thanks for the reply.

i tried it and it still does not work.

the format is not changing.

the 3 and the extra . was just a typo on my part...

 

[mazuzu]

i think the problem is... the number in A2 is type 2.

If I divided the number by 1 or if simply select the cell and hit ente
in the editing field on top.... it switches to type 1.. and th
formatting kicks in.

how do i change the type of a cell

 

[mazuzu]

ok!

i figured it out.

all i had to do was simply add a "/1" at the end of my vlookup.. and i
converts it to a number.

thanks

 

[David McRitchie]

Hi Mazuzu,
That is one way of making it work.

I would fix the table. Obviously you have text.
select an empty (never used cell) and copy it Ctrl+C
select the column in your table that should be numbers
Edit , paste special, add

Then you can check what is text constants
Ctrl+A (select all)
Edit, Goto, Special, constants & text

More infomation
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm