Jump to content

Copy the content next to a found result


Duff360
 Share

Recommended Posts

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

Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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
Link to comment
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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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. :)

Link to comment
Share on other sites

Will test tomorrow and post the result :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

:)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...