Simple Help ASAP macro to fill range...

S

Stephen

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!!!
 
J

Juan Pablo González

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))
 
S

Stephen

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...
 
J

JMB

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.
 
S

Stephen

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top