Compile Error: Loop without Do

[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

[Charlie]

Need an "End If" statement before the "Loop" statement

"cedtech23" wrote:

>
> 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
>
>
> --
> cedtech23
> ------------------------------------------------------------------------
> cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> View this thread: http://www.excelforum.com/showthread...hreadid=507400
>
>

[George Nicholson]

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,
--
George Nicholson

Remove 'Junk' from return address.


"cedtech23" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> cedtech23
> ------------------------------------------------------------------------
> cedtech23's Profile:
> http://www.excelforum.com/member.php...o&userid=31022
> View this thread: http://www.excelforum.com/showthread...hreadid=507400
>

[Jim Thomlinson]

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
--
HTH...

Jim Thomlinson


"cedtech23" wrote:

>
> 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
>
>
> --
> cedtech23
> ------------------------------------------------------------------------
> cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> View this thread: http://www.excelforum.com/showthread...hreadid=507400
>
>

[cedtech23]

does "Set CellPosition = CellPosition.Offset(1, 0)"

increase the cell by one??

[Jim Thomlinson]

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...
--
HTH...

Jim Thomlinson


"cedtech23" wrote:

>
> does "Set CellPosition = CellPosition.Offset(1, 0)"
>
> increase the cell by one??
>
>
> --
> cedtech23
> ------------------------------------------------------------------------
> cedtech23's Profile: http://www.excelforum.com/member.php...o&userid=31022
> View this thread: http://www.excelforum.com/showthread...hreadid=507400
>
>

[cedtech23]

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??

Please Login or Register  to view this content.

In Module 1 I have

Please Login or Register  to view this content.

[cedtech23]

Not a problem but then I am lost because I have several txtboxes that I 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 cell (I did that with the code below)

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

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


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

Please Login or Register  to view this content.

[Edward Ulle]

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





*** Sent via Developersdex http://www.developersdex.com ***

[cedtech23]

Thanks Edward Ulle