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

vyrys

New Member
Joined
Nov 7, 2008
Messages
12
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!!!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Welcome to the board...
You could use a macro to do it...

In this it starts in row 1, but you can adjust the SRow Variable to begin at whatever row you want. And it goes in column A

Code:
Sub AddDates()
Srow = 1
BeginDate = DateSerial(2008, 11, 7)
EndDate = DateSerial(2009, 11, 6)
NewDate = BeginDate
Do Until NewDate > EndDate
    Cells(Srow, "A").Value = NewDate
    NewDate = NewDate + 7
    Srow = Srow + 10
Loop
End Sub

Hope that helps..
 
Upvote 0
Here's a macro that will do what you want.


Code:
Sub Will_you_date_me()

Dim Counter As Long, Number_of_Entries As Long
Dim ThisCell As Range


    If Selection.Cells.Count = 1 Then
    
        If MsgBox("Do you want to start the dates in cell " & ActiveCell.Address & "?", vbQuestion + vbYesNo, "START IN CELL " & ActiveCell.Address) = vbNo Then
        
            Exit Sub
            
        End If
    
        Set ThisCell = ActiveCell
        
        ThisCell.Value = InputBox("Please enter the first date", "ENTER DATE", "7/23/1975")
        
        Number_of_Entries = Application.InputBox("How many dates would you like?", "ENTER NUMBER OF DATES", "100", , , , , 1)
        
        For Counter = 1 To Number_of_Entries
        
            ThisCell.Offset(10 * Counter).Value = ThisCell.Offset(10 * (Counter - 1)).Value + 7
            
        Next Counter
        
    End If
    

End Sub
 
Upvote 0
OK... that was completely awesome.

You guys are the coolest people on the planet.

Thank you.


now a real challenge if you want to play. (well a real challenge for me anyway)

after the weekly date is entered in every 10th cell of the column (vertically), is there a way to fill the row (horizontally) the remaining 6 days between the weekly dates?

Hope that is clear.

One way I thought was to change the macro data to reflect the new column and dates. I would have to do it 7 times though....

awwwww... poor me..... :)
 
Upvote 0
Try this

Code:
Sub AddDates()
Srow = 1
BeginDate = DateSerial(2008, 11, 7)
EndDate = DateSerial(2009, 11, 6)
NewDate = BeginDate
Do Until NewDate > EndDate
    Cells(Srow, "A").Value = NewDate
    For i = 1 to 6
        Cells(Srow, "A").Offset(0,i).Value = NewDate + i
    Next i
    NewDate = NewDate + 7
    Srow = Srow + 10
Loop
End Sub
 
Upvote 0
wow... you are good.

is it possible to skip a column when filling the row horizontally?

i.e. column A, C, E, G, I, J, L have the dates in them....


I'm getting picky now huh?
 
Upvote 0
Not too picky, seen much worse....

Wait, had it wrong. Just a sec

OK, change

Cells(Srow, "A").Offset(0,i).Value = NewDate + i

to

Cells(Srow, "A").Offset(0,i*2).Value = NewDate + i
 
Upvote 0
reread the previous post. I first posted wrong code, but then corrected it.
 
Upvote 0
sorry,
quick on the draw there....

yes your correction was spot on. Thank you very much, that is quite impressive.

A jolly good show old bean.
 
Upvote 0

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top