Getting a value from a known address a few columns over

[concreteinterface]

I didn't really know how to title this but here is the code I'm piecing together.

Option Explicit


Public xfrLastCell As String
Public xfrActiveCell As String
Public PMLogNum As String
Public JobNumber As String


Private Sub Worksheet_Change(ByVal Target As Range)


If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
    xfrLastCell = xfrActiveCell
    xfrActiveCell = ActiveCell.Address


    If Intersect(Target, Me.Range("Q:Q")) Is Nothing Then GoTo ExitThis
        PMLogNum = Sheets("PM Log").Range("Z1") + 1
        Sheets("PM Log").Range("C" & PMLogNum) = Format(Time, "hh:mm")
        Sheets("PM Log").Range("D" & PMLogNum) = JobNumber
        Sheets("PM Log").Range("E" & PMLogNum) = Sheets("Work Order Log").Range(xfrLastCell).Value
        Sheets("PM Log").Range("B" & PMLogNum) = Format(Date, "mm-dd-yy")


ExitThis:
    
End Sub

I want to move 5 columns to the left of xfrLastCell and grab a value. As you can probably tell by the title, I'm kind of a a loss for how to research this problem. Thanks for the help.

 

[concreteinterface]

Code update:

Option Explicit


Public xfrLastCell As String
Public xfrActiveCell As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
        xfrLastCell = xfrActiveCell
        xfrActiveCell = ActiveCell.Address


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)


Dim PMLogNum As String
Dim JobNumber As String
Dim PMLogEntry As String


PMLogEntry = Sheets("Work Order Log").Range(xfrActiveCell).Value
        
    If Intersect(Target, Me.Range("Q:Q")) Is Nothing Then GoTo ExitThis
        PMLogNum = Sheets("PM Log").Range("Z1") + 1
        Sheets("PM Log").Range("E" & PMLogNum) = PMLogEntry
        Sheets("PM Log").Range("C" & PMLogNum) = Format(Time, "hh:mm")
        'Sheets("PM Log").Range("D" & PMLogNum) = JobNumber
        Sheets("PM Log").Range("B" & PMLogNum) = Format(Date, "mm-dd-yy")


ExitThis:


End Sub

 

[concreteinterface]

Got it... Don't mind me. I'm just talking to my self over here...

Option Explicit


Public xfrLastCell As String
Public xfrActiveCell As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    If xfrActiveCell = "" Then xfrActiveCell = ActiveCell.Address
        xfrLastCell = xfrActiveCell
        xfrActiveCell = ActiveCell.Address


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)


Dim PMLogNum As String
Dim JobNumber As String
Dim PMLogEntry As String
Dim aCell As Range


Set aCell = Range(xfrActiveCell)


PMLogEntry = Sheets("Work Order Log").Range(xfrActiveCell).Value
JobNumber = Range(aCell.Offset(0, -5).Address)
        
    If Intersect(Target, Me.Range("Q:Q")) Is Nothing Then GoTo ExitThis
        PMLogNum = Sheets("PM Log").Range("Z1") + 1
        Sheets("PM Log").Range("E" & PMLogNum) = PMLogEntry
        Sheets("PM Log").Range("C" & PMLogNum) = Format(Time, "hh:mm")
        Sheets("PM Log").Range("D" & PMLogNum) = JobNumber
        Sheets("PM Log").Range("B" & PMLogNum) = Format(Date, "mm-dd-yy")


ExitThis:


    
End Sub

 

[farmerscott]

Concreteinterface,

you could use offset-

xfrLastCell.offset(0,-5).value

Note - this line of code will error if xfrLastCell is in Cols A-D as this takes the reference off the sheet.



Also- you will start to have some issues with

PMLogNum = Sheets("PM Log").Range("Z1") + 1

with that line Excel will be looking for a number for PMLogNum. You have it declared as a string.

Hope that helps,

FarmerScott

 

[Scott Huish]

BTW, I would drop the whole GoTo thing just exit the sub.

If Intersect(Target, Me.Range("Q:Q")) Is Nothing Then Exit Sub