Getting a value from a known address a few columns over

concreteinterface

Board Regular
Joined
Jul 10, 2008
Messages
140
I didn't really know how to title this but here is the code I'm piecing together.



Code:
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

concreteinterface

Board Regular
Joined
Jul 10, 2008
Messages
140
Code update:



Code:
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

Board Regular
Joined
Jul 10, 2008
Messages
140
Got it... Don't mind me. I'm just talking to my self over here...

Code:
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

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
Concreteinterface,

you could use offset-

Code:
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

Code:
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
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
BTW, I would drop the whole GoTo thing just exit the sub.

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

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

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