Update data in Excel with VBA

[Jan Gronqvist]

I have this Excel spredsheet with 4 columns. In colum A i have "CustomerID " in col 2 Customer in 3 data.

My problem is that the same CustomerID may appear att several rows, because the customer data may appear several times. Now i'm seekeing for a VBA code that manages to update those rows containing a certain customers data.

[Stewart Ross]

Hi. If you could post what you have tried then perhaps we can assist, but as we don't have your application in front of us it is impossible to give you 'code' which will do what you want when you tell us so little about it.

On the face of it if you have duplicate data about particular customers it is simply not possible to update the rows reliably. In database terms you have created an update anomaly by allowing duplication of customer IDs, and until you resolve this (by specifying additional information which uniquely identifies a row, or by removing the duplicates) you cannot update the rows concerned.

-Stewart

[kadghar]

If you mean, reading only the last entry of each costumer, you can add a column in excel with Count.If(ID,ran ge above), and Sort.

[Jan Gronqvist]

Hello!

There are 5 columns in this example. In this example I want to update the data for Customer 1 (new data in columns Col b and Col c).The new data is entered in the grey field above the database.

I have this code that manages to update only one row (the first). Before I run this macro another macro runs a "SORT" macro so that I get all records with (in this case) CustomerID 1 in rows 1- 10 (if there is ten records for the customer with CustomerID1).

Dim s As Range
Dim ws As Worksheet
Set ws = Worksheets(1)
Set s = ws.Range("E4:E" & ws.Rows.Count). Find(What:=ws.R ange("E4").Valu e)
If Not s Is Nothing Then ws.Range("A1:E1 ").Copy ws.Cells(s.Row, 1)



best regards
Jan

[pod]

mmm... not sure what you need

I am not certain what you really want but here goes nothing
...
how about this

' if the customer ID matches, copy the values 
for rw = 4 to 65000
  if cells(rw,1).value = cells(1,1).value  then ' the id
    cells(rw,4).value = cells(1,4).value' the id
    cells(rw,5).value = cells(1,5).value
  end if
next

[Jan Gronqvist]

Yes! thank You. I've been working with this problem for nearly 3 months. This code works perfect!

[pod]

I'm glad it helps.

You might want to change the "65000" to a lower number if you know the maximum number of rows you will have in your worksheet.