Sign in to follow this  
Followers 0
Automationuser

Fetch the Row and Column number of a string in excel

14 posts in this topic

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



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

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


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

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

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

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

#7 ·  Posted (edited)

@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

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

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

#10 ·  Posted (edited)

Columns("ABC").Select

should become

$oExcel.ActiveSheet.Columns("ABC").Select
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

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

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

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

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  
Followers 0