Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Hi Folks!
Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Yes, it is about time! <G I would probably use the Find Method. AFAIK -
VBA doesn't have much support for array formulae, although I know you can use the Evaluate method with Sumproduct (you can probably find some good examples of that in this forum). Sub Test() Dim strAddress As String On Error Resume Next With Sheets("Sheet1").Range("A1:A100") strAddress = .Find(what:=Range("D1").Value, _ after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, _ searchorder:=xlByRows, searchdirection:=xlPrevious, _ MatchCase:=False, matchbyte:=False).Address End With On Error GoTo 0 If Len(strAddress) = 0 Then _ strAddress = "A1" End Sub "T. Valko" wrote: Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Hi Biff,
Have a look at the code below. It uses a loop looping backwards through the list to find the last matching cell in the list. I've added a little bit of extras for you to consider regarding constants and setting ranges. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Application.EnableEvents = False 'don't need not changing any data ' use constants to make it easier to change Const cszRange As String = "A1:A100" Const cszCell As String = "D1" ' variables Dim lRow As Long, lRowStart As Long, lrowEnd As Long Dim test As Variant ' may be a range... If Not (Intersect(Target, _ Union(Range(cszCell), Range(cszRange))) Is Nothing) Then 'in the area ' calc the start and stop rows ie can change the area lRowStart = Range(cszRange).Row lrowEnd = Range(cszRange).Rows.Count + lRowStart - 1 test = Range(cszCell).Value ' test cell For lRow = lrowEnd To lRowStart Step -1 ' work backwards If test = Cells(lRow, 1) Then Cells(lRow, 1).Activate Exit Sub End If Next lRow Cells(1, 1).Activate End If End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "T. Valko" wrote: Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim res as Variant, rng as Range If Target.Address = "$D$1" Then res = application.Match(Target,Range("A1:A100"),0) if iserror( res) then set rng = cells(1,1) else set rng = cells(res,1) end if Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "T. Valko" wrote in message ... Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
I believe he is looking for the *last* occurence of D1 in A1:A100.
"Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res as Variant, rng as Range If Target.Address = "$D$1" Then res = application.Match(Target,Range("A1:A100"),0) if iserror( res) then set rng = cells(1,1) else set rng = cells(res,1) end if Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "T. Valko" wrote in message ... Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Its a possibility or he is just using max to pick out the only unique match.
If he wants the last occurrence: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res As Variant, rng As Range If Target.Address = "$D$1" Then Set rng = Range("A1:A100").Find( _ What:=Target.Value, _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) If rng Is Nothing Then Set rng = Cells(1, 1) End If Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "JMB" wrote in message ... I believe he is looking for the *last* occurence of D1 in A1:A100. "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res as Variant, rng as Range If Target.Address = "$D$1" Then res = application.Match(Target,Range("A1:A100"),0) if iserror( res) then set rng = cells(1,1) else set rng = cells(res,1) end if Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "T. Valko" wrote in message ... Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Yes, I see your point. I just assumed he wouldn't use an array formula to do
it instead of Match(....)+Row(A1)-1 - but it could go either way. Sometimes my paradigm shifts w/o a clutch. "Tom Ogilvy" wrote: Its a possibility or he is just using max to pick out the only unique match. If he wants the last occurrence: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res As Variant, rng As Range If Target.Address = "$D$1" Then Set rng = Range("A1:A100").Find( _ What:=Target.Value, _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) If rng Is Nothing Then Set rng = Cells(1, 1) End If Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "JMB" wrote in message ... I believe he is looking for the *last* occurence of D1 in A1:A100. "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res as Variant, rng as Range If Target.Address = "$D$1" Then res = application.Match(Target,Range("A1:A100"),0) if iserror( res) then set rng = cells(1,1) else set rng = cells(res,1) end if Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "T. Valko" wrote in message ... Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Thanks, Tom. Works just fine.
JMB wrote: I believe he is looking for the *last* occurence of D1 in A1:A100. Yes, that is correct. Thanks to everyone for their input. Biff "Tom Ogilvy" wrote in message ... Its a possibility or he is just using max to pick out the only unique match. If he wants the last occurrence: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res As Variant, rng As Range If Target.Address = "$D$1" Then Set rng = Range("A1:A100").Find( _ What:=Target.Value, _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) If rng Is Nothing Then Set rng = Cells(1, 1) End If Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "JMB" wrote in message ... I believe he is looking for the *last* occurence of D1 in A1:A100. "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim res as Variant, rng as Range If Target.Address = "$D$1" Then res = application.Match(Target,Range("A1:A100"),0) if iserror( res) then set rng = cells(1,1) else set rng = cells(res,1) end if Application.Goto _ Reference:=rng End If End Sub -- Regards, Tom Ogilvy "T. Valko" wrote in message ... Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replace hardcoded cell reference
Thanks, Martin! Works good.
Biff "Martin Fishlock" wrote in message ... Hi Biff, Have a look at the code below. It uses a loop looping backwards through the list to find the last matching cell in the list. I've added a little bit of extras for you to consider regarding constants and setting ranges. Option Explicit Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Application.EnableEvents = False 'don't need not changing any data ' use constants to make it easier to change Const cszRange As String = "A1:A100" Const cszCell As String = "D1" ' variables Dim lRow As Long, lRowStart As Long, lrowEnd As Long Dim test As Variant ' may be a range... If Not (Intersect(Target, _ Union(Range(cszCell), Range(cszRange))) Is Nothing) Then 'in the area ' calc the start and stop rows ie can change the area lRowStart = Range(cszRange).Row lrowEnd = Range(cszRange).Rows.Count + lRowStart - 1 test = Range(cszCell).Value ' test cell For lRow = lrowEnd To lRowStart Step -1 ' work backwards If test = Cells(lRow, 1) Then Cells(lRow, 1).Activate Exit Sub End If Next lRow Cells(1, 1).Activate End If End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "T. Valko" wrote: Hi Folks! Getting my "feet wet" in some VBA. (it's about time!) I'm playing around trying to do this based on another post. I have this formula in cell E1: =IF(ISNA(MATCH(D1,A1:A100,0)),"$A$1",ADDRESS(MAX(( A1:A100=D1)*(ROW(A1:A100))),1)) It returns a cell address. Then I have this event macro that uses the result of that formula: Private Sub Worksheet_Change(ByVal Target As Excel.Range) On Error GoTo enditall Application.EnableEvents = False If Target.Address = "$D$1" Then Application.Goto Reference:=ActiveSheet.Range(ActiveSheet.Range("E1 ")) End If enditall: Application.EnableEvents = True End Sub This works just fine but my question is how can I incorporate the formula (or maybe some better method) directly into the macro and not have to use the worksheet formula? Thanks! Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use Find without hardcoded value but a value from a cell | Excel Worksheet Functions | |||
Using a cell reference within a macro to find and replace | Excel Discussion (Misc queries) | |||
replace the letters (for collumn) & numbers(row no.) of cell reference only | Excel Programming | |||
find and replace cell reference to a new worksheet | Excel Worksheet Functions | |||
Replace a sheetname with a cell reference to link to another work. | Excel Worksheet Functions |