Cannot get macro to skip onto next macro after condition met

[Melbobs]

Hi


I am super new to VBA so please treat me as dumb

I have a macro that has an IF statement in it. Basically it is referencing a cell, and if that cell is blank, I’d like it to go on to run the next macro. Previously the code said ‘End Sub’ but I realised that was overriding the instruction to proceed with the next macro.

There must be a very simple answer to this but I have tried everything. I even tried putting Else statements in.

Basically I need the code below to instruct (per the below sheet and cell references):

Check to see if CA2 is blank

If so, then I want it to immediately go to Application.run “Fcopydown”

If CA2 is populated I want it to copy the value to C3001 in Sheet1, then move down to the next block of code in the macro

Sheets("qryCC_PostOffice").Select

Range("CA2").Select

If Range("CA2") = "" Then Exit Sub

Selection.Copy

Sheets("Sheet1").Select

Range("C3001").Select

ActiveSheet.Paste


Please help as it’s driving me mad

M

 

[Belleke]

Hi,
Something like this?

Sub belle()
x = Range("CA2").Value
Select Case x
    Case vbNullString
    'your code when cell CA2 is empty
    Case Else
    'your code when cell CA2 is NOT empty
End Select
End Sub

 

[Stevie]

Belleke's approach combines your macros into one, which I would suggest is the right approach in most cases if it's simple code.
Below is another approach which keeps the code separate.

sub overlord
    call sub sub1
    call sub sub2
end sub
sub sub1
    Sheets("qryCC_PostOffice").Select
    Range("CA2").Select
    If Range("CA2") = "" Then Exit Sub
    Selection.Copy
    Sheets("Sheet1").Select
    Range("C3001").Select
    ActiveSheet.Paste
end sub
sub sub2
    'code for sub sub2
end sub

Obviously this will run the second code every time, but it is not clear whether this is what you wanted or not from your post.

If this was helpful, please click 'Like'.
^.^

 

[Melbobs]

Thanks Both.

Belle's solution worked perfectly for my needs. However, I have a secondary question (so I can also use this code elsewhere). What if I want the macro to just stop as soon as it reaches a blank cell i.e. what does "Your code when CA2 is empty" need to say if I just want the processing to stop rather than skip to another macro. I tried End sub but got an error message

 

[Stevie]

Exit Sub

End Sub causes an error as the VBA editor thinks you have a half written sub. 'Exit Sub' skips to the end of the current Sub.
If you have cleanup to do at the end, you can do something like:

Sub a
    if a = 1 then
        goto cleanup:
    else:
        'do some stuff
    end if
cleanup:
    'cleanup code
end sub

This will skip to the line identified as 'cleanup'.

If this was helpful, please click 'Like'!
^.^

 

[Belleke]

If a may give a tip, try not to use activate or select,
it is rarely necessary and it slows down your code big time.
For example

Sheets("Sheet1").Range("C3001").Paste

does exact the same as

 Sheets("Sheet1").Select
    Range("C3001").Select
    ActiveSheet.Paste