Get all cell numbers of a specific value found in Excel

Help Studio
, , ,
1

Hello everyone,

I have a small query if someone can help wih this solution.
Input :
a. String Variable (which holds value for example - Nisarg)
b. Excel file path
c. Sheetname

Output :
a. List of all cell numbers where input String Variable is found with exact match.
For example, {“A4”,“D3”,“T14”}

Any suggestion in appreciated, also this is in live project so can’t use a custom activity logic and code is much appreciated.

2

add this code to a VBA file, and call is using excel application scope to VBA file and inside the scope, use execute macro

add parameters to execute macro using Macro Paratmeters property like this: {“pathname”, “sheetName”,…}

  1. path= excel path
  2. sheet= sheetName
  3. column Range:
    e.g. R:U to select R to U, R:U,N:O to select r-u and n-o
    set column range = “” to search whole sheet
  4. value to find = the value

Output = all cells where value is found , separated by comma
e.g. “A4,A9”

'columnRange e.g. R:U to select R to U,   R:U,N:O to select r-u and n-o
Function FindValuesInColumnRange(path As String, sheetName As String, columnRange As String, valueToFind As String)
    Application.DisplayAlerts = False
    'On Error GoTo ErrorHandling
    
    Dim wb As Workbook, ws As Worksheet
    


    Set wb = Workbooks.Open(path, False)
    
    Set ws = wb.Sheets(sheetName)
    ws.Activate
    
    'to select multiple column ranges, use Range("N:O,R:S").Select
    Dim result As String, res As Range
    
    If columnRange = "" Then
        Cells.Select
    Else
        Range(columnRange).Select
    End If
    
    Set res = FindAll(Selection, valueToFind)
    
    If res Is Nothing Then
        Debug.Print "No Result"
        result = ""
    Else
    
        Dim r As Range
        For Each r In res
            Debug.Print r.Address
            result = result & "," & Replace(r.Address, "$", "")
        Next r
        
        result = Right(result, Len(result) - 1)
    End If
    Debug.Print result
    

    wb.Close SaveChanges:=False

    Application.DisplayAlerts = True
    FindValuesInColumnRange = "Return:" & result
    
Exit Function
ErrorHandling:
    Application.DisplayAlerts = True
    Debug.Print Err.Description
    FindValuesInColumnRange = CStr(Err.Description)

End Function
1 Like
3

Hi @nisargkadam23

May be u can try this :

  1. Read the excel file and store in dt1 variable

  2. use this linq query to get the cell address

cell_address = (From row in dt1.AsEnumerable()
Where row.ItemArray.Contains(value)
Select Chr(65+row.ItemArray.IndexOf(value)).ToString+(dt1.Rows.IndexOf(row)+2).ToString).ToArray

where cell_address is string array()

Try this as well

Thanks & Regards,
Nived N

4

Hi @nisargkadam23 ,

After we have the Excel Sheet read as a Datatable, we could use the below Linq to get the List of Cell Address which matches the String Input :

This is a Modification to @NIVED_NAMBIAR 's above post, as it might give wrong output when the Columns are more than A-Z.

(From row In dt.AsEnumerable()
Where row.ItemArray.Contains(value)
Select UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(Array.IndexOf(row.ItemArray,value)+1).ToString+(dt.Rows.IndexOf(row)+2).ToString).ToArray
2 Likes
5

Thanks for sharing this Arpan

@supermanPunch

1 Like
6

Hey Jack can you give me this as an example in workflow the problem is when I am running it via Invoke VBA(That’s what I am using) it is giving an error saying - Sub Function not defined.

7

With the assumptions

  • we can rely on a datatable
  • intOffset - reflecting the usage of headerline / 1 based index from excel

intOffset = 1
strMatchValue = “aBc”
arrCells =

(From rix in Enumerable.Range(0, dtData.Rows.Count)
From cix in Enumerable.Range(0, dtData.Columns.Count)
Where dtData.Rows(rix)(cix).toString.Trim.ToUpper.Equals(strMatchValue.ToUpper)
Let cl = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(cix + 1)
Let ci = (rix + intOffset).toString
Select x = cl & ci).toArray
1 Like
8

Thank you so much @ppr it actually worked for me pretty well, I just had to 2 instead of intOffset and it worked for me.

(From rix in Enumerable.Range(0, dtData.Rows.Count)
From cix in Enumerable.Range(0, dtData.Columns.Count)
Where dtData.Rows(rix)(cix).toString.Trim.ToUpper.Equals(strMatchValue.ToUpper)
Let cl = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(cix + 1)
Let ci = (rix + 2).toString
Select x = cl & ci).toArray

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.