Macro's owrite eachother

brent1991

New Member
Joined
Sep 15, 2014
Messages
13
Hello,

I'm pretty busy with an advanced excel sheet but I'm stuck with at the moment with one of my last VBA codes.

I'm using the following code:



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


End Sub

and in the next macro i'm using almost the same code but with a different input:

Sub MG15Sep59()
Dim Budget As Double
Dim rTime As Long
Dim sMth As Long
Dim n As Long
Dim c As Long
Dim Lst As Integer
Dim st As Long
Lst = Range("C" & Rows.Count).End(xlUp).Row
If Lst >= 19 Then Range("C19").Resize(Lst - 18, 2).ClearContents
Budget = Range("C15"): rTime = Range("C16"): sMth = Range("C17")
c = 19
st = sMth - 1
For n = c To c + rTime - 1
Cells(n + st, 2) = MonthName(sMth)
Cells(n + st, 3) = BudgetE / rTime
sMth = sMth + 1
If sMth = 13 Then sMth = 1
Next n


End Sub

When I try to get the results it is overwriting each other. The perfect situation is that he adds it to to other results

example:

if there is 0 and you need to add 20 the end result is 20
if there is 20 and you need to add 20 the end result is 40.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

brent1991

New Member
Joined
Sep 15, 2014
Messages
13
thanks fr your quick reply.

What's being overwritten? And what is BudgetE in your second procedure?

The output of the first marco is being overwritten when i start my second macro (because it has to be in the same colums)

BudgetE is extra budget (so addditional budget)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I asked about BudgetE because you haven't declared it or assigned it a value anywhere. Maybe you want:

Code:
Cells(n + st, 3).Value = Cells(n + st, 3).Value + (BudgetE / rTime)

because that's a ratio and you don't know the original constituents.
 

brent1991

New Member
Joined
Sep 15, 2014
Messages
13

ADVERTISEMENT

I asked about BudgetE because you haven't declared it or assigned it a value anywhere. Maybe you want:

Code:
Cells(n + st, 3).Value = Cells(n + st, 3).Value + (BudgetE / rTime)

because that's a ratio and you don't know the original constituents.

Hi sorry. That's a mistake that needs to be Budget.
 

brent1991

New Member
Joined
Sep 15, 2014
Messages
13

ADVERTISEMENT

Hello,

Unfortunately not. This was a bug what I already solved the other day, But pasted the wrong code in this forum. I changed the E but the output is still the same

When i calculate my first macro the result is divided in C19 untill C42. For example two times 200 in cell C20 en C21.

When i use the second macro and the outcome is 200 in cell C30 en C31 it will automattically erase the first result. in C20 en C21.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Did you try the line of code I posted (changing BudgetE to Budget)? It replaces your line.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
How can it be overwriting? It's adding to what's already there:

Rich (BB code):
Cells(n + st, 3).Value = Cells(n + st, 3).Value + (Budget / rTime)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,911
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top