Run Time Error 1004: Method 'Range' of object'_Worksheet' failed ... Help Please !!

[davidsands007]

Hiya, I've got a spreadsheet showing an interactive matrix whereby when the end user clicks on a cell, say with the number 4 in it, it will return the value of 4 peoples names in a msgbox. In order to achieve this I have the following code as a module (this part seems to be working correctly):

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
If r = lookupval Then
result = result & " " & Chr(10) & r.Offset(0, indexcol - 1)
End If
Next r
MYVLOOKUP = result
End Function

... and then the following code sits as part of sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim Rng1 As Range
Set Rng = Range("C9:N28")
If Intersect(Target, Rng) Is Nothing Then Exit Sub
MsgBox MYVLOOKUP("Y" & Range("L4") & Range("C4") & Range("B" & Target.Row).Value & Range(Target.Column & 8).Value, Sheets("Data").Range("A4:D2050"), 4), vbInformation
End Sub



It is the MsgBox line that I get the 'Run Time Error 1004: Method 'Range' of object'_Worksheet' failed' error for and I have no idea why. It is worth me saying I'm a total novice with coding and I have copied all of this from another excel document (which does work correctly).

Any help would be much appreciated.

Dave

 

[NdNoviceHlp]

Hi Dave. The Function needs some adjustment...

Function MYVLOOKUP(lookupval as long, lookuprange As Range, indexcol As Long) as String

This isn't right according to the function requirements..

MsgBox MYVLOOKUP("Y" & Range("L4") & Range("C4") & Range("B" & Target.Row).Value & Range(Target.Column & 8).Value, Sheets("Data").Range("A4:D2050"), 4), vbInformation

The function requires...

MsgBox MYVLOOKUP(some value, Sheets("Data").Range("A4:D2050"),4),vbInformation

So maybe "some value" should be...

"Y" + Range("L4") + Range("C4") + Range("B" & Target.Row).Value + Range(Target.Column & 8).Value

Put "Option Explicit" at the top of your code. U will find that "Rng" & "Rng1" is not properly declared. I have no idea what "Y" equals? Also U should properly identify all ranges...

Sheets("Sheet1").Range("C" & 4).Value

HTH. Dave

 

[RoryA]

You can't use this:

Range(Target.Column & 8).Value

because Target.Column returns a number not a letter. Use:

Cells(8, Target.Column).Value

instead.