private macro won't work.....

[cmefly]

any idea why this won't work...

i've got a drop-down menu (A1) and in B1 i have a vlookup formula that can equal one of three result (x, y, or z).

i'm trying to hide and unhide rows based on the value in b1.

any ideas?


Private Sub worksheet_change()

Dim therange As Range
Dim i As Integer

therange = Range("w22:ab104")


If Application.VLookup(Range("b20").Value, therange, 4, 0) = "x" Then
Rows("22:123").Select
Selection.EntireRow.Hidden = False

Rows("37:123").Select
Selection.EntireRow.Hidden = True
End If


If Application.VLookup(Range("b20").Value, therange, 4, 0) = "y" Then
Rows("22:123").Select
Selection.EntireRow.Hidden = False

Rows("22:37").Select
Selection.EntireRow.Hidden = True

Rows("97:123").Select
Selection.EntireRow.Hidden = True
End If

If Application.VLookup(Range("b20").Value, therange, 4, 0) = "z" Then
Rows("22:123").Select
Selection.EntireRow.Hidden = False

Rows("22:96").Select
Selection.EntireRow.Hidden = True
End If

End Sub

 

[shg]

Object variables require Set:

[B][COLOR=red]Set[/COLOR][/B] therange = Range("w22:ab104")

 

[VoG]

It should be

Set therange = Range("w22:ab104")

 

[Norie]

The sub heading for the change event of a worksheet looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
End Sub

You seem to be missing a bit from yours.

 

[cmefly]

i'm getting a compile error....is worksheet_change the correct one to use? the macro is stopping on that line....

 

[Norie]

Is the compile error something like this:

'Procedure declaration does not match....'

If it is that's because you are using the incorrect heading for the worksheet change event.

Whether you need to use the change event is kind of another matter.

Do you want the code to trigger when a change is made on the worksheet?

Is that change the result of a formula?