Empty cells not evaluating as empty - multiple check methods tried

**rafadavidc** · 01-31-2014, 03:58 PM

I have an inelegant global macro that I'm trying to refine. Here's the old one:

Please Login or Register  to view this content.

This all works nicely, except I'm limited to six rows. I'd like to iterate a loop to unlimit myself, because I suspect I'll be getting bigger sheets with more blanks needing to be checked.

Here's what I've come up with:

Please Login or Register  to view this content.

Line 04 is failing to evaluate properly. I have tried the following:

stuff = ""
stuff = vbNullString
IsEmpty(stuff)
IsEmpty(Range(stuff))
IsEmpty(Range("stuff"))
IsEmpty(Cells(stuff))
IsEmpty(Cells("stuff"))

I've also tried all those variants with stuff.Value.

Stepping through this new macro with nested For loops, it will arrive at line 04, then jump down to line 08. This happens on every single row, regardless of the state of .Offset(1,1), regardless of what method I use to check if it's empty.

What I don't understand is why in the old macro, = "" works, with nothing working when checking the same row on the same sheet with the new one.

What am I missing?

**judgeh59** · 01-31-2014, 04:05 PM

does this need to be

old

Please Login or Register  to view this content.

new

Please Login or Register  to view this content.

**rafadavidc** · 01-31-2014, 04:16 PM

Originally Posted by judgeh59

does this need to be

old

Please Login or Register  to view this content.

new

Please Login or Register  to view this content.

I don't get what you're asking. I've tried that variation, and it still doesn't evaluate TRUE.

**judgeh59** · 01-31-2014, 04:21 PM

gotcha - I didn't see that in the list of things you have tried....

maybe try

Please Login or Register  to view this content.

if that doesn't work....on the cell you are evaluating, not using VBA, click on a blank cell and type

=LEN(<cell reference)
it should come back as 0

**rafadavidc** · 01-31-2014, 04:26 PM

Originally Posted by judgeh59

gotcha - I didn't see that in the list of things you have tried....

maybe try

Please Login or Register  to view this content.

if that doesn't work....on the cell you are evaluating, not using VBA, click on a blank cell and type

=LEN(<cell reference)
it should come back as 0

Tried TRIM(), still failing. LEN() on the cell in question returns 0.

**judgeh59** · 01-31-2014, 04:27 PM

is it possible to upload a small sample file after taking out any company confidential stuff and with the macro?

**rafadavidc** · 01-31-2014, 04:43 PM

Sure. Where? I don't see an attachment function here.

**judgeh59** · 01-31-2014, 04:45 PM

check the FAQ section....but basically instead of clicking POST QUICK REPLY, click GO ADVANCED and there is an add attachment area.....yup, not very inituitive....

**Norie** · 01-31-2014, 04:49 PM

What is your code meant to do?

Why do you have 2 identical loops in it?

PS To attach - Click Go Advanced, then click the paperclip, just like you would if attaching a file to an email.

**rafadavidc** · 01-31-2014, 04:56 PM

bb0102 - Copy.xlsx

Old macro:

Please Login or Register  to view this content.

New macro:

Please Login or Register  to view this content.

**rafadavidc** · 01-31-2014, 05:00 PM

If you look at rows 203-209 as an example, you'll see what I'm trying to accomplish: Sum O203:O209 (that's the total on order), add to H203 (that's the on-hand quantity), compare to G203 (that's allocations for that part), write I203 or zero in E203 depending on that comparison.

First macro works flawlessly. Second one fails to evaluate F204 as empty - why?

**Norie** · 01-31-2014, 05:10 PM

Does the code ever reach F204?

**rafadavidc** · 01-31-2014, 05:16 PM

That's the cell being evaluated with .Offset(1, 1). The macro steps to the If, then jumps to the Else. I don't know a better answer to your question, sorry.

**Norie** · 01-31-2014, 05:26 PM

I've checked and the inner loop doesn't go beyond E2,

You can see that if you put a debug line in the code like this.

Please Login or Register  to view this content.

If you look at the Immediate Window (CTRL+G) after you've run the code you'll hopefully see what I mean.

**rafadavidc** · 02-03-2014, 10:50 AM

Originally Posted by Norie

I've checked and the inner loop doesn't go beyond E2,

You can see that if you put a debug line in the code like this.

Please Login or Register  to view this content.

If you look at the Immediate Window (CTRL+G) after you've run the code you'll hopefully see what I mean.

OK, I see that now. What do I do with this information? I don't know how to change my code accordingly; nothing is jumping out at me as causing this behavior.

**Norie** · 02-03-2014, 12:28 PM

Rows is 0 at the start.

E2 is the first cell in the inner loop and E2.Offset(1, 1) <>"" so the code skips to here,

Please Login or Register  to view this content.

then to here.

Please Login or Register  to view this content.

The code then goes back to here starting at E2 again,

Please Login or Register  to view this content.

and the same thing happens again until all the iterations of the outer loop are completed.

**rafadavidc** · 02-03-2014, 03:33 PM

Originally Posted by Norie

Rows is 0 at the start.

E2 is the first cell in the inner loop and E2.Offset(1, 1) <>"" so the code skips to here,

Please Login or Register  to view this content.

then to here.

Please Login or Register  to view this content.

The code then goes back to here starting at E2 again,

Please Login or Register  to view this content.

and the same thing happens again until all the iterations of the outer loop are completed.

Ohhhhhhhhh.

Alright.

What I need to happen is for the inner loop to start iterating on the same row that the outer loop is on when the inner loop is called. How do I manage that?

I can create a variable called MyRow, and set it in the outer loop with MyRow = ActiveCell.Row, but I don't know how, in the inner loop, to set the Active Row via a variable's value. If I could do that, it'd solve my problem, yes?

Edit -

Is the answer right here?

Please Login or Register  to view this content.

Replace the 2 with "ActiveCell.Row" or something like that?

**Norie** · 02-03-2014, 03:53 PM

You wouldn't use ActiveCell, you would use the loop variable from the outer loop - curCell.

Something like this.

Please Login or Register  to view this content.

However I'm not sure that will fix the problem.

**rafadavidc** · 02-03-2014, 05:20 PM

Originally Posted by Norie

You wouldn't use ActiveCell, you would use the loop variable from the outer loop - curCell.

Something like this.

Please Login or Register  to view this content.

However I'm not sure that will fix the problem.

I don't understand Range calls that well. Can you English out your code suggestion for me?

That is to say, Why call the variable first, then the Range? That's not how the Range call works in the outer loop. What's the syntax rule you're following that requires you to do [variable]-comma-space-Range("[partial Range]" and the rest as I would expect)?

**rafadavidc** · 02-03-2014, 05:33 PM

Tried it. Ending up with somewhat the opposite problem - Now, I'm failing to get quantities in multi-row parts where I should, instead of getting quantities where I shouldn't.

Ctrl+G shows that the inner loop only operated on rows 583 to 685. Not sure why that should be.

**rafadavidc** · 02-03-2014, 05:54 PM

This helped. It also helped me understand just WHY I'm concatenating LastRow into my Range.

Here's my rowsCell Range:

Please Login or Register  to view this content.

Result is.....

Fail.

Rows 66 and 67 should result in E66 being populated. Instead, I get a zero there. Buh.

EDIT

Range issue is solved. With this out of the way, I've watched the loop as it steps through iterations of the inner loop and seen it flail without a way to break out at the appropriate time. Thanks for your help on this; I now know what's wrong, and it's just a matter of basic programming principles at this point.

Empty cells not evaluating as empty - multiple check methods tried

LinkBack

Thread Tools

Rate This Thread

Display

Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Re: Empty cells not evaluating as empty - multiple check methods tried

Thread Information

Users Browsing this Thread

Similar Threads

Check for multiple range and if any cell is not empty do something

Need help evaluating two pairs or four cells for empty values

check if multiple text boxes are empty

check for non-empty cells outside of a certain range

Evaluating a Range of Empty Cells with VBA

Bookmarks

Bookmarks

Posting Permissions