473,320 Members | 1,982 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update data in Excel with VBA

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.
Aug 27 '10 #1
6 1981
Stewart Ross
2,545 Expert Mod 2GB
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
Aug 27 '10 #2
kadghar
1,295 Expert 1GB
If you mean, reading only the last entry of each costumer, you can add a column in excel with Count.If(ID,range above), and Sort.
Sep 8 '10 #3
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.Range("E4").Value)
If Not s Is Nothing Then ws.Range("A1:E1").Copy ws.Cells(s.Row, 1)



best regards
Jan
Attached Images
File Type: jpg Excel.jpg (21.3 KB, 151 views)
Sep 8 '10 #4
pod
298 100+
I am not certain what you really want but here goes nothing
...
how about this
Expand|Select|Wrap|Line Numbers
  1. ' if the customer ID matches, copy the values 
  2. for rw = 4 to 65000
  3.   if cells(rw,1).value = cells(1,1).value  then ' the id
  4.     cells(rw,4).value = cells(1,4).value' the id
  5.     cells(rw,5).value = cells(1,5).value
  6.   end if
  7. next
  8.  
  9.  
Sep 8 '10 #5
Yes! thank You. I've been working with this problem for nearly 3 months. This code works perfect!
Sep 9 '10 #6
pod
298 100+
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.
Sep 9 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: hch | last post by:
dataAdapter.Update(data, "TableName") won’t work! I was about to deploy my first website on the Internet only to discover that the dataAdapter.Update() throws the Server Error in the third...
0
by: q8z | last post by:
private OleDbDataAdapter daClient; private OleDbDataAdapter daAdd; private OleDbDataAdapter daDelete; private string dataSource; private DataSet dataSet; private System.Windows.Forms.Button...
5
by: Juande | last post by:
Hello, I have a main page where the user log in to access to the data in SQL Server, the selected data by the user will be exported in a .MDB file into the server, I want that generated file...
5
by: jason | last post by:
Hi, all How can I update data (multiple rows, but not every rows) using dataset in datagrid? I mean is there any way I can let datagrid know which row(s)/column(s) has been modified and update...
1
by: chris.thompson13 | last post by:
Any relevant advice would be most appreciated for the following problem I have. I have a query that draws its data from 7 other queries. I would like to be able to edit data through that query....
6
by: shil | last post by:
Hi, I am writing a windows app in .net 2003. I have a datagrid which gets data from a storedprocedure. My question is how can I update the data in the datagrid? I want to call another...
1
by: farid25 | last post by:
update data in datagrid asp vb.net -------------------------------------------------------------------------------- Hi, all i got data base contain 3 tables t_students student_id ...
1
by: progvar | last post by:
Hi i want to update data in database using datagridview actually i am displaying the data in datagridview and after that i want to update some rows data directly modifying the displayed data into...
2
by: lavsaxena | last post by:
Can we edit a worksheet of excel through spreadsheet::writeExcel. Because when I am using my $workbook = Spreadsheet::WriteExcel->new('D:\test_result.xls'); this statement is creating the...
1
by: Rishabh Indianic | last post by:
hi, i am trying to update data stored in data base with following code i am able to show data from database but i can not able to update it. int Theme_ID = Convert.ToInt32(textBox1.Text);...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.