davidsands007
New Member
- Joined
- Feb 5, 2016
- Messages
- 1
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
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