Filldown recurring 121212

[Aussie Bob C]

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

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastRow As Long

Set wks = Worksheets("Sheet1")

With wks
LastRow = .Cells(.Rows.Count, "R").End(xlUp).Row

If LastRow < 4 Then
MsgBox "Not enough rows to fill down!"
Else
.Range("Q3").Value = 1
.Range("Q4").Value = 2
.Range("Q3:Q4").AutoFill _
Destination:=.Range("Q3:Q" & LastRow), _
Type:=xlFillCopy
End If
End With

End Sub

 

[Rick Rothstein]

Put this formula in your "starting" cell in Column R (cell R3 from your example) and copy it down as far as you want...

=1+MOD(ROW(A2),2)

 

[Rick Rothstein]

Actually, a slightly more simple formula... assuming Row 3 is the starting row, place a 1 in R3 and then use this formula in R4 and then copy it down as far as you want:

=1+MOD(R3,2)

--
Rick (MVP - Excel)


Put this formula in your "starting" cell in Column R (cell R3 from your example) and copy it down as far as you want...

=1+MOD(ROW(A2),2)

 

[Aussie Bob C]

Rick

I was look for a macro method that would enter the values 1 & 2 into the
cells, it appears Dave's use of xlFillCopy is not supported in xl2004 for Mac.

I may have to enter your formulas via a macro and copy cell values in place.
Sorry I left out the xl2004 note in this post.
I use both xl2007 and xl2004.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.

 

[Rick Rothstein]

Give this macro a try (see the comment on the For statement first)...

Sub InsertOnesAndTwos()
Dim X As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
For X = 3 To 10 ' 3 is the start row, change 1000 to your end row
Cells(X, "R").Value = 1 + (Cells(X - 1, "R").Value Mod 2)
Next
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub