Finding gaps within rows of numbers

**bd528** · 01-23-2011, 12:20 PM

Hello,

Please see attached.

Via macro, I am trying to establish where there is a gap in numbers, signified by a "-". The 2 cases of this are E3 and E5. Note that B7 isn't gap because it is the first numer in the sequence. Nor is L6, as it is the last.

I'd be grateful for some code to generate the TRUE and FALSE values in column A.

Thanks in advance.

**Marcol** · 01-23-2011, 12:33 PM

Try in A2

=IF(COUNTIF(C2:K2,"-")<>0,TRUE,FALSE)

Drag/Fill Down

Hope this helps

**snb** · 01-23-2011, 01:16 PM

In A2:

PHP Code:


=countif(C2:K2,"-")>0

**bd528** · 01-23-2011, 02:17 PM

Thanks for the replies. For both cases, I cant figure out how to use the code within a macro. Can someone explain how it should be formatted/coded?

Thanks again.

**Leith Ross** · 01-23-2011, 04:00 PM

Hello bd528,

The attached workbook has the formulas entered into column "A".

**bd528** · 01-23-2011, 04:06 PM

Thanks for your reply.

What I require is a macro that adds the code to insert that code into the sheet in cells A10:A15. Its doing this that I'm having trouble with.

Thanks.

**Leith Ross** · 01-23-2011, 04:10 PM

Hello bd528,

You lost me. Do you want a macro that will insert the formulas into these cells?

**bd528** · 01-23-2011, 04:14 PM

Originally Posted by Leith Ross

Hello bd528,

You lost me. Do you want a macro that will insert the formulas into these cells?

Yes, to insert the formula.

**Leith Ross** · 01-23-2011, 04:20 PM

Hello bd528,

Will the range start with "A1" and continue down to the last entry in column "A"?
How do you want the macro to be run: button, shortcut keys, both?

**snb** · 01-23-2011, 04:24 PM

this is your macro:

Please Login or Register  to view this content.

**bd528** · 01-23-2011, 04:26 PM

Yes, it will start at A1. I have defined the last row as "masterlastrow" elsewhere in the macro. This part of the code is about half way through the macro, so wont need executing seperately.

**Marcol** · 01-23-2011, 09:16 PM

Try this

Please Login or Register  to view this content.

Assuming your data begins in B1 and Column A is free to use, "MasterLastRow" can be from your call.

Hope this helps

**bd528** · 01-24-2011, 07:31 AM

Originally Posted by Marcol

Try this

Please Login or Register  to view this content.

Assuming your data begins in B1 and Column A is free to use, "MasterLastRow" can be from your call.

Hope this helps

That piece of code worked perfectly thanks.

Unfortunately, after applying it to my actual spreadsheet, it turns out I didn't explain my problem thoroughly.

Please see attached.

As per the attached, there can be many "-" per row, and not a "gap", as in row 8 - so this is FALSE. Row 3 has many "-" and also a gap at AD, so it's value in column J is TRUE.

**Marcol** · 01-24-2011, 08:57 AM

Try this with your new sample sheet

Please Login or Register  to view this content.

**bd528** · 01-24-2011, 08:57 AM

Originally Posted by snb

this is your macro:

Please Login or Register  to view this content.

Sorry, I missed your post.

I tried changing your code to suit the file I attached a few posts up. This is what I came up with :-

Please Login or Register  to view this content.

I think I must have written it wrong, as each result is coming up TRUE. Any ideas what I'm doing wrong. Its tricky to work out as the formula isn't displayed in the cell, just the result.

Thanks.

**bd528** · 01-24-2011, 09:04 AM

Originally Posted by Marcol

Try this with your new sample sheet

Please Login or Register  to view this content.

This works pretty well. However I added a value to AC18, re-ran the macro, and it still came through as FALSE. (I copied the value from AF18 to AC18). Any thoughts?

**Marcol** · 01-24-2011, 09:30 AM

Please don't quote whole posts in your replies, the Moderators don't like it..

Only quote a part of the post if it is really needed for clarity

Try this

Please Login or Register  to view this content.

**bd528** · 01-24-2011, 09:59 AM

Marcol, I think we're nearly there. I added a little extra data (attached). For some reason, the results on rows 25 & 25 are incorrect??

**Marcol** · 01-24-2011, 10:58 AM

Okay, try this

Please Login or Register  to view this content.

**bd528** · 01-24-2011, 12:34 PM

Marcol, this now works perfectly. Thanks for the time spent on this.

I'll make sure I read through your code and learn what's going on.

Thanks again.

**Marcol** · 01-24-2011, 12:35 PM

Happy to have helped.

Finding gaps within rows of numbers

LinkBack

Thread Tools

Rate This Thread

Display

Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Re: Finding gaps within rows of numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions