filldown recurring 121212

[Aussie_Bob_C]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi

Can I filldown column Q starting at Q3 the recurring numbers 121212 down to
lastcell in column R.

Example:
Cell
Q3 - 1
Q4 - 2
Q5 - 1
Q6 - 2
Q7 - 1
Q8 - 2

TIA

 

[CyberTaz]

One option:

Enter 1 in Q3
Enter 2 in Q4
Select both cells, Copy
Select the rest of the cells in column Q, Paste.

But your mention of column R confuses me

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Aussie_Bob_C]

Hi Bob

I wanted to add a sub routine to an existing macro to automate the 12 recurring process, your method is manual.
In xl2007 I can use xlFillCopy which would do the job, but xl2004 for Mac does not support xlFillCopy.

My mention of the column R refers to using a contiguous column of data to tell the FillDown Destination to stop at the last cell in column R containing data.
Example:-

Range("B1:B2").Copy
Range("Q3").AutoFill Destination:=Range("Q3:Q" & Range("R65536").End(xlUp).Row)
' The above code will not enter 121212 down column Q

I received a reply from a Rick Rothstein with attached code as below.
It requires a helper cell at the moment to work.
Should I enter the code as a reply to my own question?

Sub InsertOnesAndTwos()

''' This macro working ok in DVD Collection.xls (SEE the comment on the For statement first)...

Dim X As Long

With Application
..ScreenUpdating = False
..Calculation = xlCalculationManual

''' ===============================================================
''' Enters #1 in cell Q3 and #2 in cell Q4 recurring down to last cell in column R.
''' Requires helper cell Q1 to count Non blank contiguous cells in column R.
''' Q1 contains formula =COUNTA(R:R)
''' ===============================================================
For X = 3 To Range("Q1")
Cells(X, "Q").Value = 1 + (Cells(X - 1, "Q").Value Mod 2) ' counts down from row 1 making row 3 a 1
Next
..Calculation = xlCalculationAutomatic
..ScreenUpdating = True
End With

End Sub



Cheers

Bob C

 

[CyberTaz]

Sorry, I had no idea you wanted to do this programmatically. I can't help
you on XLM/VBA issues, although there are several others around here who
can. I'll see if I can call their attention to this thread.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

I wanted to add a sub routine to an existing macro to automate the 12
recurring process, your method is manual.
In xl2007 I can use xlFillCopy which would do the job, but xl2004 for Mac
does not support xlFillCopy.

Not sure why you think that xlFillCopy isn't supported. One way:

Dim nRows As Long
nRows = Range("R" & Rows.Count).End(xlUp).Row - 2
If nRows < 1 Then
Range("Q3").Value = 1
Else
With Range("Q3:Q4")
.Value = Application.Transpose(Array(1, 2))
.AutoFill Destination:=.Resize(nRows, 1), Type:=xlFillCopy
End With
End If

 

[Aussie_Bob_C]

Hi J.E.

I stand corrected, xlFillCopy does work in xl2004 for Mac.
Ran your code and it worked perfectly.

The other code I tried came from a mainly windows forum group. The error message that appeared with xlFillCopy the only word in the line of code highlighted.

I usually work with xl2007 for Windows, so I assumed incorrectly as it turned out it wasn't supported.

Thankyou