DO WHILE LOOP

amitshah

Board Regular
Joined
Apr 13, 2002
Messages
80
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?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

amitshah

Board Regular
Joined
Apr 13, 2002
Messages
80
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

New Member
Joined
Mar 16, 2002
Messages
20
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

Board Regular
Joined
Apr 13, 2002
Messages
80

ADVERTISEMENT

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

Thanks
 

Abdc

New Member
Joined
Mar 16, 2002
Messages
20
Post some example code that does some activating/selecting and I'll revise it for you.
 

amitshah

Board Regular
Joined
Apr 13, 2002
Messages
80
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

New Member
Joined
Mar 16, 2002
Messages
20
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,986
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top