Invoke VBA not executing all lines of code

Help Activities
, , ,
1

Hi.

I am rolling forward a lot (300+) Excel Spreadsheets for a new financial year and using VBA Scripts to update some values and formats that have changed during the year and update values such as the date of the End of Financial Year.

I have some very simply code that copies a value from “Prior Year” into “2 Years Prior”, and “This Year” into the “Prior Year” field. When I execute this code directly in the Excel File to test, it works perfectly, however when it is executed via Studio the copy of data from Prior Year to 2 Years Prior works fine, however the This Year to Prior Year doesn’t work. The only difference between the 2 cells in Excel is that the Prior Year is a number, while This Year is a formula. This doesn’t matter in Excel as I’m getting the value and storing as a variable and then saving that value into the new cell.

This is the code and it’s very simply. Can’t figure out why it doesn’t work.

Sub UpdateNumbers()

Dim PriorYearAmount As Double
Dim ThisYearAmount As Double

Set wSheetDashboard = ThisWorkbook.Worksheets(“Dashboard”)

wSheetDashboard.Unprotect

'Update This Year and Prior Year Amounts
ThisYearAmount = wSheetDashboard.Range(“O24”).Value 'This is the This Year Amount
PriorYearAmount = wSheetDashboard.Range(“O26”).Value 'This is the Priot Year Amount

wSheetDashboard.Range(“O26”).Value = ThisYearAmount 'Updating Prior Year Amount with This Year amount
wSheetDashboard.Range(“O27”).Value = PriorYearAmount 'Updating 2 Years Prior Amount with Prior Year amount

End Sub

2

@craig.norton

  1. Instead of thisworkwbook try activeworkbook and check
  2. Try to use a on error goto statement and see if there is any error during execution from UiPath

Cheers

3

@craig.norton

Please try this:

Sub UpdateNumbers()

 PriorYearAmount As Double
 ThisYearAmount As Double
 wSheetDashboard As Worksheet

Set wSheetDashboard = ThisWorkbook.Worksheets("Dashboard")
wSheetDashboard.Unprotect


' Update This Year and Prior Year Amounts
ThisYearAmount = wSheetDashboard.Range("O24").Value2 ' This is the This Year Amount
PriorYearAmount = wSheetDashboard.Range("O26").Value2 ' This is the Prior Year Amount

wSheetDashboard.Range("O26").Value = ThisYearAmount 'Updating Prior Year Amount with This Year amount
wSheetDashboard.Range("O27").Value = PriorYearAmount 'Updating 2 Years Prior Amount with Prior Year amount

End Sub

4

Turns out a user error. The values are formulas and the bot was clearing the data in a sheet that was driving the formulas before copying the values. When testing the scripts the data was always present, however when the entire process was run the values were showing 0.

5

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