Stubborn Evaluate 2015 Error

Please Login or Register  to view this content.

Doesn't make sense...

Hi Richard,

Thank you - looks like a great resource!

This is the cut-down, anonymised version as I have removed the code which cuts+paste row corresponding to position of matching empty cell. The idea of the "match and move action" is captured in the attached snippet. Hope this helps?


Regards,

Originally Posted by jindon

Please Login or Register  to view this content.

Doesn't make sense...

From my readings I understand that a cell can be Null or truly "empty" and gathered that the sub couldn't find an empty cell. So, I repurposed some code using Match-Index but I am not convinced of its efficacy myself. Interestingly, it seems to find an empty cell because it does not assign h=0, instead showing the error.

Actually, @Jindon and @Richard Buttrey, is it better if I move this problem to the commercial service, given it seems trickier than usual?

No, you don't need to.

1) Assuming Range is the Range name for A1:A10

Please Login or Register  to view this content.

It returns Error when A1:A10 are all filled.
But, if it is a variable, that line always returns Error.

2) The conditions for both If and ElseIf clause are the same.

Please Login or Register  to view this content.

ElseIf will never let the variable h to 0.

So, even if 1) returns correct results, it will do nothing...

If you upload a workbook and show us before/after, it will help.

Thanks, @jindon. Constructing a workbook excluding confidential work info. In the meantime:

(1) Altered variable type to Range (which should name the range).

(2)
(a) Hmmm, so both statements are perfectly alike which means the elseif condition is not satisfied and would let h=0?
(b) Retained IsEmpty form only but still not finding the blanks.

Also, if a match is found it would skip the If conditions and return something?

[] , shortcut notation of Evaluate method doesn't accept Variables.
So, if a Range is a String Variable like "A1:A10", it should be like

Please Login or Register  to view this content.

If Range is a Range object

Please Login or Register  to view this content.

If, I said earlier, Range is the name of the Named Range in the worksheet, your code should return Error or Numeric value.

All I want to say is that We can not even guess, what you are trying to do from the code you posted...

Please see attached workbook to demonstrate my objective

Originally Posted by jindon

[] , shortcut notation of Evaluate method doesn't accept Variables.
So, if a Range is a String Variable like "A1:A10", it should be like

Please Login or Register  to view this content.

If Range is a Range object

Please Login or Register  to view this content.

If, I said earlier, Range is the name of the Named Range in the worksheet, your code should return Error or Numeric value.

All I want to say is that We can not even guess, what you are trying to do from the code you posted...

This is really good to know. I have compiled a workbook demonstrating my objective, @jindon, thank you for persevering.

What is the best reference book for VBA Excel in your opinion? … I'd rather not cause unnecessary annoyance.

Originally Posted by jindon

You are welcome.
You may find good material here, New to Excel Programming and looking for learning materials

As for the code, I still don't understand what you are trying to do.

What do you expect to happen when
1) Blank cell is found
2) All are filled (No blank within the range)
?

Thank you, I will buy this book and begin a proper study of the language.

(1) Using the location of the cell - row number specifically - to move the corresponding row to the bottom of the range; then delete original position which shifts the rows up (hence looping backwards).
(2) Attached snippet shows 2 blank cells in the range

Hope this helps?

Do you just want to delete the blank cell(s)?

Please Login or Register  to view this content.

Originally Posted by jindon

Do you just want to delete the blank cell(s)?

Please Login or Register  to view this content.

No, the row containing the blank cell is important and, further, because the Do loop is moving up (range scope diminishing) it would stop the moment no blanks remained. Not meaning to sound cryptic but the column is sorted after rows with blanks are at the bottom.

Then can you just upload a workbook with Before the macro and After the macro sheets?

Originally Posted by jindon

Then can you just upload a workbook with Before the macro and After the macro sheets?

Wish I could but the datasets are huge and highly sensitive, what I have provided is reduced to isolate the matching issue. Essentially, I just need the match function to identify the existance of blanks - they exist - because the rest of the code surrounding it works.

Since I'm not clear, this is to give you the Row ref/(Address) of blank cell(s) from bottom to the top.

Please Login or Register  to view this content.

I need to go out now, so my next response will be late.

Maybe this snippet will help

Not really sure if this is how you wanted.

Please Login or Register  to view this content.

Originally Posted by jindon

Not really sure if this is how you wanted.

Please Login or Register  to view this content.

This is getting closer, @Jindon, but due to dataset size I was incorporating a Do While loop to exit when blanks are no longer found. After playing with your construct, I am unsure how to rearrange as such. I'll keep trying.

So, in summary, the following 4 formula's achieve the desired result:
(1)

Please Login or Register  to view this content.

(2)

Please Login or Register  to view this content.

(3)

Please Login or Register  to view this content.

(4) ***non-VBA compatible

Please Login or Register  to view this content.

Disregarding (4), why is it so technically complicated to EVALUATE (1)-(3)? It would simplify our approach a lot. For example, I don't understand why writing

Please Login or Register  to view this content.

is syntactically incorrect?

Originally Posted by rorya

They are not syntactically incorrect.

Hmm, yet output is still Error 2015 and blanks are clearly contained inside the specified range. Theoretically, variable h should = some row number corresponding to the 1st blank. Don't get it, @rorya.

Originally Posted by rorya

Post a simple workbook with the actual code, including specific ranges, that shows it not working.

Please see attached, @rorya, the code is now completely stripped down to the evaluate function yielding the same Error 2015.

Don't know how many rows/columns you have.

Please Login or Register  to view this content.

Originally Posted by rorya

Your formula is wrong. For example:

Please Login or Register  to view this content.

works just fine.

Thank you, @rorya. Unclear as to why encasing the string compiles correctly in VBA but it works. Interestingly, after verifying the output, nesting the function inside a loop produces an Error 2042. Do you know why this is the case or a subject for another question?

Originally Posted by jindon

Don't know how many rows/columns you have.

Please Login or Register  to view this content.

Wow, this looks cool but is beyond my comprehension. I have tried the following:

Please Login or Register  to view this content.

Which errors. Evaluate computes correctly without error outside the Loop though. Is this structure possible, @Jindon?

What does that Do Loop suppose to do?

h is always the same value Error or 1st blank row.

Please Login or Register  to view this content.

Again I don't understand the use of Evaluate method in this case...

Originally Posted by jindon

Please Login or Register  to view this content.

Again I don't understand the use of Evaluate method in this case...

I am not advanced enough to use your previous solutions regrettably, so Evaluate is the most powerful general solution I can understand and adapt to other questions.

Damn … I can't get rid of this Error 2042 - is your h variable still erroring, @Jindon?

Evaluate method is indeed powerful, but not in this case.
It slows down the process.

Originally Posted by Maze123

Damn … I can't get rid of this Error 2042 - is your h variable still erroring, @Jindon?

Missed this one...

You can not get rid of Error 2042 because of your formula structure.
That's why

Please Login or Register  to view this content.

that Evaluate should return 1 when A & i is blank, otherwise Error 2042,
Why not just

Please Login or Register  to view this content.

So no calculation time is taken.