Pull data from a corresponding cell in row A in last edited row and insert into cell A1

Question

I am looking for a script that will pull data from last edited cell into Cell B1 of active sheet and also look up data from cell in column A and then display it in cell A1.

So far I've got this to pull last edited cell into B1 and it works fine but I cannot figure out how to then go back from that point to row A and display the other info.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("F13:W9910")) Is Nothing Then
        ActiveSheet.Range("B1").Value = Target.Value
    End If
End Sub

In the attached picture if I add any numbers in section called trays completed (in red) to display in B1 and then look up number in Sap column and display the number in cell A1

Attached Image

score 2 · Accepted Answer · answered Jun 01 '18 at 02:05

2

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("F13:W9910")) Is Nothing Then
        Me.Range("B1").Value = Target.Value
        Me.Range("A1").Value = Target.Entirerow.Cells(1).Value
    End If   
End Sub

Note when in a sheet code module you can refer to the worksheet using Me Also be aware that Target might be >1 cell and your code might need to handle that.

answered Jun 01 '18 at 02:05

Tim Williams

154,628
8
97
125

Is Me necessary when Range with no defined parent on a worksheet's private code sheet always belongs to that worksheet? [this](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) – Jun 01 '18 at 02:10
2

@Jeeped - no it's not, but I like to use it for the sake of being more explicit (and after spending years on SO telling folk to never use unqualified ranges) – Tim Williams Jun 01 '18 at 02:33

urdearboy · Answer 2 · 2018-06-01T02:22:45.867

-1

This will take the corresponding value in A and place it in A1. I only added one line of code to yours.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("F13:W9910")) Is Nothing 
Then
    ActiveSheet.Range("B1").Value = Target.Value
    ActiveSheet.Range("A1").Value = ActiveSheet.Range("A" & Target.Row)  
End If
End Sub

edited Jun 01 '18 at 02:22

answered Jun 01 '18 at 02:04

urdearboy

14,439
5
28
58

Another sub procedure (bulk data load perhaps...?) could conceivably write a value into F13:W9910 and trigger this worksheet's Worksheet_Change when this worksheet is not the ActiveSheet. – Jun 01 '18 at 02:18

Pull data from a corresponding cell in row A in last edited row and insert into cell A1

2 Answers2