Vlookup in VBA

[Crispy85]

Hi Guys,

I'm busy trying to solve my problem for a couple of days now and i can't find how to fix it.

My VBA-script is as following:

Please Login or Register  to view this content.

*The hidden columns in case 3 and 4 are the same, but it's just an example.

Now what goes wrong is the value in D1. I want to retrieve it via VLOOKUP, from a value that has been entered in C1.

If i take the formula out and simply enter the value 1, 2, 3 or 4, the script works fine.
I also tried to put it between tags like this:

Case "1"
Case "2"
Case "3"
Case "4"

But that didn't make a difference.

Could someone explain me what i'm doing wrong?

Note; the use of VLOOKUP is kinda neccessary.

Thank you guys!

EDIT* btw; my VLOOKUP in D1 looks like this:

Please Login or Register  to view this content.

[Norie]

What exactly goes wrong?

[Crispy85]

Haha oh sorry.

Well, it doesn't start the action of hiding rows and columns when the number 1, 2, 3 or 4 has been retrieved by VLOOKUP.

When i just enter a random number between 1 and 4 as plain value it does do what it suppose to do; hide rows and columns.

[gjcase]

Looks like it should work. Have you verified that the lookup is returning a valid value? I tried a test case with simple lookups and it works for me.

[gjcase]

Have you tried stepping through the macro (F8) to see if it actually branches as expected? If it's not doing so with the Vlookup, I suspect there's something about that value it does not like, for instance the Vlookup might be returning 1.00001 vs 1? Will it work if you set D1 to ='Activity Database'!$I$2 (or whatever the right cell would be?)

[Crispy85]

Hi,

Yes it's returning a valid value.

Check it out for yourself, if you will

So if you enter for instance "003" in cell C1, it should start the lookup in D1. Come back with the value 2.

In the VBA script, value 2 stands for hiding certain rows and columns.

[catchnanan]

hai breda

actually what this code intends to acheive finally in the screen
can you tell us with example

[Norie]

Does the code actually get executed?

I don't think it will be as you are checking if D1 is Target, which is the cell that changed, but it's actually C1 that has been changed.

Try changing to this.

Please Login or Register  to view this content.

[Crispy85]

Does the code actually get executed?

I don't think it will be as you are checking if D1 is Target, which is the cell that changed, but it's actually C1 that has been changed.

Try changing to this.

Please Login or Register  to view this content.

Thanks!!
This was it!