Automated Search and Replace; Sheet2 to Sheet1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike P - 21 Sep 2007 11:56 GMT

I have a customer spreadsheet with over 5000 rows in it. Each week I get
information that needs to be updated (100s of rows). The new information
contains Customer Number and Paydate. I manually search for the customer
number, located in column C, and then update the PayDate, located in Column M.

Is there any way I can write some VBA that will do a massive search and
replace. I have the new info in a Text File, but I am thinking I could
import it into Sheet2 with 2 columns, customerNumber, and PayDate. Then just
search Sheet1 customer number, and when found replace paydate… but I am new
to Excel programming and have no idea of how to implement this.

Is this even possible???? Thank you in advance for your time!
Mike P

Reply to this Message

Joel - 21 Sep 2007 13:08 GMT

This code should get you started.

Sub test()

With Sheets("Sheet2")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set CustNoRangeSh2 = _
.Range(.Cells(1, "A"), .Cells(LastRow, "A"))
End With
With Sheets("Sheet1")
LastRow = .Cells(Rows.Count, "C").End(xlUp).Row
Set CustNoRangeSh1 = _
.Range(.Cells(1, "C"), .Cells(LastRow, "C"))

For Each Cell In CustNoRangeSh1
Set c = CustNoRangeSh2.Find(what:=Cell, _
LookIn:=xlValues)
If Not c Is Nothing Then
PayDate = c.Offset(rowoffset:=0, _
columnoffset:=1).Value
Cell.Offset(rowoffset:=0, _
columnoffset:=10).Value = PayDate
Else
MsgBox ("Cannot find Customer No = " & Cell)
End If
Next Cell
End With
End Sub

> I have a customer spreadsheet with over 5000 rows in it. Each week I get
> information that needs to be updated (100s of rows). The new information
[quoted text clipped - 9 lines]
> Is this even possible???? Thank you in advance for your time!
> Mike P

Reply to this Message

OssieMac - 22 Sep 2007 01:14 GMT

A comment to Joel.

Don't you think that it would be better to pick up the customer number from
sheet 2 and find them on sheet 1? The reason that I say this is Mike said he
has 5000 records on sheet 1 and he has to make 100's of changes which
suggests that there is less than 1000 to be checked. The code has a msgbox if
not found and it could drive the user insane with some 4000+ not found.
However, if there is a customer code on the new sheet then the user would
want to know if it is not found in the master sheet and also the msgbox would
not display if all found.

Regards,

OssieMac

> This code should get you started.
>
[quoted text clipped - 38 lines]
> > Is this even possible???? Thank you in advance for your time!
> > Mike P

Reply to this Message

Joel - 22 Sep 2007 03:48 GMT

I think one of the hardest point of answering Postings is the interpretation
of the questions. I may an assumption that may be wrong. I thought the
update list contained all the accounts. Usually when you get a monthly
report it contains all the active accounts. You may be right that it is just
an update list where it would be better to do it your way..

> A comment to Joel.
>
[quoted text clipped - 53 lines]
> > > Is this even possible???? Thank you in advance for your time!
> > > Mike P

Reply to this Message