| Home |
| Search |
| Today's Posts |
|
#4
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 |
| 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 | |||
Threaded Mode
