If condition is not met, go back to start of code VBA

[jazz2409]

Hi, how do I go back to the beginning of the code if a condition is not met? In my case, if column P is empty, then it should go back to the start of my For loop. If it's not empty then it should continue with the rest of the code. This is what I have so far:


If Application.CountA(Range("P:P")) = 0 Then

On Error GoTo 0


Else
'my code here
End If



That doesn't work.Please help.

[SamT]

Label1: '<-- Has Colon at end.
For i = 1 to n
'
If Condition Then Goto Label2 '<-- No Colon
'
'
'
If Condition Then Goto Label1 '<-- No Colon
'
'
'
Label2: '<-- Has Colon at end.
Next

[Paul_Hossler]

Hi, how do I go back to the beginning of the code if a condition is not met? In my case, if column P is empty, then it should go back to the start of my For loop. If it's not empty then it should continue with the rest of the code. This is what I have so far:


If Application.CountA(Range("P:P")) = 0 Then

On Error GoTo 0


Else
'my code here
End If



That doesn't work.Please help.


What's the For/Next loop look like?

[jazz2409]

What's the For/Next loop look like?

Hi Paul. here's the for loop:



Dim wkf As Worksheet
Dim SheetName As String
Dim check As Boolean
Dim rng As Range
Dim wks As Worksheet


Set wkf = Worksheets("Database")

'loop through all worksheets
For Each wks In ActiveWorkbook.Worksheets()
Set rng = Nothing
SheetName = wks.Name
On Error Resume Next
'check if the name of the sheet is included in the funds list
Set rng = wkf.Range("P:P").Find(SheetName, LookAt:=xlWhole)
On Error GoTo 0
If Not rng Is Nothing Then 'included in list
Application.DisplayAlerts = False 'Surpress warning message

If Application.CountA(Range("P:P")) = 0 Then


On Error GoTo 0


Else
'my code here
Application.DisplayAlerts = True
End If
End If
Next wks
Set wkf = Nothing
Set rng = Nothing

[Paul_Hossler]

I'd do something like this

My personal style is to avoid GoTo's, but in this type of situation, I find them clearer than a bunch of nested If/ElseIf/EndIf 's




Option Explicit


Sub test()


Dim wkf As Worksheet
Dim SheetName As String
Dim check As Boolean
Dim rng As Range
Dim wks As Worksheet




Set wkf = Worksheets("Database")

'loop through all worksheets
For Each wks In ActiveWorkbook.Worksheets()
Set rng = Nothing
SheetName = wks.Name

On Error Resume Next
'check if the name of the sheet is included in the funds list
Set rng = wkf.Range("P:P").Find(SheetName, LookAt:=xlWhole)
On Error GoTo 0

If rng Is Nothing Then GoTo NextWorksheet 'NOT included in list

If Application.CountA(wks.Range("P:P")) = 0 Then GoTo NextWorksheet ' <<<<<<<<<<<< think you need Worksheet

Application.DisplayAlerts = False 'Surpress warning message
'my code here
Application.DisplayAlerts = True




NextWorksheet:
Next wks

Set wkf = Nothing
Set rng = Nothing
End Sub

[jazz2409]

Hi Paul, thank you, works as intended.
I have a question, though.
What do you call the NextWorksheet part? Is it like select case?

[SamT]

What do you call the NextWorksheet part?
Label

[Paul_Hossler]

Hi Paul, thank you, works as intended.
I have a question, though.
What do you call the NextWorksheet part? Is it like select case?


It's a statement label and is used as the Target or destination of a GoTo

As I said, I (me, personally, just me :yes ) find that in this case, it improves code readability

GoTo's can be overdone, resulting in 'spaghetti code' where the flow is hard to follow, and hence to debug