VBA extract column/ row value from address string

[Hile_Troy]

Hi Folks

I am trying to work out how to get column/ row values from an address string. the relevant code I am using is:-

DivCell = Cells(DivRow, DivCol).Address
EndCell = Cells(ExpRow, ColMax).Address
If Not Intersect(Target, Range(DivCell, EndCell)) Is Nothing Then
Target.Address... 'This is where the problems start
End If

The code works fine, I can display the address in a message box and all is good, what I want to do is to insert a value into a cell that is in the same column but a different row than Target.

I cannot use a fixed offset as that would not work for my project.

Can anyone help? Please.

 

[SteveO59L]

What value are you wanting to populate?

 

[srikanth.nm]

you could extract the row number from an address text using http://www.ozgrid.com/VBA/ExtractNum.htm . Then, you could use InStr() to search for the number within the address string and use Left() to extract the column number.
Left(strAddress, InStr(1, strAddress, intRow, vbTextCompare) - 1)

 

[Hile_Troy]

What value are you wanting to populate?

I want to detect when any value in any of seven different cells in the same column is changed, and when it is, put the word 'amend' in an eighth cell, in the same column

 

[SteveO59L]

A specific group of 7 cells?

 

[Hile_Troy]

Right, the whole picture. I am working with a spreadsheet that shows training modules and associated details down the left (600+ rows), and role details along the top(150+ columns). In the middle is a matrix relating a specific role with a specific training module. As such, with all of the details (headcount, division, business area, team name, role name etc) of a particular role all being contained in the same column, and the spreadsheet changing on an almost daily basis I want to automate as much as possible, the spreadsheet gets updated to a database regularly and the amend tag tells the vba that updates the database to alter that record.

Hope all that makes sense

 

[SteveO59L]

And what is it you would like to do?

 

[Hile_Troy]

I would like, when any of the role details are changed (Division in row1, business area in row2, team name in row3 etc) I want the word amend to autopopulate in row 7 in the same column as the change.

(when I work that out I can adapt it to fit the modules down the side)

 

[Hile_Troy]

I'm gonna have a tinker with srikanth.nm's solution above, that may do the trick. (Thanks srikanth.nm)

 

[SteveO59L]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 7 Then
Cells(7, Target.Column) = "Amend"
End If

End Sub