So I am writing a script to compare two excel files.
I'm using a For loop in the first workbook to get the references I want to find in the second workbook (6450 rows long so that no For loop, way to slow)
I have been looking for some way to use the VLOOKUP thing but i could not make it work Here is the code : - For i = 7 to numLines ''numLines is the number of used lines of the first workbook
-
-
If '''test to get out of the LOOP
-
objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("H"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("L"&i)="" Then
-
-
i = numLines
-
-
Else '' here i get the reference (the 6 first digits of the first workbook and I try to find it in the second)
-
-
If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)<>"" Then
-
-
Reference = Mid(objExcel.Workbooks(Str1).Sheets(1).Range("D"&i),1,6)
-
-
Set table_lookup = objExcel.Workbooks(Str1).Sheets(1).Range( "C1:C" & numLines2 )
-
cell = objExcel.Workbooks(Str2).WorksheetFunction.vlookup(Reference, table_lookup, 0, False)
-
MsgBox cell.row
-
MsgBox cell.column
-
-
End If
-
-
End If
-
-
Next
So I found the answer to my question, I switched to the "find" method instead of the vlookup that does not seem to work on vbscript :
Here is the code : -
-
For i = 7 to numLines
-
If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("H"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("L"&i)="" Then
-
i = numLines
-
Else
-
If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)<>"" Then
-
Reference = Mid(objExcel.Workbooks(Str1).Sheets(1).Range("D"&i),1,6)
-
Set r = objExcel.Workbooks(Str2).Sheets(1).Range( "C1:C" & numLines2 )
-
Set matched = r.Find(Reference)
-
If Not r.Find(Reference) Is Nothing Then
-
objExcel.Workbooks(Str1).Sheets(1).Range("R"&i).Value = matched.Offset(0,0).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("S"&i).Value = matched.Offset(0,1).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("T"&i).Value = matched.Offset(0,2).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("U"&i).Value = matched.Offset(0,3).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("V"&i).Value = matched.Offset(0,6).Value
-
End If
-
End If
-
End If
-
Next
-
-
1 1736
So I found the answer to my question, I switched to the "find" method instead of the vlookup that does not seem to work on vbscript :
Here is the code : -
-
For i = 7 to numLines
-
If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("H"&i)="" AND objExcel.Workbooks(Str1).Sheets(1).Range("L"&i)="" Then
-
i = numLines
-
Else
-
If objExcel.Workbooks(Str1).Sheets(1).Range("D"&i)<>"" Then
-
Reference = Mid(objExcel.Workbooks(Str1).Sheets(1).Range("D"&i),1,6)
-
Set r = objExcel.Workbooks(Str2).Sheets(1).Range( "C1:C" & numLines2 )
-
Set matched = r.Find(Reference)
-
If Not r.Find(Reference) Is Nothing Then
-
objExcel.Workbooks(Str1).Sheets(1).Range("R"&i).Value = matched.Offset(0,0).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("S"&i).Value = matched.Offset(0,1).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("T"&i).Value = matched.Offset(0,2).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("U"&i).Value = matched.Offset(0,3).Value
-
objExcel.Workbooks(Str1).Sheets(1).Range("V"&i).Value = matched.Offset(0,6).Value
-
End If
-
End If
-
End If
-
Next
-
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: barma16 |
last post by:
I've hit a bit of a brick wall here, and could use some advice. I have
an Access application whose output is a four-tab Excel spreadsheet
where three of the four tabs are the result of database...
|
by: c.verma |
last post by:
I have a web application. There is a page which has a datagrid on
it.The datagrid displays the data that comes from SAP. SAP sends the
chinese characters to this grid. Before I display CHinese...
|
by: Girish |
last post by:
Hi,
I want to embed a txt document into an excel using python.
Here is my code, but i get an error message
===================================================
Traceback (most recent call...
|
by: bebek_tetangga |
last post by:
Hello, I'm trying to open a .txt file in Excel using C#. Here is my
code:
clsWorkbook = clsExcel.Workbooks.Open(dailyPath + "\\070701N.txt", 2,
false, 5, "", "", true,...
|
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am...
|
by: wankhusairi |
last post by:
hallo sir .. i am still new on using vb and i have tried to load an excel using a visual basic interface.. but if i wanted to plot my excel data what should i do.. must i cahnge my program on loading...
|
by: DJK |
last post by:
Hi everyone,
I have created a button in Excel using C#(without using Window form)
and i want to perform certain actions on clicking that button. My code is as follows:
...
|
by: rameshmandapati |
last post by:
hi
i have a problem with Excel.
iam adding data to a cell in Excel using java script
var excel = new ActiveXObject ("Excel.Application");
but i want image also i search in google i...
|
by: =?Utf-8?B?U2hlZXMgQWJpZGk=?= |
last post by:
I read an article on the link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;306572 related to
reading data from Excel using OLEDB The topic's heading is: How to query and
display excel...
|
by: Ramya28 |
last post by:
Hi...
I need to read and write to an excel sheet with watermark in it by using java..
i can perform the rest of operations i need to on the excel except for watermark image in the output excel..
...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new...
| |