I have a snippet of code as part of a much larger macro. In the snippet below I am getting a Compile Error: Object Required on the line Set PrevTROrderNo = TROrderNo
I want to check if the new instance of TROrderNo is the same as the last one. If the above is not the way to do this, what can I use?
The intent of this snippet is to look through a list of order numbers to see if that order number appears x times and then modify another workbook's data by dividing the Quantity by x.
So, for each cell in the relevant range, TROrderNo is the cell's value. Count how many times that value appears in the table (PayCount). If it appears more than once then find those occurrences in another workbook and divide the Quantity value by the PayCount. At the end of this I want the code to remember the TROrderNo it has been looking at, so when it gets the next TROrderNo in the list, the code can skip over a TROrderNo it has already handled.
I want to check if the new instance of TROrderNo is the same as the last one. If the above is not the way to do this, what can I use?
The intent of this snippet is to look through a list of order numbers to see if that order number appears x times and then modify another workbook's data by dividing the Quantity by x.
So, for each cell in the relevant range, TROrderNo is the cell's value. Count how many times that value appears in the table (PayCount). If it appears more than once then find those occurrences in another workbook and divide the Quantity value by the PayCount. At the end of this I want the code to remember the TROrderNo it has been looking at, so when it gets the next TROrderNo in the list, the code can skip over a TROrderNo it has already handled.
VBA Code:
Dim TROrder As Range, TROrderNo As String, PrevTROrderNo As String, PayCount As Integer, FoundOrder As Range, firstAddress As Range, InitialQty As Integer
For Each TROrder In TypeReport.ActiveSheet.Range("D" & TypeRepFirstRow, "D" & TypeRepLastRow)
TROrderNo = Range(TROrder).Value
If Not PrevTROrderNo = TROrderNo Then
PayCount = Application.CountIf(TypeReport.ActiveSheet.UsedRange, cell.Value)
If PayCount > 1 Then
With ExpReport.ActiveSheet.Range("A" & ExpFirstOrderRow, "A" & ExpLastOrderRow)
Set FoundOrder = .Find(TROrderNo, LookIn:=xlValues)
If Not FoundOrder Is Nothing Then
firstAddress = FoundOrder.Address
Do
InitialQty = FoundOrder.Offset(0, 3).Value
FoundOrder.Offset(0, 3).Value = InitialQty / PayCount 'Re-write Qty according to number of payments
Set FoundOrder = .FindNext(FoundOrder)
Loop While Not FoundOrder Is Nothing And FoundOrder.Address <> firstAddress
End If
End With
End If
End If
Set PrevTROrderNo = TROrderNo
Next TROrder
