Jump to content
Duff360

Copy the content next to a found result

Recommended Posts

Duff360

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
water
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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Duff360

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
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Duff360

@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
water

Will test tomorrow and post the result :)

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

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

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
Duff360

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
water

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

×