How do i make a variable to a parameter

Would a formula solution work?

D1: =IF(COUNTIF($C$1:$C1,$C1)=1,SUMPRODUCT(--(ISNUMBER(MATCH($A$1:$A$13,$E$1:$E$2,0))),--($C$1:$C$13=$C1),$B$1:$B$13),"")
_________________

no needs to be done in vb

VB it is, here we go:

Becomes:

With the help of:

when trying to run the following code i get a error saying sub or function not defined ( I have marked the line look below)

How do i fix it?


Sub parse()

Dim TotShares(1 To 1000) ' Make this large enough
Dim Tickers(1 To 1000)

SRow = 1
FinalRow = Range("A65536").End(xlUp).Row


MyNumber = 0
For Each NewTicker In Range("C1:C" & Range("C65536").End(xlUp).Row)
ThisTicker = NewTicker.Value

MyNumber = MyNumber + 1
************Complains with the line below
Ticker(MyNumber) = ThisTicker
TotShares(MyNumber) = 0

For i = 1 To FinalRow

Set Action = Cells(i, 1)


If Cells(i, 3) = ThisTicker And (Action = "B" Or Action = "BL") Then
TotShares(MyNumber) = TotShares(MyNumber) + Cells(i, 2)
End If
Next i
Next NewTicker

End Sub

Replace this --

Ticker(MyNumber) = ThisTicker

with this --

Tickers(MyNumber) = ThisTicker

This is a duplicate. I've locked the original:

http://www.mrexcel.com/board2/viewtopic.php?t=87236

Nice to see you're no longer using a double-loop, while the insistence on looping is a point of curiosity as you have very powerful tools available to you that are being ignored.

at the same time i don't understand your insistence on not using loops...perhaps u are better at this than i am which is most likely the case so explain your reluctance about loops.

This is fair enough. Please might be nice, I am trying to help you at no cost to you...

In any case, if your data set is large, the loop will perform much more slowly, probably will on a small data set as well. Especially when using cell properties. E.g.,

http://www.mrexcel.com/board2/viewtopic.php?t=84948&start=4

Did you solve the double loop (slower yet)?

Did the sql not yield what you wanted?

i did not solve the double loop...side tracked with other projects...

I would very much like it if i moved away from cell properties and moved into more of what you speak of...but you know when u know how to do something one way and when you need to pull a script together in a short time you just stick with what you know...sort of like saying i'll put in comments after writing or declaring variables when it is all done...

and it also comes down to time...unfortunately only still 24 hrs in a day.

Hopefully I can find time to learn what it is you speak of.

Thanks for the help.

Lino