Getting a value from a known address a few columns over

concreteinterface

Board Regular
Joined
Jul 10, 2008
Messages
144
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,508
Messages
6,137,036
Members
450,041
Latest member
MM2024

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
Back
Top