ExcelBanter - Find Values and Update

As Tim said, it would help to know what's going on in Columns D-F. This
problem is solved better with a macro. Suppose these columns have the most
recent dates, with the most recent date in Column D. Do the following:

1. Make sure the Control Toolbox toolbar is visible by clicking the
ViewToolbars menu items in Excel.

2. Click the Command Button icon on this toolbar to create a command button.

3. Right click the new button and select Properties, which will bring up the
properties window. Change the Name property to "cmdUpdate" and the Caption
property to "Update". If you want, you can change the appearance, such as
font, using the other properties.

4. Right the button again and select View Code. This puts you in the VBA
editor, where you'll see

Private Sub cmdUpdate_Click()

End Sub

5. Copy the following code into this procedure

Private Sub cmdUpdate_Click()

Const iDATE_COLUMN_OFFSET As Integer = 2
Const iNUMBER_DATE_COLUMNS As Integer = 3
Const iNEWVALS_COLUMN_OFFSET As Integer = 6

Const strHOMECELL As String = "B1"

Dim i As Integer
Dim j As Integer
Dim iNew_count As Integer

Dim iSerial_num As Long

Dim rngHome As Range

Set rngHome = ThisWorkbook.ActiveSheet.Range(strHOMECELL)

With rngHome
' Determine the number of update items
iNew_count = 0

Do Until .Offset(iNew_count, iNEWVALS_COLUMN_OFFSET).Value = ""
iNew_count = iNew_count + 1
Loop

' Now check each serial number to see if needs an update
iSerial_num = 0

Do Until .Offset(iSerial_num, 0).Value = ""
' Compare this serial number against those in the update list
For i = 0 To iNew_count - 1
If .Offset(i, iNEWVALS_COLUMN_OFFSET).Value = _
.Offset(iSerial_num, 0).Value Then
' Found a match
GoSub UpdateDates
Exit For
End If
Next i

iSerial_num = iSerial_num + 1
Loop
End With

Exit Sub

UpdateDates:

With rngHome
' Move the old date values one column over
For j = iNUMBER_DATE_COLUMNS - 1 To 1 Step -1
.Offset(iSerial_num, iDATE_COLUMN_OFFSET + j).Value = _
.Offset(iSerial_num, iDATE_COLUMN_OFFSET + j - 1).Value
Next j

' Put the new date in the first of the date columns
.Offset(iSerial_num, iDATE_COLUMN_OFFSET).Value = _
.Offset(i, iNEWVALS_COLUMN_OFFSET + 1).Value
End With
Return

End Sub

6. Return to the spreadsheet and click the Exit Design Mode button on the
Control Toolbox toolbar to make the button active. To update the list, just
click the Update button you have created.

Note that the actual updating all occurs in the subroutine UpdateDates. I've
written to update based on my supposition for the function of columns D-F.
Change the subroutine to accomplish whatever else you might want to do.

I hope this helps. Good luck.

"Tim" wrote:

What determines whether D, E or F gets the date from I ?

VLOOKUP seems like a good candidate here.

Tim

"QTGlennM" wrote in message
ups.com...
I have a worksheet that has thousands of serial numbers in column B
that have a date to be updated daily in column D, E, and F. Currently
I have to go through manually and find and match the serials from H to
B, then update the corresponding Column D,E, or F. How do I match
values from H with values from B and update dates in D or E or F from
I. Any help would be greatly appreciated I don't really know what I am
doing....

A B C D E F G H I
123 456 dateB
456 912 dateC
678
912
243

Thanks
Glenn