You’re offline. This is a read only version of the page.
Issue: Incorrect row duplication and error with Write to Excel Worksheet in Power Automate Desktop
I’m working on a Power Automate Desktop flow that processes Excel data, which checks for payment due to citizens. The output spreadsheet cannot have a value of more than £100 per row so it splits payment values into increments of £100. Anyone due less than £100 just has their row of data copied across by writing these values into a new Excel sheet, anyone with an amount over £100, for example £250, would have three rows of data. Two for £100 each and one for £50, before the loop moves on to the next person. I'm encountering multiple problems:
Row Duplication/Deletion:
Error in Write to Excel Worksheet Action:
Share
ReportExcelRowCounter variable to specify the row for writing to Excel, I get the error "Argument 'Row' must be an integer value."ExcelRowCounter with a specific number (e.g., 2), the error disappears but the row sequencing doesn’t work correctly.Flow Logic:
If statements and a loop (LoopIndex) to handle payments over £100 and increment the ExcelRowCounter. However, there seems to be a problem with the loop logic or how ExcelRowCounter is managed.LoopIndex and NumberOfFullPayment conditions are correctly placed, leading to the wrong rows being written.Expected Output:
All responses (
Answers (Option Explicit
' Create Excel application object
Dim objExcel, objWorkbook, objSheet, objNewWorkbook, objNewSheet
Dim lastRow, i, row, paymentAmount, remainingAmount
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False ' Make Excel visible if you want to debug
' Open the input Excel file
Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\input\file.xlsx")
Set objSheet = objWorkbook.Sheets(1)
' Create a new Excel workbook for the output
Set objNewWorkbook = objExcel.Workbooks.Add
Set objNewSheet = objNewWorkbook.Sheets(1)
' Copy header from original sheet to new sheet
For i = 1 To objSheet.UsedRange.Columns.Count
objNewSheet.Cells(1, i).Value = objSheet.Cells(1, i).Value
Next
' Get the last row of the input data
lastRow = objSheet.UsedRange.Rows.Count
' Start processing from the second row (assuming first row is header)
row = 2
For i = 2 To lastRow
' Get the payment amount from the specific column (assuming it's column 2 here)
paymentAmount = objSheet.Cells(i, 2).Value
' Check if payment is less than or equal to £100
If paymentAmount <= 100 Then
' Copy the entire row to the new sheet
objNewSheet.Rows(row).Value = objSheet.Rows(i).Value
row = row + 1
Else
' Process payment by splitting into increments of £100
remainingAmount = paymentAmount
Do While remainingAmount > 0
If remainingAmount >= 100 Then
' Copy the row and set payment to £100
objNewSheet.Rows(row).Value = objSheet.Rows(i).Value
objNewSheet.Cells(row, 2).Value = 100 ' Change payment to £100
remainingAmount = remainingAmount - 100
row = row + 1
Else
' Copy the row and set payment to the remaining amount
objNewSheet.Rows(row).Value = objSheet.Rows(i).Value
objNewSheet.Cells(row, 2).Value = remainingAmount ' Set the remaining payment
remainingAmount = 0
row = row + 1
End If
Loop
End If
Next
' Save the new workbook
objNewWorkbook.SaveAs "C:\path\to\output\file.xlsx"
' Close workbooks and quit Excel
objWorkbook.Close False
objNewWorkbook.Close True
objExcel.Quit
' Clean up
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objNewSheet = Nothing
Set objNewWorkbook = Nothing
Set objExcel = Nothing
WScript.Echo "Processing complete."
#1
eetuRobo
18
Super User 2025 Season 1
#2
stampcoin
6
#2
John_Mavridis
6
Microsoft Employee
