Adjust an existing VBA code

[Panoos64]

Hi all, i would like to modify the below VBA code so that to work to any spreadsheet. However i pasted it as a patch in another VBA code and a message appears as follow:

Compile error
Dublicate declaration in current scope

I write off the first 2 lines and it runs without appear the expected result.

Many thanks in advance



Dim Nams As Variant, n As Variant, c As Long, oSum(1 To 5) As Double
Dim Rng As Range, Dn As Range, Dic As Object
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Nams = Array("TOTAL OPERATING COST", "OTHER DIRECT EXPENSES", "OTH. ADMIN. EXPENSES", "ADMINISTRATION EXPENSES", ACCOUNTS", "OTHER INCOME", "OTHER EXPENSES", "TAXES")
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each n In Nams: Dic = Empty: Next
For Each Dn In Rng
If Dic.Exists(Dn.Value) Then
oSum(1) = oSum(1) + Dn.Offset(, 1).Value
oSum(2) = oSum(2) + Dn.Offset(, 3).Value
oSum(3) = oSum(3) + Dn.Offset(, 5).Value
oSum(4) = oSum(4) + Dn.Offset(, 7).Value
oSum(5) = oSum(5) + Dn.Offset(, 9).Value
c = c + 1
End If
If c = Dic.Count Then
With Dn.Offset(1)
.Resize(2).EntireRow.Insert
.Offset(-1).Value = "TOTAL EXPENSES"
.Offset(-1, 1).Value = oSum(1)
.Offset(-1, 3).Value = oSum(2)
.Offset(-1, 5).Value = oSum(3)
.Offset(-1, 7).Value = oSum(4)
.Offset(-1, 9).Value = oSum(5)
End With
Exit Sub
End If
Next Dn
End Sub

 

[steve the fish]

This will cause that error, ie 'a' duplicated:

Sub duplicate()
Dim a
Dim a
End Sub

Your code as posted doesnt give that error.

 

[Panoos64]

ok steve, the code it works if i run it as i sent it above, but in case that to copying and past it as a patch in another code so that that to extend the vba code, it doesn't work. Thanks for your support.

 

[Fluff]

Supplying the part of the code that works, is not very helpful.

It would help us to help you, if your supplied the complete code. That way we have a chance of figuring what is wrong

 

[Panoos64]

Hi Fluf, is a huge code, around 30-40 commands and i wanted to extend it with above code. Just if is possible, explain me how to add some new commands to an existing code. Thanks for your support

 

[Fluff]

The simplest way is just to put

Call macro1

at the end of the existing code.Where macro1 is the name of the sub you have posted above.

 

[Panoos64]

No Fluf, it dosen't work with above method. Let me check how to find the solution. Thanks for your interesting to resolve my project.