Stop a macro if cell is empty
Solved
Dimitri020386
Posted messages
5
Status
Member
-
Dimitri020386 Posted messages 5 Status Member -
Dimitri020386 Posted messages 5 Status Member -
Hello everyone,
I started programming in VBA on Excel last year to create a finance tracking file.
I finished this file for the first time, but I started from scratch with all the macros to simplify it because I had way too many manipulations to do before running the update macro.
I searched in vain for the lines of code to integrate at the beginning of my macro (maybe I haven't looked in the right place).
Here's my problem:
I need to copy the non-empty cells located between "M8" and "N38" across 7 different sheets to the cells located after the last non-empty cells in columns "B" and "C."
I'm managing that part.
Before copying these cells, I want to stop the execution of the macro if at least one of the cells "M8:N8" on at least one sheet is empty.
And at the same time, display a warning window with a message.
If all the cells "M8:N8" are non-empty, I would, of course, like to continue executing my macro.
I hope I have been clear enough in my explanations.
If not, I will try to clarify any unclear points.
Thank you in advance to those who take the time to read and help me resolve this issue.
Dimitri
I started programming in VBA on Excel last year to create a finance tracking file.
I finished this file for the first time, but I started from scratch with all the macros to simplify it because I had way too many manipulations to do before running the update macro.
I searched in vain for the lines of code to integrate at the beginning of my macro (maybe I haven't looked in the right place).
Here's my problem:
I need to copy the non-empty cells located between "M8" and "N38" across 7 different sheets to the cells located after the last non-empty cells in columns "B" and "C."
I'm managing that part.
Before copying these cells, I want to stop the execution of the macro if at least one of the cells "M8:N8" on at least one sheet is empty.
And at the same time, display a warning window with a message.
If all the cells "M8:N8" are non-empty, I would, of course, like to continue executing my macro.
I hope I have been clear enough in my explanations.
If not, I will try to clarify any unclear points.
Thank you in advance to those who take the time to read and help me resolve this issue.
Dimitri
7 answers
Hello,
you need to place
That's all I can tell you, without a file or code in front of me!
--
Remember to say "thank you" and to mark it as "resolved" when the issue is fixed.
you need to place
exit subafter the part of the macro that checks for any empty cell(s).
That's all I can tell you, without a file or code in front of me!
--
Remember to say "thank you" and to mark it as "resolved" when the issue is fixed.
Hello,
I confirm, it's impossible to respond correctly without a working file.
We don't know if these are values or formulas. Whether an empty cell is really empty (without formula), or if "" should be considered as empty, and so on...
cjoint.com and paste the provided link here
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to saying thank you (yes, it happens!!!), remember to mark it as resolved. Thank you
I confirm, it's impossible to respond correctly without a working file.
We don't know if these are values or formulas. Whether an empty cell is really empty (without formula), or if "" should be considered as empty, and so on...
cjoint.com and paste the provided link here
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to saying thank you (yes, it happens!!!), remember to mark it as resolved. Thank you
OK, I understand.
Here is the link to my file:
https://www.cjoint.com/c/JJqlFZOkhFJ
I have noted the exit sub (Thank you Yoyo01000)
I confirm that the cells will be completely empty, with no formulas in them.
Here is the link to my file:
https://www.cjoint.com/c/JJqlFZOkhFJ
I have noted the exit sub (Thank you Yoyo01000)
I confirm that the cells will be completely empty, with no formulas in them.
I assumed that your tabs were the last 7, you could have specified...
If these tabs are still consecutive, add a name using the formula:
This calculates the number of entries between these 2 sheets (inclusive).
In case of changes, you will only have this formula to modify.
In the code:
If these sheets are subject to change, it might be interesting to create a Start sheet, another End sheet (adjust the formula), and insert/remove between the two while remembering to adjust the number of desired entries in the formula.
eric
--
By continually trying, one eventually succeeds.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, it does happen!!!), remember to mark it as resolved. Thanks
If these tabs are still consecutive, add a name using the formula:
=COUNT('Data My savings:Data ACTIONS EDF'!$M$8:$N$8)=14 This calculates the number of entries between these 2 sheets (inclusive).
In case of changes, you will only have this formula to modify.
In the code:
If Not [entriesOK] Then Exit Sub
If these sheets are subject to change, it might be interesting to create a Start sheet, another End sheet (adjust the formula), and insert/remove between the two while remembering to adjust the number of desired entries in the formula.
eric
--
By continually trying, one eventually succeeds.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to the thank you (yes, it does happen!!!), remember to mark it as resolved. Thanks
Thank you very much Eric,
Indeed, I completely forgot to specify, my apologies.
I just did a test, I don’t understand much because I tried what you gave me:
"NBVAL("Data Mon épargne:Data ACTIONS EDF" & "M8:N8") = 14
If Not NBVAL Then Exit Sub"
Not working, I attempted a modification because Visual Basic doesn’t accept " ' " and " ! ".
So I wrote the following, but I think I made at least one (if not more) mistake(s) in my code writing.
extractionOK = NBVAL("Data Mon épargne:Data ACTIONS EDF" & "M8:N8") = 14
If Not extractionOK Then Exit Sub
The macro is stuck on "NBVAL"
I have an error message saying:
"Compilation error:
Sub or function not defined"
Can you tell me what’s wrong?
I'm using Microsoft Visual Basic 7.1, is it possible that some functions are only compatible with certain versions of Visual Basic?
And by the way, I'm new to forums, how do you integrate lines of VBA code into a box?
Dimitri
Indeed, I completely forgot to specify, my apologies.
I just did a test, I don’t understand much because I tried what you gave me:
"NBVAL("Data Mon épargne:Data ACTIONS EDF" & "M8:N8") = 14
If Not NBVAL Then Exit Sub"
Not working, I attempted a modification because Visual Basic doesn’t accept " ' " and " ! ".
So I wrote the following, but I think I made at least one (if not more) mistake(s) in my code writing.
extractionOK = NBVAL("Data Mon épargne:Data ACTIONS EDF" & "M8:N8") = 14
If Not extractionOK Then Exit Sub
The macro is stuck on "NBVAL"
I have an error message saying:
"Compilation error:
Sub or function not defined"
Can you tell me what’s wrong?
I'm using Microsoft Visual Basic 7.1, is it possible that some functions are only compatible with certain versions of Visual Basic?
And by the way, I'm new to forums, how do you integrate lines of VBA code into a box?
Dimitri