Remember old string during loop

Question

I have table with data that I need to transfer and everything is working fine but I need a control check before the actual code starts. The user needs to specify himself if he wants to process the data from a specific row by selecting 'process' in the specific column. But you should only be allowed to process it if the movement is the same on each selected row.

For example

Row	Movement	Amount	Action
1	Income	1000	Process
2	Other	500
3	Expenses	200	Process
4	Income	300

In this case an error needs to pop up stating that the action cannot be done as we have selected 'income' in row 1 and 'expenses' in row 3.

My thought was to compare the previous movement type with the current but it's not working because the movement of row 1 'is already forgotten'.

My code was as follows:

Dim Movement As String
Dim Movement_previous as String

For i = 2 To LastRow
    Movement = Range("Movement").Cells(i).Text
    Movement_previous = Range("Movement").Cells(i-1).Text
    If ActiveSheet.Range("Action").Cells(i).Value = "Process" Then
        If Movement <> Movement_previous Then
            MsgBox ("You can only select one movement type per booking.")
            Exit Sub
        End If
        
        'Actual code
        
    End If
Next i

Is there a workaround to 'remember' the previous variable string? Or is there a better solution overall?

What do you want to compare: "previous row" or "previous row where action = process"? — Ike
– Ike, Commented Oct 28, 2024 at 10:42
I would use rotabor's solution on WorkSheet_Change. That way your user knows as soon as they select an invalid row. — Frank Ball
– Frank Ball, Commented Oct 28, 2024 at 14:17

PizzaOverflow · Accepted Answer · 2024-10-28 11:29:04Z

5

I think you need two changes:

Do the validation first and the processing next. You need 2 loops.
In the validation: For the first row to process, store the movement. For the other rows, do the check.

    Dim Movement As String
    Dim Movement_first As String
    
    ' First: Validate
    '    Rule: all rows with action "Process", should have the same movement
    For i = 2 To LastRow
        If Range("Action").Cells(i).Text = "Process" Then
            Movement = Range("Movement").Cells(i).Text
            
            ' For the first occurence of movement, store it in a variable
            If Movement_first = "" Then
                Movement_first = Movement
            Else
                ' For further occurences, compare with the first movement
                If Movement <> Movement_first Then
                    MsgBox ("You can only select one movement type per booking.")
                    Exit Sub
                End If
            End If
        
        End If
    Next i
    
    ' Next: Process
    For i = 2 To LastRow
        If Range("Action").Cells(i).Text = "Process" Then
            MsgBox ("I process row " + Range("Row").Cells(i).Text)
        End If
    Next i

answered Oct 28, 2024 at 11:29

PizzaOverflow

2662 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Doublus Over a year ago

Thank you!! The same point of view as Chill60 here above commented. This works perfectly, thanks!

CHill60 Over a year ago

@Doublus The advantage to PizzaOverflow's solution is that you only start to move stuff if you know the validation is ok. With your current method, which I replicated you could find half the stuff has moved before you know there is a problem. Balance that against the two loops taking longer, which might not even be a factor to consider

Doublus Over a year ago

That's true, thanks for saying it yourself! Shows good sportsmanship!

rotabor · Accepted Answer · 2024-10-28 11:40:49Z

5

I propose to delegate Excel its job (Excel-365):

  Dim res As Variant
  res = Application.Evaluate("ROWS(UNIQUE(FILTER(Movement,Action=""Process"")))")
  If Not IsNumeric(res) Then
    MsgBox "No rows selected to process."
    Exit Sub
  End If
  If res <> 1 Then
    MsgBox "You can only select one movement type per booking."
    Exit Sub
  End If

First, rows are filtered by "Process". Second, the number of unique movements calculates. If no rows to process, res gets a non-numeric error value. Otherwise, the number of unique movements is tested.

edited Oct 28, 2024 at 11:40

answered Oct 28, 2024 at 11:25

rotabor

5,6112 gold badges5 silver badges23 bronze badges

1 Comment

Doublus Over a year ago

Oww that is a nice way to look at, I'll try this as well. Thanks!!

CHill60 · Accepted Answer · 2024-10-28 10:47:05Z

Only set a value in Movement_previous after you hit the first "Process" row. That first row defines what all subsequent movement types must be.

Dim Movement As String
Dim Movement_previous As String

For i = 2 To LastRow
    Movement = Range("Movement").Cells(i).Text
    If Movement_previous = "" Then
        Movement_previous = Movement
    End If
    If ActiveSheet.Range("Action").Cells(i).Value = "Process" Then
        If Movement <> Movement_previous Then
            MsgBox ("You can only select one movement type per booking.")
            Exit Sub
        End If
        
        'Actual code
        
    End If
Next i

The first time it hits the If Movement <> Movement_previous it will obviously pass. After that you are comparing the dynamic Movement variable, which changes on each row, against the static Movement_previous variable which is only set once.

Collectives™ on Stack Overflow

Remember old string during loop

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related