I have an Excel file with several VBA inquiries, they are all working fine
except one.
This particular one, each time I execute it, it's adding the answer to the
one that was there before.

Have been searching and trying several thing for a few hours now, and I
think I will never find out why alone!
If you can please help me?

Thanks,

Sophie



Here's part of the code (it is repeated for 17 different rows and range).

---

Static Sub mPfait()

Range("M60,M57,M54,M51,M48,M45,M42,M39,M36,M33,M30 ,M27,M24,M21,M18,M15,M12").Select
Selection.ClearContents


Dim MyRange2001 As Range
Set MyRange2001 = Range("U12:BZ12")

Dim MyRange3001 As Range
Set MyRange3001 = Range("U2:BZ2")

For Each C In MyRange2001
If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If
End If
Next
End If
Next
Range("M12") = mytotal2001

I think you are going to have to be more specific with your data and what you
are trying to do. I would guess that the If...Then below is not being
executed.

If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If

Try putting a break point at the top of your loop and step thru each line of
code line by line using the F8 button to confirm that everything is executing
properly.
--
Cheers,
Ryan


"Fluffy" wrote:

I have an Excel file with several VBA inquiries, they are all working fine
except one.
This particular one, each time I execute it, it's adding the answer to the
one that was there before.

Have been searching and trying several thing for a few hours now, and I
think I will never find out why alone!
If you can please help me?

Thanks,

Sophie



Here's part of the code (it is repeated for 17 different rows and range).

---

Static Sub mPfait()

Range("M60,M57,M54,M51,M48,M45,M42,M39,M36,M33,M30 ,M27,M24,M21,M18,M15,M12").Select
Selection.ClearContents


Dim MyRange2001 As Range
Set MyRange2001 = Range("U12:BZ12")

Dim MyRange3001 As Range
Set MyRange3001 = Range("U2:BZ2")

For Each C In MyRange2001
If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If
End If
Next
End If
Next
Range("M12") = mytotal2001

When I open the file and run the query, everything is executed correctly, but
if I run it again (before saving and closing the file), it will add the new
total to the one that was in the cell before running the query.

In short, I'm trying to make the add everything number that is in a green
cell in the row, and for which the heading column is pink.
(I know, it's not easy to understand when you're not looking at the file -
if there's a way I can attached the file to the post, I would be glad to do
so.)

Thanks for the help!



"RyanH" wrote:

I think you are going to have to be more specific with your data and what you
are trying to do. I would guess that the If...Then below is not being
executed.

If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If

Try putting a break point at the top of your loop and step thru each line of
code line by line using the F8 button to confirm that everything is executing
properly.
--
Cheers,
Ryan


"Fluffy" wrote:

I have an Excel file with several VBA inquiries, they are all working fine
except one.
This particular one, each time I execute it, it's adding the answer to the
one that was there before.

Have been searching and trying several thing for a few hours now, and I
think I will never find out why alone!
If you can please help me?

Thanks,

Sophie



Here's part of the code (it is repeated for 17 different rows and range).

---

Static Sub mPfait()

Range("M60,M57,M54,M51,M48,M45,M42,M39,M36,M33,M30 ,M27,M24,M21,M18,M15,M12").Select
Selection.ClearContents


Dim MyRange2001 As Range
Set MyRange2001 = Range("U12:BZ12")

Dim MyRange3001 As Range
Set MyRange3001 = Range("U2:BZ2")

For Each C In MyRange2001
If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If
End If
Next
End If
Next
Range("M12") = mytotal2001

1. Add the following statement
mytotal2001 = 0
before the line
For Each C In MyRange2001

I guess you are using mytotal2001 somewhere else and not initializing it
before the use in the code fragment you have shown.

2. I modified your code as below and ran it multiple times. Got the same
answer every time.
'-------------------
Sub test()
Dim MyRange2001 As Range
Set MyRange2001 = Range("A1:J1")

Dim MyRange3001 As Range
Set MyRange3001 = Range("A2:J2")

i = 0
j = 0
For Each C In MyRange2001
'MsgBox C
'If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
j = j + 1
'If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
'End If
End If
i = i + 1
Next
'End If
Next
Range("A3") = mytotal2001
MsgBox "i: " & i & vbCrLf & "j: " & j
End Sub
'-------------------
"Fluffy" wrote:

When I open the file and run the query, everything is executed correctly, but
if I run it again (before saving and closing the file), it will add the new
total to the one that was in the cell before running the query.

In short, I'm trying to make the add everything number that is in a green
cell in the row, and for which the heading column is pink.
(I know, it's not easy to understand when you're not looking at the file -
if there's a way I can attached the file to the post, I would be glad to do
so.)

Thanks for the help!



"RyanH" wrote:

I think you are going to have to be more specific with your data and what you
are trying to do. I would guess that the If...Then below is not being
executed.

If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If

Try putting a break point at the top of your loop and step thru each line of
code line by line using the F8 button to confirm that everything is executing
properly.
--
Cheers,
Ryan


"Fluffy" wrote:

I have an Excel file with several VBA inquiries, they are all working fine
except one.
This particular one, each time I execute it, it's adding the answer to the
one that was there before.

Have been searching and trying several thing for a few hours now, and I
think I will never find out why alone!
If you can please help me?

Thanks,

Sophie



Here's part of the code (it is repeated for 17 different rows and range).

---

Static Sub mPfait()

Range("M60,M57,M54,M51,M48,M45,M42,M39,M36,M33,M30 ,M27,M24,M21,M18,M15,M12").Select
Selection.ClearContents


Dim MyRange2001 As Range
Set MyRange2001 = Range("U12:BZ12")

Dim MyRange3001 As Range
Set MyRange3001 = Range("U2:BZ2")

For Each C In MyRange2001
If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If
End If
Next
End If
Next
Range("M12") = mytotal2001

I also thought I was usign mytotal2001 somewhere else, but I've check
everything and I am not, but anyway, adding your statement did the trick.

Thank you very much, you're my new hero.



"Sheeloo" wrote:

1. Add the following statement
mytotal2001 = 0
before the line
For Each C In MyRange2001

I guess you are using mytotal2001 somewhere else and not initializing it
before the use in the code fragment you have shown.

2. I modified your code as below and ran it multiple times. Got the same
answer every time.
'-------------------
Sub test()
Dim MyRange2001 As Range
Set MyRange2001 = Range("A1:J1")

Dim MyRange3001 As Range
Set MyRange3001 = Range("A2:J2")

i = 0
j = 0
For Each C In MyRange2001
'MsgBox C
'If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
j = j + 1
'If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
'End If
End If
i = i + 1
Next
'End If
Next
Range("A3") = mytotal2001
MsgBox "i: " & i & vbCrLf & "j: " & j
End Sub
'-------------------
"Fluffy" wrote:

When I open the file and run the query, everything is executed correctly, but
if I run it again (before saving and closing the file), it will add the new
total to the one that was in the cell before running the query.

In short, I'm trying to make the add everything number that is in a green
cell in the row, and for which the heading column is pink.
(I know, it's not easy to understand when you're not looking at the file -
if there's a way I can attached the file to the post, I would be glad to do
so.)

Thanks for the help!



"RyanH" wrote:

I think you are going to have to be more specific with your data and what you
are trying to do. I would guess that the If...Then below is not being
executed.

If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If

Try putting a break point at the top of your loop and step thru each line of
code line by line using the F8 button to confirm that everything is executing
properly.
--
Cheers,
Ryan


"Fluffy" wrote:

I have an Excel file with several VBA inquiries, they are all working fine
except one.
This particular one, each time I execute it, it's adding the answer to the
one that was there before.

Have been searching and trying several thing for a few hours now, and I
think I will never find out why alone!
If you can please help me?

Thanks,

Sophie



Here's part of the code (it is repeated for 17 different rows and range).

---

Static Sub mPfait()

Range("M60,M57,M54,M51,M48,M45,M42,M39,M36,M33,M30 ,M27,M24,M21,M18,M15,M12").Select
Selection.ClearContents


Dim MyRange2001 As Range
Set MyRange2001 = Range("U12:BZ12")

Dim MyRange3001 As Range
Set MyRange3001 = Range("U2:BZ2")

For Each C In MyRange2001
If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If
End If
Next
End If
Next
Range("M12") = mytotal2001

Thanks for the appreication. Glad I could help.

It is always a good idea to initialize variable close to their use. This way
you don't have to worry about other parts of the code if you use any variable.

"Fluffy" wrote:

I also thought I was usign mytotal2001 somewhere else, but I've check
everything and I am not, but anyway, adding your statement did the trick.

Thank you very much, you're my new hero.



"Sheeloo" wrote:

1. Add the following statement
mytotal2001 = 0
before the line
For Each C In MyRange2001

I guess you are using mytotal2001 somewhere else and not initializing it
before the use in the code fragment you have shown.

2. I modified your code as below and ran it multiple times. Got the same
answer every time.
'-------------------
Sub test()
Dim MyRange2001 As Range
Set MyRange2001 = Range("A1:J1")

Dim MyRange3001 As Range
Set MyRange3001 = Range("A2:J2")

i = 0
j = 0
For Each C In MyRange2001
'MsgBox C
'If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
j = j + 1
'If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
'End If
End If
i = i + 1
Next
'End If
Next
Range("A3") = mytotal2001
MsgBox "i: " & i & vbCrLf & "j: " & j
End Sub
'-------------------
"Fluffy" wrote:

When I open the file and run the query, everything is executed correctly, but
if I run it again (before saving and closing the file), it will add the new
total to the one that was in the cell before running the query.

In short, I'm trying to make the add everything number that is in a green
cell in the row, and for which the heading column is pink.
(I know, it's not easy to understand when you're not looking at the file -
if there's a way I can attached the file to the post, I would be glad to do
so.)

Thanks for the help!



"RyanH" wrote:

I think you are going to have to be more specific with your data and what you
are trying to do. I would guess that the If...Then below is not being
executed.

If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If

Try putting a break point at the top of your loop and step thru each line of
code line by line using the F8 button to confirm that everything is executing
properly.
--
Cheers,
Ryan


"Fluffy" wrote:

I have an Excel file with several VBA inquiries, they are all working fine
except one.
This particular one, each time I execute it, it's adding the answer to the
one that was there before.

Have been searching and trying several thing for a few hours now, and I
think I will never find out why alone!
If you can please help me?

Thanks,

Sophie



Here's part of the code (it is repeated for 17 different rows and range).

---

Static Sub mPfait()

Range("M60,M57,M54,M51,M48,M45,M42,M39,M36,M33,M30 ,M27,M24,M21,M18,M15,M12").Select
Selection.ClearContents


Dim MyRange2001 As Range
Set MyRange2001 = Range("U12:BZ12")

Dim MyRange3001 As Range
Set MyRange3001 = Range("U2:BZ2")

For Each C In MyRange2001
If C.Interior.ColorIndex = 50 Then
For Each D In MyRange3001
If D.Column = C.Column Then
If D.Interior.ColorIndex = 38 Then
mytotal2001 = mytotal2001 + C
End If
End If
Next
End If
Next
Range("M12") = mytotal2001