I have a file with two worksheets. Column C of Sheet1 has FILE IDs, as does Column D of Sheet2. I would like to find any matches and then copy all of the data from the matching row in Sheet1 to the end of the matching row on Sheet2.
3 1358
I have a file with two worksheets. Column C of Sheet1 has FILE IDs, as does Column D of Sheet2. I would like to find any matches and then copy all of the data from the matching row in Sheet1 to the end of the matching row on Sheet2.
Hi,
You can assign a range to a variant, so you can work it with FOR or DO. Try to get the info you want into an array and after that send it to Excel. If you use Excel during the process, it'll slow things down.
Something like this should do. - sub matchData()
-
dim a
-
dim b
-
dim c() as string
-
dim i as long
-
dim j as long
-
dim k as long
-
With Worksheets(1)
-
a = range(.cells(1,1), .cells(1,1).end(-4121).end(-4161))
-
End With
-
With Workseets(2)
-
b = range(.cells(1,4), .cells(1,4).end(-4121))
-
End With
-
ReDim Preserve c (1 to Ubound(a,2)-3)
-
For i = 1 To Ubound(a)
-
j = 1
-
Do
-
If b(j,1) = a(i,3) Then
-
For k = 1 To Ubound(c)
-
c(k) = a(i,k+3)
-
Next
-
With Worksheets(2)
-
Range(.Cells(j,5),.Cells(j,4+ubound(c)) = c
-
End With
-
Exit Do
-
End If
-
j = j + 1
-
Loop Until j > Ubound(b)
-
Next
-
End Sub
HTH
thanks, I will give that a shot.
If you are having really much, I'ld assign the cell content into variables and let it mtch the values of them in the backround. Should be a little faster if you need it more often
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Phil Powell |
last post by:
/*--------------------------------------------------------------------------------------------------------------------------------
Parameters:
$formField1: The name of the first array
$formField2:...
|
by: Ilan |
last post by:
Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though...
|
by: Megan |
last post by:
Hi everybody-
I'm helping a friend with a music database. She has an old one and is
creating a new one. She wants to compare records and fields in the old
database with records and fields in the...
|
by: Niklas |
last post by:
Hi
Before I start coding I need to be sure that nobody else has not done it yet
and that I can use it.
I need an import utility which import data from Excel to a database or some
object in...
|
by: DONE1 |
last post by:
I wrote a code as follows to compare 2 tables on diff worksheet.
Each work sheet has about 6000 Rows.
The code works but appears to Work very slow.
Is there a more efficient way to do it.
Sub...
|
by: Tbell |
last post by:
Hello
I admit I am somewhat of a beginner at this, but I am amazed as to why none of the examples and other help I have found have allowed me to get this working.
I have two tables, table1 and...
|
by: lindabaldwin |
last post by:
Hello everyone,
I am fairly new to VBA. I have a worksheet in Excel, named "Data Sheet" from which I am trying to query data. This worksheet contains the following data: unit (column A), date...
|
by: grego9 |
last post by:
I have a bit of visual basic code in an excel spreadsheet that I need some help with. I am attempting to search a file called TO Cancellations2.xls for counterparty names and for each counterparty...
|
by: moutoncito |
last post by:
Hi all,
I need some help with a VBA code. I have tried differents codes that i fund in the forum but they didn t match with what i m looking for. So i will explain what i have , what i need and...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |