Code to update asset numbers automatically

[irrefutable14]

I have a list of asset numbers that are on sheet 1, column A. Then on Sheet 2, column A I have to enter the asset number (not in the same order listed in sheet 1, column a) along with the member its assigned to. However sometimes the asset numbers change... What I would like to have excel do is; whenever an asset number is updated in sheet 1 column A; I want it to automatically find that old number on sheet 2 column A and replace/update it with the new asset number... is that possible???

 

[Rick Rothstein]

Re: is this possible?!?!?!?!?

I think this event code should do what you want...

[table="width: 500"]
[tr]
	[td]Dim CurrentCellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
  Sheets("Sheet2").Columns("A").Replace CurrentCellValue, Target.Value, , xlWhole, , , False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count = 1 Then CurrentCellValue = Target.Value
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code (Sheet1 in your case) and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[irrefutable14]

Re: is this possible?!?!?!?!?

Thanks a lot for your response! When I inserted the code here is what happened.
Whatever number I'd input in Sheet 1 Column A:2 it would take that number and mirror it into the entire column A on sheet 2 and they are all red.
I'd put 123456 in S1 Column A:2 and this would happen

ex.
Sheet 2-
123456
123456
123456
123456
123546

 

[My Aswer Is This]

Re: is this possible?!?!?!?!?

I had another solution but had to save the current value into a cell and refer to the cell value. How is it possible the current value was saved as variant and then used in another sub routine. I thought all dim values were lost when switching to another sub routine? But I did try yours and it worked.

I think this event code should do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dim CurrentCellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
  Sheets("Sheet2").Columns("A").Replace CurrentCellValue, Target.Value, , xlWhole, , , False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count = 1 Then CurrentCellValue = Target.Value
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code (Sheet1 in your case) and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[JoeMo]

Re: is this possible?!?!?!?!?

Here's an alternative you can try. Replace the existing code per Rick's instructions.

Dim rowNum1 As Long
Dim rowNum2 As Long

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing And Target.Row = rowNum1 Then
    Sheets("Sheet2").Cells(rowNum2, "A").Value = Target.Value
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
    If Intersect(Target, Columns("A")).Count > 1 Then
        MsgBox "Change only one cell at a time"
        With Application
            .EnableEvents = False
            .CutCopyMode = False
            .EnableEvents = True
            Me.Range("A1").Select
            Exit Sub
        End With
    End If
        rowNum1 = Target.Row
        On Error Resume Next
        rowNum2 = Application.Match(Target.Value, Sheets("Sheet2").Columns("A"), 0)
        If IsError(rowNum2) Then Exit Sub  'value selected in Sheet1 has no match in sheet2
End If
End Sub

 

[My Aswer Is This]

Re: is this possible?!?!?!?!?

Ricks script worked for me.

I think this event code should do what you want...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Dim CurrentCellValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
  Sheets("Sheet2").Columns("A").Replace CurrentCellValue, Target.Value, , xlWhole, , , False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count = 1 Then CurrentCellValue = Target.Value
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code (Sheet1 in your case) and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[My Aswer Is This]

Re: is this possible?!?!?!?!?

Now with one little bit of help from Rick. This is how I would have written the script.
Was not aware variant values could be saved from One sub routine to another:

Dim ans As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
ans = Target.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    For Each c In Sheets(2).Range("A1:A" & Lastrow)
        If c.Value = ans Then c.Value = Target.Value
    Next
End If
End Sub

 

[Rick Rothstein]

Re: is this possible?!?!?!?!?

Ricks script worked for me.

And it worked for me when I tested it before posting it as well.

Now with one little bit of help from Rick. This is how I would have written the script.
Was not aware variant values could be saved from One sub routine to another:

If you Dim a variable outside of any procedures (Subs and Functions) and do so at the top of the module it is in, then it becomes a global variable and is available to all procedures within that module. If you declare it with keyword Public instead of Dim within a module, it becomes available to all procedures in all modules within that project except for modules where Option Private Module is in effect.

 

[My Aswer Is This]

Re: is this possible?!?!?!?!?

Thanks for that bit of information. And your script was a lot shorter then mine. I need to learn more about "Replace"

And it worked for me when I tested it before posting it as well.




If you Dim a variable outside of any procedures (Subs and Functions) and do so at the top of the module it is in, then it becomes a global variable and is available to all procedures within that module. If you declare it with keyword Public instead of Dim within a module, it becomes available to all procedures in all modules within that project except for modules where Option Private Module is in effect.

 

[irrefutable14]

Re: is this possible?!?!?!?!?

hMM... Maybe you could take a look at my sheet. Could be because I have other formulas in place that its not working. I'm not totally sure... Never worked with codes before. Here is the link. Maybe you could take a look it to get a better idea. I do know on a new workbook it worked perfectly fine. just not on this one.
also if I wanted to add this code on the same sheet would it work & how do I put them together??

Private Sub Worksheet_Change(ByVal Target As Range)

'   Make sure it only runs when data is inserted in a single cell
    If Target.Count > 1 Then Exit Sub

'   Only run when columns A or B are updated with a value
    If Target.Column <= 2 And Target <> "" Then
'       If column A was updated, move to column B in same row
        If Target.Column = 1 Then Target.Offset(0, 1).Select
'       If column B was updated, move to column A in next row
        If Target.Column = 2 Then Target.Offset(1, -1).Select
    End If

End Sub

DOWNLOAD LINK
http://www.dropbox.com/s/xcsrsaoj2gsigvp/TabletCheckin_out.xlsm?dl=0