Compile Error: Loop without Do 2

[vttech]

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

 

[AnotherHiggins]

You need to close your IF statement.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[vttech]

Thanks but I change the code but know I am running into the following error
“Run-Time error ‘13’ Type mismatch”

Then when I click debug it brings me to line “CellPosition = CellPosition + 1”

What I am trying to is increase A13 to A14 and so on

How to I get a cell positon like A13 and increase it by one to A14???

Private Sub cmdInsert_Click()
Dim CellPosition, counter

counter = 1
CellPosition = "A13"


Do While counter <= 10
MsgBox CellPosition
If Sheets("hsform").Range(CellPosition).Value = " " Then
Sheets("hsform").Range(CellPosition).Value = txtOrganization.Value
Exit Do
Else
CellPosition = CellPosition + 1
counter = counter + 1
End If
Loop

 

[PHV]

Typed, untested:
Private Sub cmdInsert_Click()
Dim CellPosition As Range, counter As Integer
counter = 1
Set CellPosition = Sheets("hsform").Range("A13")
Do While counter <= 10
If CellPosition.Value = " " Then
CellPosition.Value = txtOrganization.Value
Exit Do
Else
Set CellPosition = CellPosition.Offset(0, 1)
counter = counter + 1
End If
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vttech]

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

 Private Sub cmdInsert_Click()

Info

End Sub

In Module 1 I have

 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

 

[PHV]

I'm afraid you have to put your function in the same class module as the controls ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vttech]

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…

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

 

[rmikesmith]

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

If you want to access the controls (TextBoxes in this case) in a sub/function that resides outside the Userform class module, you must qualify the references using the Userform name. Using your Module 1 function as an example (assume the form name is frmInput):

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 = [b]frmInput.[/b]txtOrganization.Value & " " & [b]frmInput.[/b]txtPosition.Value
      Exit Do
    Else
      Set CellPosition = CellPosition.Offset(1, 0)
      counter = counter + 1
    End If
  Loop
End Function

Regards,
Mike