asked on 5/23/2012

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

Last Comment

wobbled

5/23/2012

Rory Archibald

5/23/2012

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

5/23/2012

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

5/23/2012

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

wobbled

5/23/2012

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

Anastasia D. Gavanas

5/23/2012

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

5/23/2012

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