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

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

"Mike P" wrote:

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

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

"Joel" wrote:

> 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
>
> "Mike P" wrote:
>
> > 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
> >

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..

"OssieMac" wrote:

> 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
>
> "Joel" wrote:
>
> > 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
> >
> > "Mike P" wrote:
> >
> > > 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
> > >