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

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

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

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

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

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

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

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

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