Loop Until Excel VBA not counting correctly

Question

̀ soos()` should just loop through one row at once until there is an empty cell.

vorgesetze_suchen() looks for the superior of an employee in another column, it then checks the employee list again to find if the superior has a superior and so on.

If no superior is found soos() should go to the next employee.

Instead zeile gets overwritten by the value of z from vorgesetzte_suchen()

Is there something wrong with my variable declaration?

Console Output:

2
103

Code:

Private Sub soos()    
    Dim zeile As Integer
    Dim spalte As Integer
    Dim ma_vorname As String
    Dim ma_nachname As String
    
    zeile = 2
    spalte = 23
    
    Do Until Tabelle1.Cells(zeile, spalte) = ""    
        ma_vorname = Tabelle1.Cells(zeile, spalte)
        ma_nachname = Tabelle1.Cells(zeile, spalte + 2)
    
        Debug.Print (zeile) 'Should be 2'
        Call vorgesetzte_suchen(zeile, spalte, ma_vorname, ma_nachname, 0) 
        zeile = zeile + 1
        Debug.Print (zeile) 'Should be 3 but is 103' 
    Loop
    
    MsgBox "Fertig", vbCritical
End Sub

Private Function vorgesetzte_suchen(z, s, ma_name, ma_nachname, counter)
    Dim vorgesetzter As String
    Dim vg_name As String
    Dim vg_nachname As String
    
    Do Until Tabelle1.Cells(z, s) = ma_name And Tabelle1.Cells(z, s + 2) = ma_nachname Or Tabelle1.Cells(z, s) = ""
        z = z + 1
    Loop
    
    If Tabelle1.Cells(z, s) = "" Then
        'Debug.Print ("Done")
        counter = 0
    Else
        vorgesetzter = Tabelle1.Cells(z, s + 73)
        Tabelle1.Cells(z, s + 97 + counter) = vorgesetzter
        vg_name = Split(vorgesetzter, ",")(1) 'Namen andersrum
        vg_nachname = Split(vorgesetzter, ",")(0)
        counter = counter + 1
        Call vorgesetzte_suchen(z, s, vg_name, vg_nachname, counter)
    End If
End Function

chris neilsen · Accepted Answer · 2022-03-16 10:35:36Z

2

You are passing zeile to vorgesetzte_suchen ByRef (the default when reference type is not specified). That means is can be modified by vorgesetzte_suchen.

Either use a different variable in vorgesetzte_suchen, or specify ByVal

answered Mar 16, 2022 at 10:35

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

Collectives™ on Stack Overflow

Loop Until Excel VBA not counting correctly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related