CODEWITHSUNDEEP
arraysexcelvbaloops
user960358
user960358

Reputation: 295

VBA Excel - Loop based on cell range value

So I'm running a loop to grab data from one column on a different sheet and I want it to stop once the data in the column ends, but I'm having some trouble finding a condition to end the loop. 

Do While values(i) <> 0

Set xcell = Range(Cells(x + 3, 2), Cells(x + 3, 2))
Set ycell = Range(Cells(y + 3, 2), Cells(y + 3, 2))
x = x + 1
y = y + 1

ycell.Formula = "='\\Drcs8570168\shasad\[CR Status.xlsx]Sheet1'!" & xcell.Address

values(i + 1) = xcell.Value
i = i + 1

Loop

I tried to assign each cell to a variable with an array and run the loop until blank cells start to be recorded. It tells me that the subscript is out of range.

Upvotes: 0

Views: 4248

Answers (3)

Roman
Roman

Reputation: 411

Have you intialized the variable i and the array values?

Before the loop:

i=0
Redim Preserve valus(i)
values(i)=1 'or else we will never enter the loop

In the loop (the last lines before the loop statement):

i = i + 1
Redim Preserve values(i)
values(i) = xcell.Value

But you don't even need the array values (that is if you will not use it anywhere else in your code), just store the last cell value in i.

Before the loop:

i = 1 'or we won't enter the loop
while i<>0 'starting the loop

here comes your looping code, and then before the end of the loop:

i = xcell.Value
Loop

EDIT: But if you have an actual 0 as cell-value in your list, the loop will stop before the end of the list, so you might want rather to check whether your cell contains no characters:

Assuming:

Dim i as Variant

The loop condition in the last example could then also be:

while i<>""

And this will only stop if the cell is really empty (=has no contents)

Upvotes: 1

Reafidy
Reafidy

Reputation: 8431

Firstly, instead of: 

Set xcell = Range(Cells(x + 3, 2), Cells(x + 3, 2))

Use just:

Set xcell = Cells(x + 3, 2)

BUT, I would doubt that you even need to loop at all. Try something like this:

Dim vValues As Variant

'// Get the range of cells you want to work with
With Range("B3", Cells(Rows.Count, "B").End(xlUp))
    '// Add the formula, excel will automatically fill in series
    .Formula = "='\\Drcs8570168\shasad\[CR Status.xlsx]Sheet1'!A1"
    '// Build an array from the values
    vValues = .Value
End With

Upvotes: 2

ApathyCorps
ApathyCorps

Reputation: 11

You can try using a function to find the last row with data in it such as:

Function LastRow(wks As Worksheet) As Long
    On Error GoTo ErrHandle
    LastRow = wks.Cells.Find("*", After:=wks.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Exit Function
ErrHandle:
    LastRow = 0
End Function

Upvotes: 1

Related Questions