Range error
 

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

Range error
 

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

--
HTH

Bob Phillips

"George Andrews" wrote in message
...
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

Range error
 

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" wrote in message
...
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

--
HTH

Bob Phillips

"George Andrews" wrote in message
...
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

Range error
 

"George Andrews" wrote in message
...
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?