Duff360

Copy the content next to a found result

10 posts in this topic

Hello!

I tried to find this information on the forum, but I did not find any results that allowed me to solve my problem :>

In an Excel file, I want to copy the content next to a found result. I use the following code:

Local $aResult = _Excel_RangeFind($oWorkbook, "United States" )

For example, if this expression is found in column A1, I would like to copy the content of the column B1.

I know this is not hard, but I'm still a beginner with AutoIt and programming. 

Thank you very much!

Félix

Share this post


Link to post
Share on other sites



Local $aResult = _Excel_RangeFind($oWorkbook, "United States" )
For $i = 0 to UBound($aResult, 1) - 1
    If $aResult[$i][2] = "A1" Then ... Copy the cell
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Hello water,

Thank you again for your answer!!

I don't understand your code :(

The only thing I want is search for a word in an Excel file and if the word is found, copy the content next to it.

Example:

Column A   Column B

Ford           Mustang

GMC           Sierra

Honda        Civic

If I search for GMC, I want to copy Sierra to another Excel file :)

Thank you very much!

Félix

Edited by Duff360

Share this post


Link to post
Share on other sites

Is there only one occurrence of "GMC" in the file or multiple?
 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Untested ;)

Local $aResult = _Excel_RangeFind($oWorkbook, "GMC", "A:A") ; Search column A
For $i = 0 to UBound($aResult, 1) - 1
    _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oWorkbook.Activesheet.Range($aResult[$i][2]).Offset(0,1).Value, <Range to copy to>)
Next

You need to change the <Range to copy to>

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

@water Thank you again for your answer. Really appreciated.

It can have multiple occurrences but the value in column B is always the same.
It does not seem to work.

An example of the code with your script to test it:

#include <Excel.au3>
#include <Array.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "GMC", "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Sierra", "B1")

Local $aResult = _Excel_RangeFind($oWorkbook, "GMC", "A:A")
For $i = 0 to UBound($aResult, 1) - 1
    _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oWorkbook.Activesheet.Range($aResult[$i][2]).Offset(0,1).Value, "C1")
Next

I would like to copy Sierra to "C1"

I will try to figure out why it does not work. :)

Share this post


Link to post
Share on other sites

Will test tomorrow and post the result :)

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

This works for me:

#include <Excel.au3>
#include <Array.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "GMC", "A1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Sierra", "B1")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "GMC", "A5")
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Sierra Madre", "B5")

Local $aResult = _Excel_RangeFind($oWorkbook, "GMC", "A:A")
For $i = 0 To UBound($aResult, 1) - 1
    _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oWorkbook.Activesheet.Range($aResult[$i][2]).Offset(0, 1), $oWorkbook.Activesheet.Range($aResult[$i][2]).Offset(0, 2))
Next

 

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

It works perfectly!

I adapted the script for my application :)

Thank you very much @water! Always here to help!

Share this post


Link to post
Share on other sites

#10 ·  Posted

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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