Spreading a value over multiple columns + different start colums

[brent1991]

Hi all,

I'm trying to build my own recurring to regarding to the budgets for Google Adwords that I have used. But I'm stuck, maybe you guys can help me.

The structure of my excel is as follwing:

Google Adwords Total budget 1000 running Time 4 Start month 3 January February March etc. etc. etc. <tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

What i'm trying to do is to make a code that can help me with dividing the total budget with the running time and divide proportional in to as much columns as the running time has. One extra addition to this is that it has to start on different months. When I fill in 3 in the start month colum it has to start on march. When it's 5 it has to start on may etc. etc.

I already found a code what almost helped me with this but not exact with what a want:

Option ExplicitSub DivideValue() Dim LR&, LoopCtr1&, LCol&, Ans# Application.ScreenUpdating = False LR& = Cells(Rows.Count, "A").End(xlUp).Row For LoopCtr1& = 1 To LR& Step 1 Ans# = Cells(LoopCtr1&, "A").Value / Cells(LoopCtr1&, "B").Value LCol& = Cells(LoopCtr1&, "B").Value - 1 With Range(Cells(LoopCtr1&, 3), Cells(LoopCtr1&, 3 + LCol&)) .Value = Ans# .NumberFormat = "$#,##0.00" End With Next LoopCtr1& Application.ScreenUpdating = TrueEnd Sub</pre>

<colgroup><col><col></colgroup><tbody>
</tbody>

Can someone help me with this?

Thanks a lot!

 

[MickG]

Perhaps this:-

[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(A)         [/B][/COLOR] [COLOR=RoyalBlue][B]Col(B) [/B][/COLOR]
1.      Google Adwords         
2.      Total budget    1000   
3.      running Time    4      
4.      Start month     3      
5.      Results:-              
6.      March           250    
7.      April           250    
8.      May             250    
9.      June            250

[COLOR=Navy]Sub[/COLOR] MG15Sep03
[COLOR=Navy]Dim[/COLOR] Budget      [COLOR=Navy]As[/COLOR] Double
[COLOR=Navy]Dim[/COLOR] rTime       [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] sMth        [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] n           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] c           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
Range(Range("A6"), Range("A" & Rows.Count).End(xlUp)).Resize(, 2).ClearContents
Budget = Range("B2"): rTime = Range("B3"): sMth = Range("B4")
c = 6
[COLOR=Navy]For[/COLOR] n = c To c + rTime -1
    Cells(n, 2) = Budget / rTime
    Cells(n, 1) = MonthName(sMth)
    sMth = sMth + 1
    [COLOR=Navy]If[/COLOR] sMth = 13 [COLOR=Navy]Then[/COLOR] sMth = 1
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[brent1991]

Thank for your fast reply.

Perhaps this:-

Range("A" & Rows.Count)

Regards Mick

What is the purpuse of this part? Do I need to change this when I'm using other cells? Then stated in the example?

Greets,

Brent

 

[MickG]

That whole line is to clear any old data from column "A" starting row 6, before the rest of the code runs, to add new data.
You can alter the Range to what you like by change the "A6" to new say "F2" and change the "A" to "F".
Code like:-"Cells(n, 2) " refer to "n", the Row and, "2" the column. Column "B" being 2.

 

[brent1991]

Awesome thank you!

The basics are working. I ran into two problems with this code. I'm using this code on c9, c10 and c11. Where the range start on C19. in between those cells there are also codes. But when I run the code it erases all the codes that are inbetween c11 and c19. How is that possible?

And to make the code even more perfect. It would be awesome if there is a possibilty that when the starting month is February equals 2 it starts on C20 instead of C19 and march C21 and so on. Is that possible?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG15Sep58
[COLOR="Navy"]Dim[/COLOR] Budget      [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] rTime       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sMth        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] st          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Lst = Range("C" & Rows.Count).End(xlUp).Row
    [COLOR="Navy"]If[/COLOR] Lst >= 19 [COLOR="Navy"]Then[/COLOR] Range("C19").Resize(Lst - 18, 2).ClearContents
        Budget = Range("D2"): rTime = Range("D3"): sMth = Range("D4")
            c = 19
                st = sMth - 1
[COLOR="Navy"]For[/COLOR] n = c To c + rTime - 1
    Cells(n + st, 3) = MonthName(sMth)
    Cells(n + st, 4) = Budget / rTime
    sMth = sMth + 1
    [COLOR="Navy"]If[/COLOR] sMth = 13 [COLOR="Navy"]Then[/COLOR] sMth = 1
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick