Loop in specific range and count nth cell not including blank cells

Hi All,

I need help on creating a macro to count and extracting the number. Please kindly refer to the details below and I’ve attached a sample spreadsheet for reference.

I wish to count on every 7th cells from left to right, blank cell are not included from counting. When the counting reaches number 45 at range("AS1"), its loop back to number 1 at range ("A1") to continue counting until the 7th number. All selected number will be cut and paste at Range("A4") to Range("G4").

Eg: It can start on any number, if its start on number 3 and there are 2 blank cells in between the range, then the selection of 7th cell is number 12 as number 3 and blank cells are not included from counting.

Any help would be appreciated. Thanks in Advance!

Regards,

Eric

Not entirely sure this does what you want or not...

Please Login or Register  to view this content.

the above assumes any cells in A1:AS1 that do not contain numbers to be blank and represents just one approach of many that you could adopt

Originally Posted by ericwcf

...after copy number 8, 15 and up to 7th number from Range(“A1:AS1”) to Range(“A4:G4”), so
may I know how do you remove the number 8, 15, etc that was just copied from Range(“A1:AS1”).

I confess I don't really understand the above.

What I would say is that is that if you don't understand some code it's a good idea to "step through" it via F8 in VBE - use the Locals Window etc to see what's going on regards specific variables etc as the code progresses.

For more info. re: "debugging" code see: http://www.cpearson.com/excel/Debug.htm

In terms of explanation (as best I can)...

The code essentially takes the values in A1:AS1 and populates an Array with only the non-blank values (ie all blanks are removed).

Please Login or Register  to view this content.

In your sample this means that the above generates an Array containing 40 items (0 to 39) - the 5 blanks being disposed of.

The code establishes the position of the "start" number within the Array:

Please Login or Register  to view this content.

(adjusting return by 1 to account for 0 based Array - ie Match 1 is item 0)

We then iterate 7 times (bCount 1 to 7) to retrieve the appropriate values given start position.

We use a MOD construct based on present bMatch value and interval of 7 to determine the appropriate Item number for each iteration:

Please Login or Register  to view this content.

The selected number is added to the previous numbers selected by virtue of an incrementing string variable:

Please Login or Register  to view this content.

once the 7 iterations are complete we then take the string of 7 numbers (strY), remove the leading space before subsequently splitting the string by Space character and returning the now 7 individual results to A4:G4

Please Login or Register  to view this content.

(the numbers are stored in the initial array and indeed strY as strings ["1" etc] - we use the numberformat and value = value route to coerce the final 7 values back to numbers)

edit: so you're saying in the case of the final number in the sequence the result is 15 given 12 is removed from consideration as it appears previously ?

in other words no number can be used more than once.

edit: given your example should the final two numbers not be 7 & 16 given 3 is also to be removed (per your earlier comments)

Assuming I am right and that for 3 result should be: 12, 20, 27, 35, 43, 7 & 16 (given exclusion of 3) then perhaps the below will work for you ?

Please Login or Register  to view this content.

OK, so 3rd variation in which the start number is not removed from the number set but number selected thereafter are removed from the set...

Please Login or Register  to view this content.