DO WHILE LOOP

[amitshah]

I have a Do while loop and within it I have a few for loops and if conditional statements. Eg.

Do While condition
statement
if condition then
statement
for i=0 to count
statement
next i
statement
etc etc
Loop

I need the for loops and if conditional statements in my loop. However when I compile I get an error “Compile Error: Loop without Do”

Why is this so? Is this not allowed in VBA?

 

[Abdc]

You need an End If

 

[amitshah]

Thanks for the reply. Yes it did compile fine after I added the "End If"

I also want to activate sheets for a specific workbook. This is the code I am using

Workbooks("workbookname.xls").Worksheets("sheetname").Range("A2").Select

also when I need to activate a sheet I am using the following code:

Worksheets("Sheet1").Range("A1").Select

I am getting a an error when I run the code. The error is:

“Run-Time error ‘1004’: Activate method of Range Class Failed”

Can anyone tell me why?

Thanks.

 

[Abdc]

You have to activate the workbook and then activate the sheet before you can select a cell on that sheet.

Alternatively, you can probably revise your code so that you don't have to select or activate anything.

 

[amitshah]

Thanks. How do you go about doing stuff in the sheets and cells without activating it? Any example?

Thanks

 

[Abdc]

Post some example code that does some activating/selecting and I'll revise it for you.

 

[amitshah]

Here is an example of what I am trying to do.

Sub DoSomething()

Workbooks("filename.xls").Worksheets("sheet1").Activate
Range("A2").Select

For i = 0 To 2205
If i <> 0 Then
‘ here I want to clear the contents of
‘ my array so the data from the next
‘row can be taken and stored in the array
Worksheets("sheet1").Activate
‘ below I want to go to the next row
ActiveCell.Cells(row_Count, 0).Select
End If

‘Collect the column values of the row in
‘an array
row_Count = row_Count + 1
Next i

End Sub


I have an array that I am using to store values from a row. I am using a for loop to loop through the rows. After each loop I need to erase all the elements of the array and start storing values from the row.

How can I empty the contents of the array after each loop?

Thanks

 

[Abdc]

The following doesn't do any activating/selecting :-


Sub DoSomething()
Dim myArray As Variant, cell As Range
With Workbooks("filename.xls").Worksheets("sheet1")
'Loop through each cell in column A range
For Each cell In .[A3:A2205]

' "here I want to clear the contents of _
my array so the data from the next _
row can be taken and stored in the array"
'NOT NECESSARY TO DO THIS - the array values _
will be automatically overwritten each time.

'Collect the values in columns A:F of the row in _
an array
myArray = .Range(cell, cell(1, 6))
'Put the array values in columns H:M of the same row
.Range(cell(1, 8), cell(1, 13)).Value = myArray
Next
End With
End Sub


What is it that you want to do with the values stored in the array for each row?
Perhaps you don't need a loop.
And perhaps you don't need to use an array.
This message was edited by Abdc on 2002-09-07 16:35