Sign in to follow this  
Followers 0
AoRaToS

Excel search,compare?

2 posts in this topic

I have two excel files, one contains stuff like:

A B C

BOX1 name 7643294

BOX2 name 4324523

BOX4 name 5553254

Then I have a second one that has only numbers, all of them are in the first one but the order is random, so what I want is:

Read a number out of the second file, find the corresponding one in the first file and take it's box number to put it in a cell next to the number in the second file.

It would be like

$sFilePath1 = @ScriptDir & "\file1.xls" ;This file should already exist
$sFilePath2 = @ScriptDir & "\file2.xls" ;This file should already exist

$oExcel2 = _ExcelBookOpen($sFilePath2)
$oExcel1 = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

Sleep(4000)

For $count = 1 to 250
    $value = _ExcelReadCell($oExcel2, $count,6)
        ; $searchcell = Search($oExcel1, $value)  should return something like F35 etc
        ; $box = _ExcelReadCell($oExcel1, "*",6)    *split $searchcell and use only the number to make A35 (I can do this)
    _ExcelWriteCell($oExcel2, $value, $count,7)
Next

Is there some way of searching an excel file using a UDF?sending Ctrl+F would take long and require allot of checking if the window has appeared, sleep to let the search finish, get the selected item...


s!mpL3 LAN Messenger

Current version 2.9.8.8 [30/09/2015]

Download Here

s!mpL3

Share this post


Link to post
Share on other sites



AoRaToS,

Surely the way to do this would be to use a loop and _ExcelReadCell? Something like this:

For $count2 = 1 to 250
    $value2 = _ExcelReadCell($oExcel2, $count,6)
    For $count1 = Whatever values you need to search
        $value1 = _ExcelReadCell($oExcel1, some function of $count1)
        If $value1 = $value2 Then ExitLoop
    Next
; $count1 will hold the index of the found number
; but you will need to errorcheck to see if you fell out of the loop without finding the number!
; now read another cell in that row to get the box number
    $box = _ExcelReadCell($oExcel1, some other function of $count1)
; split $searchcell and use only the number to make A35   (Quote: "I can do this")
    _ExcelWriteCell($oExcel2, $value, $count2,7)
Next

I do not have Excel so I cannot test this, but the logic should be good. Think of it as "an exercise for the student"!

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0