19. februar 2023 - 09:54Der er
2 kommentarer og 1 løsning
Loop in Excel
Hi Forum,
I would like your help with a small issue regarding loop.
I have a well-working script with a loop where I loop through a range of numbers. Now, I need to loop through a range containing text. I can't reuse the current script 1:1 due to the change of value type (numbers => text) but I can't figure out how to change the script. Can you please help me?
In my current script I loop through a range with numbers row by row until the numbers change to the value 'STOP'. As a part of the script, I use the numbers as file names:
Sub Test()
Dim i As Integer Dim j As Long
i = 1
Do While Cells(i, 1).Value <> "STOP"
If Cells(i, 1).Value = "STOP" Then
Exit Do
End If
If Cells(i + 1, 1).Value = "STOP" Then
MsgBox "Program completed"
Exit Sub
End If
j = Cells(i, 1).Value i = i + 1
Dim FileName As String
FileName = j
[here comes the part of the script that handle data]
To modify your existing script to loop through a range of text instead of numbers, you can replace the variable type from Integer to String. Also, you will need to update the conditional statements that check for "STOP" as the stopping condition instead of a number. Here's an updated version of your script that should work with a range of text:
Sub Test()
Dim i As Integer Dim j As String
i = 1
Do While Cells(i, 1).Value <> "STOP"
If Cells(i, 1).Value = "STOP" Then
Exit Do
End If
If Cells(i + 1, 1).Value = "STOP" Then
MsgBox "Program completed"
Exit Sub
End If
j = Cells(i, 1).Value i = i + 1
Dim FileName As String
FileName = j
[here comes the part of the script that handle data]
Loop
End Sub
Make sure to update the range in your worksheet to contain the text values you want to loop through, and to update any other parts of your script that rely on the type or format of the values in the range.
Thank you so much for your suggestions on how I can solve the loop issue.
@tjacob: Unfortunately, I couldn't use your solution. The return value from the loop was only 1 (one) and the same value as the i value.
@Keld: Your solution works just the way I wanted. Thanks!
Kind regards,
Arne
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
