Jump to content
Sign in to follow this  
Automationuser

Fetch the Row and Column number of a string in excel

Recommended Posts

Automationuser

Hello All,

Is there any way to fetch the Row and Column number of a string in excel?

I am looking for a function something like _ExcelGetRowColumnNumber($String)

 

Share this post


Link to post
Share on other sites
Juvigy

Hello All,

Is there any way to fetch the Row and Column number of a string in excel?

I am looking for a function something like _ExcelGetRowColumnNumber($String)

 

Give me an example of what you mean by 'string' - 'C5' ?

Share this post


Link to post
Share on other sites
water

Do you know the address cell where the string is stored?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Automationuser

I have a tescase ID "TC_01" in the 6th row and 1st column and this address might change anytime. So what I need is to search for Text "TC_01" and fetch the row and column number and write the result of the testcase in the 2nd column.

TC_01 Pass

Share this post


Link to post
Share on other sites
Juvigy

I dont remember if there was a FIND function in the excel UDF , but you can convert this VBA:

   Columns("B:B").Select
    Selection.Find(What:="Belguim", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="Belgium", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

Then you can do  Activecell.address or something similar.

Share this post


Link to post
Share on other sites
water

There is no search function in the Excel UDF that comes with AutoIt - at the moment.

Please have a look at the search function in my rewrite of the Excel UDF (still an early alpha version!). Download link can be found in my signature.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Automationuser

@Juvigy,

Where can I find info about VBA?

I get  ERROR: Columns(): undefined function. if I use tht code in my script

Edited by Automationuser

Share this post


Link to post
Share on other sites
water

Can you post the AutoIt code you have so far?

You need to specify the worksheet you want to access.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Automationuser

Global $oExcel = _ExcelBookOpen(@ScriptDir&"ResultTestRunResults.xls") ; Open the Excel file

_ExcelSheetActivate($oExcel, "ABC")
   Columns("ABC").Select
    Selection.Find(What:="TC_01", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="TC_01", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

Share this post


Link to post
Share on other sites
water

Columns("ABC").Select

should become

$oExcel.ActiveSheet.Columns("ABC").Select
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

This works for me:

#include <Excel.au3>
$oExcel = _ExcelBookAttach("C:\111.xlsx")
$oExcel.Application.ActiveSheet.Columns("ABC").Select
ConsoleWrite($oExcel.Application.Selection.Find("TC_01").Address&@CRLF)
$oExcel = 0

Share this post


Link to post
Share on other sites
Automationuser

I am receiving the following :

Variable must be of type "Object".:
$oExcel.Application.ActiveSheet.Columns("ABC").Select
$oExcel^ ERROR

if I use isobj(), its returning 0.

Share this post


Link to post
Share on other sites
Juvigy

I am receiving the following :

Variable must be of type "Object".:

$oExcel.Application.ActiveSheet.Columns("ABC").Select

$oExcel^ ERROR

if I use isobj(), its returning 0.

You need to have C:111.xlsx already opened. You can change  _ExcelBookAttach to  _ExcelBookOpen or try it like this:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open($FilePath)
$oExcel.Application.ActiveSheet.Columns("ABC").Select
ConsoleWrite($oExcel.Application.Selection.Find("TC_01").Address&@CRLF)
$oExcel = 0

Share this post


Link to post
Share on other sites
Automationuser

My bad...had incorrect file name in my script.

Thanks...it worked :)

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  

×