IF's within a Do until loop

[smiler44]

struggling with If's that are inside a Do Until loop, can you have many IF's inside a do until loop?

I'm having problems with the If ouc = "JF......... ifs

Not sure how to write

[/FONT]
[FONT=Arial]if ouc = "JFB" then [/FONT]
[FONT=Arial]Sheets("VP_Data").Select [/FONT]
[FONT=Arial]Sheets("VP_Data").Range("B13").Select[/FONT]

I have

[FONT=Arial]Do Until IsEmpty(ActiveCell.Value) = True[/FONT] 
[FONT=Arial] If ActiveCell.Text = " " Then[/FONT] 
[FONT=Arial]          MsgBox ("blank")[/FONT] 
[FONT=Arial]          Exit Sub[/FONT] 
[FONT=Arial]          Else[/FONT] 
[FONT=Arial]     If ActiveCell.Text <> " " Then[/FONT] 
[FONT=Arial]          Dim ouc As String[/FONT] 
[FONT=Arial]          Dim nunb As String[/FONT] 
[FONT=Arial]          Dim actsel As String 'activecell[/FONT] 
[FONT=Arial]          [/FONT]
[FONT=Arial]          ouc = ActiveCell.Text[/FONT] 
[FONT=Arial]          actsell = ActiveCell.Address[/FONT] 
[FONT=Arial]          [/FONT]
[FONT=Arial]          MsgBox (ouc)[/FONT] 
[FONT=Arial]          ActiveCell.Offset(0, 5).Activate[/FONT] 
[FONT=Arial]         [/FONT][FONT=Arial] nunb = ActiveCell.Text[/FONT] 
[FONT=Arial]          MsgBox (nunb)[/FONT] 
[FONT=Arial]          If ouc = "JFB" Then Sheets("VP_Data").Select ' Sheets("VP_Data").Range("B13").Select[/FONT] 
[FONT=Arial]          [/FONT][FONT=Arial]          [/FONT]
[FONT=Arial]          If ouc = "JFC" Then Sheets("VP_Data").Select[/FONT] 
[FONT=Arial]          Sheets("VP_Data").Range("C13").Select [/FONT][FONT=Arial]          [/FONT]
[FONT=Arial]          If ouc = "jfd" Then Sheets("VP_Data").Select[/FONT] 
[FONT=Arial]         [/FONT][FONT=Arial] Sheets("promotions").Select[/FONT] 
[FONT=Arial]          Sheets("promotions").Range(actsell).Select[/FONT] 
[FONT=Arial]          ActiveCell.Offset(1, 0).Activate[/FONT] 
[FONT=Arial]          MsgBox ("there")[/FONT] 
[FONT=Arial]     End If[/FONT] 
[FONT=Arial]  End If[/FONT] 
[FONT=Arial]          Loop[/FONT] 
[FONT=Arial]          End Sub[/FONT]

smiler44

 

[dpm]

Seems to me you'd want a Select Case down there.

Select Case ouc
    Case "JFB"
        Sheets("VP_Data").Select ' Sheets("VP_Data").Range("B13").Select
    Case "JFC"
        Sheets("VP_Data").Select
        Sheets("VP_Data").Range("C13").Select
    Case "jfd"
        Sheets("VP_Data").Select
End Select

I don't see a Do Loop, so I am making an assumption on your intended result.

You could also continue the way it is now, using If, ElseIf, End If. It's a matter of preference, mostly. A Select Case stops evaluating when the first True case is encountered, which might be preferable in some cases. One reason I like them is that they present your code in a nice, organized structure which is easy to decipher.

 

[smiler44]

thanks DPM,
I'll try a case, never used one before.
The reason for the do until loop is that for example
c4 is selected. if cell is blank then exit sub. if has text in then........
move to C5 if cell is blank then exit sub. if has text in then........
and keep going until c? is blank

do until text.value = " "

smiler44

 

[dpm]

In that case, you will need a structure which contains each cell in the given column, to iterate through. I would imagine the best object to use would be a Range.

You would likely do this iteration with a For Each loop. This might be wrapped in, as you suggest, a Do Until loop, or you might use a Do While loop (Do While text.Value <> ""). Here again, it's a matter of preference which you use.

I'm an Access VBA developer and am not intimately familiar with the Excel object library, so I have no code lying about to provide you.

There are tons of ways to implement what you'd like to achieve. As you become more familiar with VBA, you will begin to form a style of coding, using constructs which suit your tastes.

I'm sure someone who is expert in Excel VBA will provide some decent code.