adding a goto skip if data as already input

[B-Man]

I have a vba formula that opens another workbook, matches the heading and paste the data in the last row +1
now I want to integrate a check function that makes sure that data isn't already pasted and if so skip the rest of the formula and pop a msg box up saying data already there..

2 problems I cant for the life of me work out how to integrate the match into the current formula


I have
col = 22
lastrow =11
data is currently in rows 5 to 11 in column 22
I need to match wsSource.Range("C3").Value with data in Row 5 to 11 on column 22 and if it matches then skip the rest of the formula that would paste it at the end.

the skip part of the code im trying is

If Application.WorksheetFunction.Match(wsSource.Range("C3").Value, WsDest.range('''''''somehow add the correct row and column here'''''''' ,0) = True Then GoTo Skip

and 2. how do I go to a skip point without it being run if its not skipped. As I put it at the bottom of the macro but if it doesnt skip (as I havent finished skip yet) it still ends up reading out my msgbox

 

[B-Man]

this seems to have got the formula working

If Application.IfError(Application.Match(wsSource.Range("C3").Value, wsDest.Cells(col).EntireColumn, 0), 0) Then GoTo Skip

now just need to sort out the skip part

 

[Joe4]

I would HIGHLY recommend NOT using a GoTo statement. Generally, you only want to use these in error handling, as it can create "spaghetti" code, and make things a bit harder to debug.
I don't think you need a GoTo anyway. Most of the time, all you need is an IF block, i.e.

If some condition Then
    'do this
Else
    'do that
End If

 

[B-Man]

I would HIGHLY recommend NOT using a GoTo statement. Generally, you only want to use these in error handling, as it can create "spaghetti" code, and make things a bit harder to debug.
I don't think you need a GoTo anyway. Most of the time, all you need is an IF block, i.e.

If some condition Then
    'do this
Else
    'do that
End If

I want to

If some condition Then
    msg box
end sub
Else
    finish off the code
End If
end sub

do i need to make an IF not? instead

If not some condition Then
    finish off the code
Else
   msg box
End If
end sub

?

ok so changing to an IF not has issues with my With formula as the if formula is halfway through my with formula

with WsDest
. blah bla
col= "22"
lastrow = 11

If not Application.IfError(Application.Match(wsSource.Range("C3").Value, wsDest.Cells(col).EntireColumn, 0), 0) Then 

Cells(Lastrow + 1, col).Value = wsSource.Range("C3").Value
Cells(Lastrow + 1, col + 1).Value = Round(wsSource.Range("AE10").Value / 1000, 1)
Cells(Lastrow + 1, col + 2).Value = wsSource.Range("AD10").Value
End With

... rest of formula

else
msgbox data already input

end if

end sub

 

[Joe4]

"End Sub" only comes at the very end of your procedure.
Basically, it is telling VBA "this is where my procedure code ends".

If you want to exit the procedure in the middle of the code, you would use "Exit Sub", i.e.

If some condition Then
    msg box
    Exit Sub
Else
    finish off the code
End If

end sub

Actually, you don't even need the "Else" if you just want to check for a condition and exit the sub.
You could simply place the rest of the code under the "IF" block like this:

If some condition Then
    msg box
    Exit Sub
End If
  
'the rest of your code here

End Sub

 

[B-Man]

Ah I knew there was another way to end the sub but end sub wouldn't work... cheers. hope that fixes my new problem (edited my last post above)
I don't know much about vba but with some googling and a little help I have done some pretty advanced formulas . this explains why I don't know some of the 'basics'

Edit:

That worked a treat! and much neater than my previous attempts.

thanks

 

[Joe4]

Not sure you saw, but I edited my last post to add another comment/code block at the bottom.
I like to use this method a lot when I am checking a bunch of things that might cause me to exit my code, and I don't want an absurdly long nested IF block.
I can just check each one individually in its own IF block (sometimes you may be able to use a single IF with some OR clauses, if it is not too complex).

 

[B-Man]

Not sure you saw, but I edited my last post to add another comment/code block at the bottom.
I like to use this method a lot when I am checking a bunch of things that might cause me to exit my code, and I don't want an absurdly long nested IF block.
I can just check each one individually in its own IF block (sometimes you may be able to use a single IF with some OR clauses, if it is not too complex).

yes I saw that. Much nicer and less spagetti, now if only the rest of my coding was that neat.

I used the second code block. its definitely nicer than having an absurdly long nested IF block.

 

[Joe4]

yes I saw that. Much nicer and less spagetti, now if only the rest of my coding was that neat.

I used the second code block. its definitely nicer than having an absurdly long nested IF block.

I agree.
If you only had a line or two of code, then using "Else" would be fine.
But if you have a lot of code, I like to have it outside of everything.