Looping Code

sandramac
sandramac used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2011
Top Expert 2011
Commented:
Sure:
Dim rngCell as range
for each rngCell in range("B506:B508").Cells
   If len(rngCell.Value) > 0 then
      Range("B18").Value = rngCell.Value
      Call WebDataSingle
   Else
      Exit For
   End If
Next rngCell

Open in new window

Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Commented:
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
Top Expert 2011

Commented:
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

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial