Macro - Multiple Variable

MotaA

New Member
Joined
May 10, 2010
Messages
2
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>
My question is: How to create a unique variable like (Result(N), where (N), that could:<o:p></o:p>
ð 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:p></o:p>
<o:p> </o:p>
See following part of my initial code:<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Dim Results1 As Long, Results2 As Long……Results70 as long<o:p></o:p>
Dim Linha2 As Integer<o:p></o:p>
Dim I As Integer<o:p></o:p>
<o:p> </o:p>
Linha2 = 1<o:p></o:p>
<o:p> </o:p>
Do While Sheets(“PPV”).Cells(Linha2, "C") <> ""<o:p></o:p>
Linha2 = Linha2 + 1<o:p></o:p>
Loop<o:p></o:p>
<o:p> </o:p>
Linha2 = Linha2 - 1<o:p></o:p>
For I = 1 To Linha2<o:p></o:p>
<o:p> </o:p>
If Sheets(“PPV”).Cells(I, "F").Value < 0 Then<o:p></o:p>
If UCase(Cells(I, "AP").Value = "Cardinal Health") Then<o:p></o:p>
Cells(I, "F").Value = Cells(I, "F").Value<o:p></o:p>
<o:p></o:p>
Results1 = Results1 + Cells(I, "F").Value<o:p></o:p>
Sheets(“DATA”).Range("E6") = Results1<o:p></o:p>
End If<o:p></o:p>
End If<o:p></o:p>
<o:p> </o:p>
If Sheets(“PPV”). Cells(I, "F").Value < 0 Then<o:p></o:p>
If UCase(Cells(I, "AP").Value = "GEA") Then<o:p></o:p>
Cells(I, "F").Value = Cells(I, "F").Value<o:p></o:p>
Results2 = Results2 + Cells(I, "F").Value<o:p></o:p>
Sheets(“DATA”).Range("E7") = Results2<o:p></o:p>
End If<o:p></o:p>
End If<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Thanks<o:p></o:p>
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,880
Re: Macro - Multiple Varaible

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

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

MotaA

New Member
Joined
May 10, 2010
Messages
2
Re: Macro - Multiple Varaible

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

Thanks!
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,944
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?

Rich (BB code):
If UCase(Cells(I, "AP").Value = "Cardinal Health") Then
Don't you mean:

Rich (BB code):
If UCase(Cells(I, "AP").Value) = "CARDINAL HEALTH" Then
(the brackets are also wrong I think).

What does this do?

Rich (BB code):
Cells(I, "F").Value = Cells(I, "F").Value
Does it overwrite a formula?

Wigi
 

Forum statistics

Threads
1,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top