I want to set an if statement on my macro, if a cell meets a certain condition, do not run macro, else run macro
unimportant background info
I have a macro that i'm writing where the user selects a work package executes the macro then that macro selects a range of
cells and copies them, then pastes the values then selects the cell in column A 6 rows down, the macro then executes again
doing the same thing as long as the user holds down the shortcut key. I'm also writing an alternate macro that does the
exact same thing except instead of replacing the cells with values it replaces the cells with the formulas. I don't want the
macro to be automatic at this time, i want it to have to be ran on each work package, that way the user can easily run it on
the whole list of work packages if they hold the shortcut key down or it can easily be executed on just one work package by
hitting the shortcut key once.
the idea is that the user starts at the top, holds down the shortcut key for the
macro and the macro keeps executing until it has executed on the last work package. After executing in the last work package
the cell it selects 6 rows down will have a defined name of "STOP"
when it reaches that last cell with
the name of STOP rather than executing it should cancel itself/not execute and instead return a message that says "End
of worksheet reached, Macro can not be executed from this cell". It doesn't need to be a special form control text box,
just if there is an easy way to make it show that message in the standard grey excel macro debug text box that usually pops
up on errors would be great so that the user knows what's going on or that they reached the end.
this is my code:
' PasteValue Macro
' Keyboard Shortcut: Ctrl+Shift+Z
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
this is what i want it to do
So I want it to be something along the lines of:
If Active.Selection (or currently selected cell/don't know the
syntax) = "STOP" (defined name of the cell is stop, not the cell value/contents)
Do not run macro and
return message saying you have reached end of spreadsheet and macro cannot be executed from this cell.
Active.Selection <> "STOP" Then
Execute the macro/above lines of code as it's written.
So if defined name = stop, don't run macro, else if, run macro
and if i wanted to how would i make such code
check multiple condtions. Like an if statement with (and) or (or) in it
Attached is a sample spreedsheet i'm
working with. I only have two work packages added at the moment but a user could have only a few or a couple hundred in the
spreadsheet which is why i want the user to be able to hold down the macro shortcut key and have it keep executing till it
reaches the end and then stop without the user having to pay particular attention or worry about breaking the spreadsheet by
running the macro after it has reached the end.
and is there a good online resource for how to
set conditions or if statements for macros. I'm quite good with if statements but i have no idea how to put if statements
into macro form. So i can record most of my macros but don't know how to take it to the next step and make them conditional
or error trap them.