Can I fill non-adjacent cells with the fill feature? (example listed)

[vyrys]

Hi everyone,
this site is awesome. Thank you all for your input.... it is so helpful.

Something I can't seem to figure out, is how to enter data in non-adjacent cells using a fill command.

Here is what I am trying to do:
in the column, I am holding ctrl button to select every 10th cell down the sheet. I need to enter a date in every selected cell that is exactly 7 days apart, i.e., 11/7/08 then 11/14/08 then 11/21/08, etc.... but no other dates or data.

I have tried to figure out a way to do this other than manually, but am confounded.

Hope you can help me... my boss is waiting to see if I can pull it off.

Thanks!!!

 

[vyrys]

ok... now there is another problem (for me anyway)

every two columns are merged and centered. so it's not working right.

ie, AB CD EF GH... etc...

that's why I asked to skip over one when filling.

any ideas?

 

[Jonmo1]

That shouldn't make any difference....

 

[vyrys]

I didn't think so either, but it wont populate the cells.

The details:

I am beginning or Column C (instead of A)
Each two columns after that are merged and labeled as follows-
C/D Monday
E/F Tuesday
G/H Wednesday
I/J Thursday
K/L Friday
M/N Saturday
O/P Sunday

Funny thing is, Monday and Tuesday fill fine. Wednesday skips over a day, and then Thursday is also skipped by one day. Fri, Sat, and Sun have no data at all.

where no cells are merged, it fills properly and displays properly

 

[Jonmo1]

OK, it's probably getting populated, just not displaying correctly.

1 rule about merged cells, the TOP/LEFT cell of the merged range is the cell that will display it's data. so if G1 and H1 are merged, only data in G1 will display. H1 will still have data in it, it just won't be seen.

So keeping that in mind, what are the LEFT most cells of each range you want the dates to go in (all 7, Mon - Sun)

Another General Rule of Thumb about Merged Cells...
Keep away from them like they are the plague. They cause more problems than they solve.

A very similar effect can be achieved with "Center Accross Selection" found in the same place merged cells is found. In the Top Dropdown of that page...

 

[vyrys]

only two cells are merged together in a row.... then I skip 8 rows and merge again on the 9th row below that. (my original post incorrectly stated 10).

I can type in the merged cells and they display correctly. I can also drag and quick fill across them just fine.

It is the left cell of the pair that displays.... as you stated.

I have the 9th rows merged for about 600 rows down, after that, I do not have any formatting of cells. The dates entered from the macro display fine there, but not in the merged cells.

sorry for the trouble... I am learning though thanks to you, and I appreciate it a lot.

 

[Jonmo1]

OK, keeping in mind that only the LEFT cell of your merged range will display information. therfore information needs to go into the LEFT cell of the merged range.

Which columns do you want each date to go into.

Monday goes in Column ?
Tuesday goes in Column ?
Wed goes in column ?

etc...

 

[vyrys]

Monday=C
Tuesday=E
Wednesday=G
Thursday=I
Friday=K
Saturday=M
Sunday=O

 

[Norie]

Do you really need/want merged cells?

As you've probably seen they just cause problems, especially when working with them in VBA.

 

[Jonmo1]

Try

Sub AddDates()
Srow = 1
BeginDate = DateSerial(2008, 11, 7)
EndDate = DateSerial(2009, 11, 6)
NewDate = BeginDate
Do Until NewDate > EndDate
    Cells(Srow, "C").Value = NewDate
    For i = 1 to 6
        Cells(Srow, "C").Offset(0,i*2).Value = NewDate + i
    Next i
    NewDate = NewDate + 7
    Srow = Srow + 10
Loop
End Sub

 

[vyrys]

That is what I had the last time I think.....but I did try it, and it still is producing the same results.

It is a file that has been used for a couple of years and is populated with data that needs to be kept.

I can probably work it out by unmerging the cells from this point on and then running the macro.