Avatar of sandramac
sandramac
 asked on

Looping Code

Hello, I have the following code below; I have this same code pasted 9 times, except the next range would be B506, then B507, then B508 and so on...  How would I set it up to run starting with the range being B507, which is my first cell with data and then once this code finishes checks cell B508, if it is blank then end code, if not copy paste that value into b18 and call WebDataSingle.

Range("B506").Select
    Selection.Copy
    Range("B18").Select
    ActiveSheet.Paste
Call WebDataSingle
Microsoft Excel

Avatar of undefined
Last Comment
wobbled

8/22/2022 - Mon
Rory Archibald

Dim rngCell as range
for each rngCell in range("B506:B508").Cells
   Range("B18").Value = rngCell.Value
Call WebDataSingle
Next rngCell

Open in new window


should do it.
sandramac

ASKER
Thanks, one thing, is there a way to just stop running the code if the next value in rngCell is blank, instead of it running through the end of the range.
ASKER CERTIFIED SOLUTION
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
wobbled

You can exit a for loop simply by putting in an exit for command - below is simple example - just ammend it to your needs


for each rngCell in range("B506:B508").Cells
   Range("B18").Value = rngCell.Value

   If Range("B18").Value = "" then exit for

Call WebDataSingle
Next rngCell
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Anastasia D. Gavanas

So that would be something like:

Dim rngCell as range
for each rngCell in range("B506:B508").Cells
   if  If Range("B18").Value = "" then
                 exit for
   else
                 Range("B18").Value = rngCell.Value
                 Call WebDataSingle
   end if
Next rngCell
wobbled

here you go.  you just need to check each rngCell as you loop through to see if it is blank or not

Dim rngCell As Range

For Each rngCell In Range("B506:B508").Cells
   
   If rngCell.Value = "" Then
        Exit For
   Else
        Range("B18").Value = rngCell.Value
        Call WebDataSingle
   End If
Next rngCell