0

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.

Improve this question
1
  • How are you declaring values() ? Commented Oct 3, 2011 at 18:39

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hmm, I would like to try this out but I'm not familiar with those commands. Even though you wouldn't want to use a loop would you know why mine isn't working?
The commands may look a little complex to start with, but they are really not. All it does it gets the used cells of column b, then fills them with a formula and populates and array from them. Please edit your post to include the entire code, I need to see declarations and what the initial values of x and y are etc.
1

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)

Improve this answer

3 Comments

So I put ReDim Preserve values(i) values(i) = 1 But it still says out of range And I was hoping to keep the cell contents in the array, that's why i wasn't just using i for the loop.
Actually, one thing I didn't notice before is that I do get an output of the first cell in the loop, but then I get the "type mismatch" error.
Sorry, made a mistake. The code did work, just forgot the redim line.
1

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
Improve this answer

3 Comments

What happens if you don't include the optional argument?
To be honest, I'm not even sure why that argument is optional. I wrote this a while ago when I was first learning VBA and I can't really recall why I wrote it like that. I've edited my post to make the argument required.
This function will return the last used row that occurs anywhere in the sheet, but the user wants the last used row in a particular column (or columns). So while I would use Reafidy's approach, I would suggest you tailor the Find to search discrete columns to be applicable to this issue. Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.