Help on existing Macro

[endlessboundless]

I have the following Macro and I would like it to also populate Jan, Feb, Mar... Dec (over and over again) under the C2 on the Actual and Budget pages. Can anyone please help?

Dim Rng As Range
Dim Ray As Variant
Dim Dn As Range
Set Rng = Range(Range("C17"), Range("C" & Rows.Count).End(xlUp))
For Each Dn In Rng
Ray = Ray & Application.Rept(Dn.Value & ",", 12)
Next Dn
Sheets("Actual").Range("C2").Resize(, Rng.Count * 12) = Split(Ray, ",")
Sheets("Budget").Range("C2").Resize(, Rng.Count * 12) = Split(Ray, ",")

TYIA!

 

[endlessboundless]

Okay, easier question. How can I change the following Macro to that the fill is from A2-to-X, based on the values in A1-to-X?

I can't seem to change it to make it work...

Sub FillTheColumn()
Dim X As Range
Dim XX As Range
Set XX = ActiveSheet.Range("A1")
Set X = ActiveSheet.[B1]
X.AutoFill Destination:=X.Resize(XX.End(xlDown).Row, 1)
Range("A2:Z2").Select
Set X = Nothing
Set XX = Nothing
End Sub

Thanks!

 

[endlessboundless]

Sorry, this is the one I want to change....

Sub FillThesssColumn()
Dim X As Range
Dim XX As Range
Set XX = ActiveSheet.Range("A1")
Set X = ActiveSheet.[B1]
X.AutoFill Destination:=X.Resize(XX.End(xlDown).Row, 1)
Range("B1:B10").Select
Set X = Nothing
Set XX = Nothing
End Sub

TYIA!

 

[Norie]

So you want to fill what's in B1 down as many rows as there are data in column A?

What is in B1? A formula? A value?

If it's just a value that you want to repeat you could use this.

With Range("B1")
     .Copy Range("B1").Resize(Range("A"& Rows.Count).End(xlup).Row))
End With

Or even this.

With Range("B1")
     .Resize(Range("A" & Rows.Count).End(xlUp).Row).Value = .Value
End With

You could also use the first one for formulas.

If however it's a series/sequence, eg Jan, Feb..., then other code might be needed or the above code changed.

 

[endlessboundless]

Thanks for repy. Okay so here's the situation.

I want to fill Jan - Dec as many times as possible from A2 - End, as long as data exists in the cells above... So if i have "dept 1" across the top 12 times and then "dept 2"... i want jan - dec under each dept.


Dept 1 Dept 1..............Dept 2
Jan Feb...................Dec

 

[Norie]

So do you already have the first row filled in?

Try this.

Dim rng As Range
 
'=TEXT((MOD(COLUMN()-1, 12)+1)&"/2011","mmm")
 
Set rng = Range("A2").Resize(, Cells(1, Columns.Count).End(xlToLeft).Column)
 
With rng
    .Formula = "=TEXT((MOD(COLUMN()-1, 12)+1)&""/2011"",""mmm"")"
    .Offset(1).Value = .Value
End With

 

[endlessboundless]

Yes 1st row is alreay filled in... i just need Jan - Dec looped for every dept i have in the cell above.

This macro gives me 2 rows.... i just need 1 row of Jan - Dec looped based on whats above...

TYIA!

 

[Norie]

That was just for testing, I forgot to remove the Offset part.

So just remove Offset(1) and only remove that.

 

[endlessboundless]

Thanks again, but 1 more thing... It can't be a formula that shows the date.. it has to be Jan, Feb, Mar... becuase i'm using these values to reteive from another database...

THanks!

 

[Norie]

It's not a formula.

A formula is used but then it's replaced with the result of the formula.

That's what this does.

.Value = .Value

Even if it is a formula I'm not sure how it would affect the retrieval from another 'database'.

You are getting the values you want, ie Jan, Feb,...,Dec, aren't you?

If you don't want the 12 months of a year you really need to clarify what you do want.