Spreading a value over multiple columns + different start colums

brent1991

New Member
Joined
Sep 15, 2014
Messages
13
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 budget1000
running Time4
Start month3
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!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Perhaps this:-
Code:
[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

Code:
[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
 
Last edited:

brent1991

New Member
Joined
Sep 15, 2014
Messages
13
Thank for your fast reply.

Perhaps this:-

Code:
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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

New Member
Joined
Sep 15, 2014
Messages
13
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,959
Messages
5,599,056
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top