Replace hardcoded cell reference

  • Thread starter Thread starter T. Valko
  • Start date Start date
T

T. Valko

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
 
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
 
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
 
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
 
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
 
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.
 
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
 
Thanks, Martin! Works good.

Biff

Martin Fishlock said:
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
Click to expand...
 
Back
Top