Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Help ASAP macro to fill range...
I know this is probably very simple but I've been banging my head against the
wall with this last minute, tight deadlined project that I'm just not thinking straight anymore. I need to fill a range of decending cells with a very particular patern... By manually filling a few and recording the macro I think you can see what I'm trying to accomplish... ActiveCell.FormulaR1C1 = "R4C3" Range("B9").Select ActiveCell.FormulaR1C1 = "R4C4" Range("B10").Select ActiveCell.FormulaR1C1 = "R4C5" Range("B11").Select ActiveCell.FormulaR1C1 = "R5C3" Range("B12").Select ActiveCell.FormulaR1C1 = "R5C4" Range("B13").Select ActiveCell.FormulaR1C1 = "R5C5" Range("B14").Select Each series is three cells ahs has to read like above with R# having a sequential numberical value that steps after thre instances and the C3, C4, C5 continually repeating itself. It does not have to run to the bottom of the sheet only to row 736 and specifically in Column B beginning at row 2. Like I said I'm sure this is not a difficult macro to generate properly but my brain matter walked off for lunch about an hour ago. Anyone who can help please please do. I hate to sound desperate but I'm beginning to feel that way and I refuse to manually enter this when I know there is a better way. Thank you thank you thank you!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Help ASAP macro to fill range...
No need to use a macro... you can just put this formula in B9 and drag down
=OFFSET($C$4,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3)) -- Regards, Juan Pablo González "Stephen" wrote in message ... I know this is probably very simple but I've been banging my head against the wall with this last minute, tight deadlined project that I'm just not thinking straight anymore. I need to fill a range of decending cells with a very particular patern... By manually filling a few and recording the macro I think you can see what I'm trying to accomplish... ActiveCell.FormulaR1C1 = "R4C3" Range("B9").Select ActiveCell.FormulaR1C1 = "R4C4" Range("B10").Select ActiveCell.FormulaR1C1 = "R4C5" Range("B11").Select ActiveCell.FormulaR1C1 = "R5C3" Range("B12").Select ActiveCell.FormulaR1C1 = "R5C4" Range("B13").Select ActiveCell.FormulaR1C1 = "R5C5" Range("B14").Select Each series is three cells ahs has to read like above with R# having a sequential numberical value that steps after thre instances and the C3, C4, C5 continually repeating itself. It does not have to run to the bottom of the sheet only to row 736 and specifically in Column B beginning at row 2. Like I said I'm sure this is not a difficult macro to generate properly but my brain matter walked off for lunch about an hour ago. Anyone who can help please please do. I hate to sound desperate but I'm beginning to feel that way and I refuse to manually enter this when I know there is a better way. Thank you thank you thank you!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Help ASAP macro to fill range...
I see where this is going but I think I might have miss spoke. My range
starts at B2 and works its way down to B736. My values need to read as follows... B2 needs to read R2C3 B3 needs to read R2C4 B4 needs to read R2C5 B5 needs to read R3C3 B6 needs to read R3C4 B7 needs to read R3C5 etc... "Juan Pablo González" wrote: No need to use a macro... you can just put this formula in B9 and drag down =OFFSET($C$4,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3)) -- Regards, Juan Pablo González "Stephen" wrote in message ... I know this is probably very simple but I've been banging my head against the wall with this last minute, tight deadlined project that I'm just not thinking straight anymore. I need to fill a range of decending cells with a very particular patern... By manually filling a few and recording the macro I think you can see what I'm trying to accomplish... ActiveCell.FormulaR1C1 = "R4C3" Range("B9").Select ActiveCell.FormulaR1C1 = "R4C4" Range("B10").Select ActiveCell.FormulaR1C1 = "R4C5" Range("B11").Select ActiveCell.FormulaR1C1 = "R5C3" Range("B12").Select ActiveCell.FormulaR1C1 = "R5C4" Range("B13").Select ActiveCell.FormulaR1C1 = "R5C5" Range("B14").Select Each series is three cells ahs has to read like above with R# having a sequential numberical value that steps after thre instances and the C3, C4, C5 continually repeating itself. It does not have to run to the bottom of the sheet only to row 736 and specifically in Column B beginning at row 2. Like I said I'm sure this is not a difficult macro to generate properly but my brain matter walked off for lunch about an hour ago. Anyone who can help please please do. I hate to sound desperate but I'm beginning to feel that way and I refuse to manually enter this when I know there is a better way. Thank you thank you thank you!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Help ASAP macro to fill range...
Try this: enter the following
B2 = 23 B3 = 24 B4 = 25 Then, in B5 enter =B2+10 and copy down as far as needed. Then, format the cells using a custom number format "R"0"C"0 Or, if you have to have the "R" and "C" in the actual data (not just displayed as such), repeat the steps above, but instead of custom number format use =TEXT(B2,"R0C0") and Copy, then Edit/Paste Special - values. "Stephen" wrote: I see where this is going but I think I might have miss spoke. My range starts at B2 and works its way down to B736. My values need to read as follows... B2 needs to read R2C3 B3 needs to read R2C4 B4 needs to read R2C5 B5 needs to read R3C3 B6 needs to read R3C4 B7 needs to read R3C5 etc... "Juan Pablo González" wrote: No need to use a macro... you can just put this formula in B9 and drag down =OFFSET($C$4,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3)) -- Regards, Juan Pablo González "Stephen" wrote in message ... I know this is probably very simple but I've been banging my head against the wall with this last minute, tight deadlined project that I'm just not thinking straight anymore. I need to fill a range of decending cells with a very particular patern... By manually filling a few and recording the macro I think you can see what I'm trying to accomplish... ActiveCell.FormulaR1C1 = "R4C3" Range("B9").Select ActiveCell.FormulaR1C1 = "R4C4" Range("B10").Select ActiveCell.FormulaR1C1 = "R4C5" Range("B11").Select ActiveCell.FormulaR1C1 = "R5C3" Range("B12").Select ActiveCell.FormulaR1C1 = "R5C4" Range("B13").Select ActiveCell.FormulaR1C1 = "R5C5" Range("B14").Select Each series is three cells ahs has to read like above with R# having a sequential numberical value that steps after thre instances and the C3, C4, C5 continually repeating itself. It does not have to run to the bottom of the sheet only to row 736 and specifically in Column B beginning at row 2. Like I said I'm sure this is not a difficult macro to generate properly but my brain matter walked off for lunch about an hour ago. Anyone who can help please please do. I hate to sound desperate but I'm beginning to feel that way and I refuse to manually enter this when I know there is a better way. Thank you thank you thank you!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Help ASAP macro to fill range...
Thanks for the thought but I ended up using a macro...
Sub test() Row = 2 a = 2 Do For i = 3 To 5 Cells(Row, 2).Value = "R" & a & "C" & i Row = Row + 1 Next i a = a + 1 Loop Until Row 735 End Sub and it works like a charm! "JMB" wrote: Try this: enter the following B2 = 23 B3 = 24 B4 = 25 Then, in B5 enter =B2+10 and copy down as far as needed. Then, format the cells using a custom number format "R"0"C"0 Or, if you have to have the "R" and "C" in the actual data (not just displayed as such), repeat the steps above, but instead of custom number format use =TEXT(B2,"R0C0") and Copy, then Edit/Paste Special - values. "Stephen" wrote: I see where this is going but I think I might have miss spoke. My range starts at B2 and works its way down to B736. My values need to read as follows... B2 needs to read R2C3 B3 needs to read R2C4 B4 needs to read R2C5 B5 needs to read R3C3 B6 needs to read R3C4 B7 needs to read R3C5 etc... "Juan Pablo González" wrote: No need to use a macro... you can just put this formula in B9 and drag down =OFFSET($C$4,INT((ROW(A1)-1)/3),MOD(ROW(A1)-1,3)) -- Regards, Juan Pablo González "Stephen" wrote in message ... I know this is probably very simple but I've been banging my head against the wall with this last minute, tight deadlined project that I'm just not thinking straight anymore. I need to fill a range of decending cells with a very particular patern... By manually filling a few and recording the macro I think you can see what I'm trying to accomplish... ActiveCell.FormulaR1C1 = "R4C3" Range("B9").Select ActiveCell.FormulaR1C1 = "R4C4" Range("B10").Select ActiveCell.FormulaR1C1 = "R4C5" Range("B11").Select ActiveCell.FormulaR1C1 = "R5C3" Range("B12").Select ActiveCell.FormulaR1C1 = "R5C4" Range("B13").Select ActiveCell.FormulaR1C1 = "R5C5" Range("B14").Select Each series is three cells ahs has to read like above with R# having a sequential numberical value that steps after thre instances and the C3, C4, C5 continually repeating itself. It does not have to run to the bottom of the sheet only to row 736 and specifically in Column B beginning at row 2. Like I said I'm sure this is not a difficult macro to generate properly but my brain matter walked off for lunch about an hour ago. Anyone who can help please please do. I hate to sound desperate but I'm beginning to feel that way and I refuse to manually enter this when I know there is a better way. Thank you thank you thank you!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Am confused about a simple data layout- need help asap! | Charts and Charting in Excel | |||
How can I save the formulas when running a macro? Need ASAP!! | Excel Discussion (Misc queries) | |||
auto fill simple data | Excel Programming | |||
Simple question - Named Range in Macro | Excel Programming | |||
Add a form onto a macro - Need help ASAP | Excel Programming |