Formula to show last activated cell in an array

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
I have an array of cells B4:E4, I was wondering is there a formula or some code which will populate cell G4 with the last cell I have activated within the array. So once I click any of the cells in the array of B4:E4 cell G4 will change with some form of identifier, let’s say 1 for B4, 2 for C4 and so on

thanks
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:E4")) Is Nothing Then
      Range("G4") = Split(Target.Address(1, 0), "$")(0)
   End If
End Sub
This needs to go in the relevant sheet module.
 

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:E4")) Is Nothing Then
      Range("G4") = Split(Target.Address(1, 0), "$")(0)
   End If
End Sub
This needs to go in the relevant sheet module.
Thanks thats nearly it one stupid thing I forgot, It actually needs to return the actual cell reference, it looks to be just returning the column Range, or even return a 1 for B4, 2 for C4 etc
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
If you want the cell reference use
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:E4")) Is Nothing Then
      Range("G4") = Target.Address
   End If
End Sub
 
Solution

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

That has worked perfectly thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff,
I made some additions to this so it took into account a lot more Cells. However I have realised that i actually now want to return the Target address - 1 row, so lets say cell AE17 is selected it returns the value AE16. does that make sense?

Here is my code now

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updates lookup values for column AV to then populate relevant selections
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("AC17:AF19")) Is Nothing Then
      Range("AV19") = Target.Address
      End If
   If Not Intersect(Target, Range("AC26:AF28")) Is Nothing Then
      Range("AV28") = Target.Address
        End If
If Not Intersect(Target, Range("AC35:AF37")) Is Nothing Then
      Range("AV37") = Target.Address
  End If
If Not Intersect(Target, Range("AC44:AF46")) Is Nothing Then
      Range("AV46") = Target.Address
  End If
   If Not Intersect(Target, Range("AC53:AF55")) Is Nothing Then
      Range("AV55") = Target.Address
  End If
       If Not Intersect(Target, Range("AC62:AF64")) Is Nothing Then
      Range("AV64") = Target.Address
  End If
      If Not Intersect(Target, Range("AC71:AF73")) Is Nothing Then
      Range("AV73") = Target.Address
  End If
      If Not Intersect(Target, Range("AC80:AF82")) Is Nothing Then
      Range("AV82") = Target.Address
  End If
      If Not Intersect(Target, Range("AC89:AF91")) Is Nothing Then
      Range("AV91") = Target.Address
  End If
End Sub
 

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
Also does the above code not work on merged cells?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,980
Office Version
  1. 365
Platform
  1. Windows
If you use
VBA Code:
Target.Offset(1).Address
it will give you the address of the cell above the target cell.

As for merged cells, they are an abomination & should be avoided like the plague.
 

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
If you use
VBA Code:
Target.Offset(1).Address
it will give you the address of the cell above the target cell.

As for merged cells, they are an abomination & should be avoided like the plague.
Thanks for the very simple solution I am clearly an idiot for not thinking that, much appreciated. And as for the merged cells yes they do not make life easy!
 

Forum statistics

Threads
1,136,434
Messages
5,675,846
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top