Find a value

Help Activities
, , ,
1

Hello there,

I would like to find whether a value exists in any sheets of an excel file (including the hidden tabs). If the value exists, I would like to write the cell address in a new sheet.

I tried to tailor a macro script (as below) but it is not working. Can I get your guidance on how to correct it?

Sub FindOutdatedFYCells()
Dim ws As Worksheet
Dim destSheet As Worksheet
Dim cell As Range
Dim lastRow As Long

Set destSheet = 
ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
destSheet.Name = "Outdated FY"
destSheet.Range("A1").Value = "Sheet Name"
destSheet.Range("B1").Value = "Cell Address"

For Each ws In ThisWorkbook.Worksheets
    For Each cell In ws.UsedRange.Cells
        If InStr(cell.Value,"2021")>0 Then
            lastRow = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Row + 1
            destSheet.Cells(lastRow, 1).Value = ws.Name
            destSheet.Cells(lastRow, 2).Value = cell.Address
        End If
    Next cell
Next ws

End Sub

2

Hi @WS_Chai

  1. Open your Excel workbook in UiPath using the Excel Application Scope activity.
  2. Add a Invoke VBA activity inside the Excel Application Scope.
  3. In the Invoke VBA activity properties, set the following:
  • CodeFilePath: Leave it blank if you are writing the VBA code directly in the VBA editor of the Excel file. If the code is saved in a separate .vba file, provide the full path to that file.
  • CodeContent: Paste the VBA macro script you provided into this field.

The VBA macro will be executed in the Excel Application Scope, and it will find the desired value in all sheets (including hidden ones) and write the cell addresses to the “Outdated FY” sheet.

Please ensure that your Excel workbook is properly opened using the Excel Application Scope before running the VBA macro. Also, make sure the VBA macro code is correct and tailored to your specific requirement.

If you encounter any errors, you can check the Output panel in UiPath for any error messages or issues with the VBA code. Additionally, verify that the VBA macro works as expected when running directly in Excel to ensure there are no errors or logical issues with the code itself.

3

Thanks @pravallikapaluri,

Can you advice if my code is correct before I try the abovementioned steps?
Seems like there’s error in my existing code.

4

@WS_Chai

Try this code

Sub FindOutdatedFYCells()
    Dim ws As Worksheet
    Dim destSheet As Worksheet
    Dim cell As Range
    Dim lastRow As Long

    Set destSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    destSheet.Name = "Outdated FY"
    destSheet.Range("A1").Value = "Sheet Name"
    destSheet.Range("B1").Value = "Cell Address"

    For Each ws In ThisWorkbook.Worksheets ' Loop through all sheets, including hidden ones
        For Each cell In ws.Cells ' Loop through all cells in the sheet
            If InStr(cell.Value, "2021") > 0 Then ' Check if the cell contains "2021"
                lastRow = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Row + 1
                destSheet.Cells(lastRow, 1).Value = ws.Name
                destSheet.Cells(lastRow, 2).Value = cell.Address
            End If
        Next cell
    Next ws
End Sub

the macro will loop through all sheets, regardless of whether they are hidden or visible, and check each cell’s value for the string “2021”. If the value is found, it will record the sheet name and cell address in the “Outdated FY” sheet.

5

Why not just use the For Each Row in Excel activity, and for each row do an If and then Write Cell? Seems to me you’re overcomplicating it with the macro.

6

Hi @pravallikapaluri,

Sorry that the code unfortunately is not working.
Any idea on the reason?

7

@postwick,

Hi Paul, thanks for the idea, but I would have hidden tabs too which I need to find the value.
Would for each activity gives me a correct result?

8

I don’t know, you’d have to try it and find out. Simple to do, just add an Excel Process Scope, inside it a Use Excel File and designate the file. Then inside it a For Each Row in Excel, and inside it a Log Message that outputs the sheet name. See if the hidden sheets are there.