Macro - Multiple Variable

[MotaA]

Hey Guys

I need to create a macro that can count different columns from a huge Data Base and then save each result in a specific cell in another file.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have more than 70 different columns to be counted and so, I do not want to create more than 70 variables (like Result1, Result2, Result3…Result70 etc…)<o></o>
My question is: How to create a unique variable like (Result(N), where (N), that could:<o></o>
ð Count the first column based in a multiple criteria, save the result in a cell, clean-up the value from the variable and then count another column?<o></o>
<o> </o>
See following part of my initial code:<o></o>
<o> </o>
<o> </o>
Dim Results1 As Long, Results2 As Long……Results70 as long<o></o>
Dim Linha2 As Integer<o></o>
Dim I As Integer<o></o>
<o> </o>
Linha2 = 1<o></o>
<o> </o>
Do While Sheets(“PPV”).Cells(Linha2, "C") <> ""<o></o>
Linha2 = Linha2 + 1<o></o>
Loop<o></o>
<o> </o>
Linha2 = Linha2 - 1<o></o>
For I = 1 To Linha2<o></o>
<o> </o>
If Sheets(“PPV”).Cells(I, "F").Value < 0 Then<o></o>
If UCase(Cells(I, "AP").Value = "Cardinal Health") Then<o></o>
Cells(I, "F").Value = Cells(I, "F").Value<o></o>
<o></o>
Results1 = Results1 + Cells(I, "F").Value<o></o>
Sheets(“DATA”).Range("E6") = Results1<o></o>
End If<o></o>
End If<o></o>
<o> </o>
If Sheets(“PPV”). Cells(I, "F").Value < 0 Then<o></o>
If UCase(Cells(I, "AP").Value = "GEA") Then<o></o>
Cells(I, "F").Value = Cells(I, "F").Value<o></o>
Results2 = Results2 + Cells(I, "F").Value<o></o>
Sheets(“DATA”).Range("E7") = Results2<o></o>
End If<o></o>
End If<o></o>
<o> </o>
<o> </o>
Thanks<o></o>

 

[Scott Huish]

Re: Macro - Multiple Varaible

I'm not sure what you are trying to do exactly, but what you are describing is an array:

Sub Test()
Dim Result (0 to 3) as Integer
Result(0)=12
Result(1)=15
Result(2)=20
Msgbox Result(1)
End Sub

 

[MotaA]

Re: Macro - Multiple Varaible

Simple but efficient.
It's exactly what I was trying to do.

Thanks!

 

[wigi]

Re: Macro - Multiple Varaible

I bet you can improve a lot on that code and similar code you are writing, but there's not enough information to elaborate on that.

What is this supposed to be?

If UCase(Cells(I, "AP").Value = "Cardinal Health") Then

Don't you mean:

If UCase(Cells(I, "AP").Value) = "CARDINAL HEALTH" Then

(the brackets are also wrong I think).

What does this do?

Cells(I, "F").Value = Cells(I, "F").Value

Does it overwrite a formula?

Wigi