Compile Error: Loop without Do

  • Thread starter Thread starter cedtech23
  • Start date Start date
C

cedtech23

I keep getting the following error "Compile Error: Loop without Do"

I am checking to see if A13 is empty if it is then enter the value in
txtorganization in cell A13 then end loop else increase cellposition by
1 and repeat the process...

any ideas how to solve this error??

Private Sub cmdInsert_Click()
Dim CellPosition, counter

counter = 1
CellPosition = a13
Do While counter <= 10
If [CellPosition].Value = " " Then
[CellPosition].Value = txtOrganization.Value
Exit Do
Else
CellPosition = a13 + 1
counter = counter + 1
Loop
End Sub
 
This should run. I don't know if it does exactly what you want, but it will
at least compile and run. Always check you "if" and "end if" and indent your
code for readability.

Private Sub cmdInsert_Click()
Dim CellPosition As Range
Dim counter As Integer

counter = 1
Set CellPosition = Range("a13")
Do While counter <= 10
If CellPosition.Value = " " Then
CellPosition.Value = txtOrganization.Value
Exit Do
Else
Set CellPosition = CellPosition.Offset(1, 0)
counter = counter + 1
End If
Loop
End Sub
 
The problem is that your If...Else doesn't have the mandatory End If.

When the compiler encounters "Loop" before the expected EndIf, it causes the
error you describe, although the message is a bit misleading, it does point
in the correct general direction (something missing regarding the loop
itself or within the loop).

HTH,
 
Exactly... I made the variable into a range object. It is (kind of) similar
to the active cell. You need to use the set statement to move it around...
 
I placed my code in module1 and I get the following error “Run-Time
error: ‘424’ object required” when I click on debug it brings me to

“CellPosition.Value = txtOrganization.Value & " " & txtPosition.Value”

I don’t know what wrong because when I run it under Private Sub
cmdInsert_Click()
It works great. What am I missing??




Code:
--------------------
Private Sub cmdInsert_Click()

Info

End Sub

--------------------


In Module 1 I have


Code:
--------------------
Function Info()

Dim CellPosition As Range
Dim counter As Integer

counter = 1

Set CellPosition = Range("a13")
Do While counter <= 10
MsgBox CellPosition.Value
If CellPosition.Value = "" Then
CellPosition.Value = txtOrganization.Value & " " & txtPosition.Value
Exit Do
Else
Set CellPosition = CellPosition.Offset(1, 0)
counter = counter + 1
End If
Loop
End Function
 
Not a problem but then I am lost because I have several txtboxes that
have to test if the cell is blank and input data if so. Example

Test if A13 is blank if so input the vlaue in txtposition in that cel
(I did that with the code below)

Test if C13 is blank if so input the values in txtStartMonth an
txtStartYear in cell C13

Test if D13 is blank if so input the values in txtEndMonth an
txtEndYear in cell D13


I know how to do this for one cell but I am lost on how to bring i
call toegether for all my cell?? Some guidance would be helpful…


Code
-------------------


Private Sub cmdInsert_Click()

Dim CellPosition As Range
Dim counter As Integer

counter = 1

Set CellPosition = Range("a13")
Do While counter <= 10
MsgBox CellPosition.Value
If CellPosition.Value = "" Then
CellPosition.Value = txtOrganization.Value & " " & txtPosition.Value
Exit Do
Else
Set CellPosition = CellPosition.Offset(1, 0)
counter = counter + 1
End If
Loop

End Sub
 
cedtech23,

It seems to me you moved the code from the cmdInsert_Click method of a
control to a new module.

Assuming cmdInsert_Click is associated with UserForm1 simple preceed
with the control for example.

CellPosition.Value = UserForm1.txtOrganization.Value & " " &
UserForm1.txtPosition.Value
 
Back
Top