Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address & VLOOKUP in VBA
Hi All
I have a sheet that contains a textbox that displays help text depending on the selected cell. Currently the text is selected via a lookup formula based on another cells value. This cell is updated via the Worksheet_SelectionChange event -- so: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A3") = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) End Sub Then Cell C3 uses the following formula to lookup the text: =VLOOKUP((IF(ISERROR(LEFT(A3,FIND(":",A3)-1)),A3,LEFT(A3,FIND(":",A3)-1))),tblHELPTXT,2,FALSE) Is there a way to do this lookup in the Worksheet_SelectionChange event rather than being reliant on cells updating? I'm using XL2002 Look forward to your responses. Trevor Williams |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address & VLOOKUP in VBA
Maybe...
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim res As Variant Dim myTable As Range With Worksheets("sheet9999") Set myTable = .Range("tblhelptxt") End With Set Target = Target.Cells(1) 'first cell res = Application.VLookup(Target.Value, myTable, 2, False) 'still want this? Me.Range("A3").Value = Target.Address(0, 0) 'and maybe this Me.Range("B3").Value = Target.Value If IsError(res) Then Me.Range("C3").Value = "Not found" Else Me.Range("C3").Value = res End If End Sub Trevor Williams wrote: Hi All I have a sheet that contains a textbox that displays help text depending on the selected cell. Currently the text is selected via a lookup formula based on another cells value. This cell is updated via the Worksheet_SelectionChange event -- so: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A3") = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) End Sub Then Cell C3 uses the following formula to lookup the text: =VLOOKUP((IF(ISERROR(LEFT(A3,FIND(":",A3)-1)),A3,LEFT(A3,FIND(":",A3)-1))),tblHELPTXT,2,FALSE) Is there a way to do this lookup in the Worksheet_SelectionChange event rather than being reliant on cells updating? I'm using XL2002 Look forward to your responses. Trevor Williams -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address & VLOOKUP in VBA
Does this work?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If InStr(Target, ":") 0 Then FindData = Left(Target, InStr(Target, ":") - 1) Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then Target = c.Offset(0, 1) End If End If End Sub "Trevor Williams" wrote: Hi All I have a sheet that contains a textbox that displays help text depending on the selected cell. Currently the text is selected via a lookup formula based on another cells value. This cell is updated via the Worksheet_SelectionChange event -- so: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A3") = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) End Sub Then Cell C3 uses the following formula to lookup the text: =VLOOKUP((IF(ISERROR(LEFT(A3,FIND(":",A3)-1)),A3,LEFT(A3,FIND(":",A3)-1))),tblHELPTXT,2,FALSE) Is there a way to do this lookup in the Worksheet_SelectionChange event rather than being reliant on cells updating? I'm using XL2002 Look forward to your responses. Trevor Williams |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address & VLOOKUP in VBA
Hi Joel -- thanks for picking this up.
the short answer is "no". I get a Type Mismatch on the first line If InStr(Target, ":") 0 Also, I think this code will only work if the target cell is part of a range i.e. A1:B1 as it's looking for the colon. If the cell is a single cell i.e. A1 only then I presume I'd need to include and Else statement in the IF. e.g. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If InStr(Target, ":") 0 Then FindData = Left(Target, InStr(Target, ":") - 1) Else FindData = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) End If Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then Target = c.Offset(0, 1) End If End Sub Does your code return an absolute cell address? I need it to return A1 rather than $A$1 And, last of all, if the cell address is not in the tblHELPTXT then I need to return a "no help" message to the text box. Thanks again. Trevor "Joel" wrote: Does this work? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If InStr(Target, ":") 0 Then FindData = Left(Target, InStr(Target, ":") - 1) Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then Target = c.Offset(0, 1) End If End If End Sub "Trevor Williams" wrote: Hi All I have a sheet that contains a textbox that displays help text depending on the selected cell. Currently the text is selected via a lookup formula based on another cells value. This cell is updated via the Worksheet_SelectionChange event -- so: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A3") = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) End Sub Then Cell C3 uses the following formula to lookup the text: =VLOOKUP((IF(ISERROR(LEFT(A3,FIND(":",A3)-1)),A3,LEFT(A3,FIND(":",A3)-1))),tblHELPTXT,2,FALSE) Is there a way to do this lookup in the Worksheet_SelectionChange event rather than being reliant on cells updating? I'm using XL2002 Look forward to your responses. Trevor Williams |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Address & VLOOKUP in VBA
Hi Joel -- I think I've cracked it.
Let me know if you think I could refine it. Thanks Trevor Private Sub Worksheet_SelectionChange(ByVal Target As Range) myAddress = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) If InStr(myAddress, ":") 0 Then FindData = Left(myAddress, InStr(myAddress, ":") - 1) Set c = Sheets("Sheet1").Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _ LookIn:=xlValues, lookat:=xlWhole) Else Set c = Sheets("Sheet1").Range("tblHELPTXT").Resize(, 1).Find(what:=myAddress, _ LookIn:=xlValues, lookat:=xlWhole) End If If Not c Is Nothing Then MsgBox (c.Offset(0, 1)) Else MsgBox ("No Help") End If End Sub "Joel" wrote: Does this work? Private Sub Worksheet_SelectionChange(ByVal Target As Range) If InStr(Target, ":") 0 Then FindData = Left(Target, InStr(Target, ":") - 1) Set c = Range("tblHELPTXT").Resize(, 1).Find(what:=FindData, _ LookIn:=xlValues, lookat:=xlWhole) If Not c Is Nothing Then Target = c.Offset(0, 1) End If End If End Sub "Trevor Williams" wrote: Hi All I have a sheet that contains a textbox that displays help text depending on the selected cell. Currently the text is selected via a lookup formula based on another cells value. This cell is updated via the Worksheet_SelectionChange event -- so: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A3") = Target.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) End Sub Then Cell C3 uses the following formula to lookup the text: =VLOOKUP((IF(ISERROR(LEFT(A3,FIND(":",A3)-1)),A3,LEFT(A3,FIND(":",A3)-1))),tblHELPTXT,2,FALSE) Is there a way to do this lookup in the Worksheet_SelectionChange event rather than being reliant on cells updating? I'm using XL2002 Look forward to your responses. Trevor Williams |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get the address of the cell found our by vlookup | Excel Programming | |||
retreiving cell address of vlookup value | Excel Worksheet Functions | |||
How do I get the cell address of a VLOOKUP reference? | Excel Worksheet Functions | |||
Cell address from Vlookup | Excel Programming |