Range error

[George Andrews]

Excel 2000

Can anybody help?

I get a " Method 'Range' of object '_Worksheet' failed " error message when
I run this code:

Private Sub Worksheet_selectionChange(ByVal Target As Range)

If ActiveCell = "Sun" Then

LUPVALUE = Range("f129")

result = Application.WorksheetFunction _
.VLookup(LUPVALUE, Range("testrange"), 2, False)

Application.StatusBar = "Total Orders = " & result
Else
Application.StatusBar = ""

End If
Application.Calculate
End Sub

Can you let me know how to fix this.

Regards

George

 

[Bob Phillips]

It works okay if the valu looked up is found, so to cater for not finding,
try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LUPVALUE, result

If ActiveCell = "Sun" Then

LUPVALUE = Range("f129")

result = Application.VLookup(LUPVALUE, Range("testrange"), 2, False)

If Not IsError(result) Then
Application.StatusBar = "Total Orders = " & result
Else
Application.StatusBar = ""
End If
Else
Application.StatusBar = ""

End If
Application.Calculate
End Sub

 

[George Andrews]

Bob

I am afraid this still does not work.

Nothing appears on the staus bar when I click on any cell that is not equal
to "Sun".

When I click on a cell with "sun" then I get the same error message as
before.

Any ideas?

Andrew

 

[Bob Phillips]

Bob

I am afraid this still does not work.

Nothing appears on the staus bar when I click on any cell that is not equal
to "Sun".

That is b ecause your code clears the statusbar in this case.

When I click on a cell with "sun" then I get the same error message as
before.

If the looked up value is not found, I added code to clear the status bar
(as was your default). Is it found?