Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown recurring 121212
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 -- Thank you Aussie Bob C Little cost to carry knowledge with you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown recurring 121212
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 Aussie Bob C wrote: 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 -- Thank you Aussie Bob C Little cost to carry knowledge with you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown recurring 121212
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 (MVP - Excel) "Aussie Bob C" wrote in message ... 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 -- Thank you Aussie Bob C Little cost to carry knowledge with you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown recurring 121212
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) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Aussie Bob C" wrote in message ... 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 -- Thank you Aussie Bob C Little cost to carry knowledge with you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown recurring 121212
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" wrote: 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) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Aussie Bob C" wrote in message ... 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 -- Thank you Aussie Bob C Little cost to carry knowledge with you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filldown recurring 121212
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 -- Rick (MVP - Excel) "Aussie Bob C" wrote in message ... 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" wrote: 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) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Aussie Bob C" wrote in message ... 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 -- Thank you Aussie Bob C Little cost to carry knowledge with you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent filldown | Excel Discussion (Misc queries) | |||
Macro filldown | Excel Worksheet Functions | |||
Filldown? & Cmb? | Excel Programming | |||
FillDown Macro | Excel Worksheet Functions | |||
FillDown | Excel Programming |