Avatar billede Nuser2008 Mester
19. februar 2023 - 09:54 Der 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]


Loop


End sub
Avatar billede tjacob Juniormester
20. februar 2023 - 08:55 #1
Just ignore variable j

Delete the lines:    j = Cells(i, 1).Value
                  and:    FileName = j

and set FileName directly:    FileName = Cells(i, 1).Value
Avatar billede Keld Broe Seniormester
22. februar 2023 - 22:25 #2
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.
Avatar billede Nuser2008 Mester
25. februar 2023 - 12:41 #3
Hi tjacob and Keld Broe

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
Avatar billede Ny bruger Nybegynder

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.

Loading billede Opret Preview
Kategori
Kurser inden for grundlæggende programmering

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester