How do i make a variable to a parameter

[Lino]

I am looping through a file with a for each statement and then in the for loop statement i am comparing the value of the cell with tickers to the for each loop ticker value and if the same i assign the value of the shares for the ticker to the totshares variable with the parameter of the Ticker in question.

then later on down the code when i reference TotShare(ThisTicker) I will be able to see how many shares for each specific ticker.

How do I set this up so TotShares will take a parameter.

Thanks Lino

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

For i = 1 To FinalRow
If Cells(i, 3) = ThisTicker And (Action = "B" Or Action = "BL") Then
TotShares(ThisTicker) = TotShares + Cells(i, 2)
End If
Next i

 

[NateO]

You're welcome. I could show you how to loop, but I'm pretty certain that the sql is where you want to be, while perhaps not a starting point in VBA technique.

Does it do what you want it to do?

 

[Lino]

I want to eliminate the column headings: Ticker, Value, Action and replace them with column numbers because the order of my data is always the same.

how would i do this?

Thanks,

Lino

 

[Lino]

Nate,

It fails at ".Open , , 3, 3"

the error says: no value given for one or more required parameters.

Can you please fix although i am familiar with sql this is method is new for me to troubleshoot.

thanks,

Lino

 

[Lino]

suggestions anyone?

thanks,

lino

 

[NateO]

You'll want headers. Just add a row, slam in some headers, do your business, then delete the dummy row. E.g.,

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> SumTicks()
<SPAN style="color:darkblue">Dim</SPAN> cn <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, rs <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> clcMde <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>

clcMde = Application.Calculation
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
Application.Calculation = xlCalculationManual
<SPAN style="color:darkblue">With</SPAN> Sheets(1)
    .Rows(1).Insert
    .Range("a1:c1").Value = Array("Action", "Value", "Ticker")
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>

Sheets(2).[a2:iv65536].ClearContents

<SPAN style="color:darkblue">Set</SPAN> cn = CreateObject("ADODB.Connection")

cn.<SPAN style="color:darkblue">Open</SPAN> "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;"
  
<SPAN style="color:darkblue">Set</SPAN> rs = CreateObject("ADODB.Recordset")
<SPAN style="color:darkblue">With</SPAN> rs
    <SPAN style="color:darkblue">Set</SPAN> .ActiveConnection = cn
    .Source = "Select [Ticker], Sum([value]) From [Sheet1$a1:iv65536] " & _
        "Where [Action] = ""B"" or [Action] =""BL"" Group By [Ticker]"
    .<SPAN style="color:darkblue">Open</SPAN> , , 3, 3
    Sheets(2).[a1].CopyFromRecordset rs
    .<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
cn.<SPAN style="color:darkblue">Close</SPAN>
<SPAN style="color:darkblue">Set</SPAN> rs = Nothing: <SPAN style="color:darkblue">Set</SPAN> cn = <SPAN style="color:darkblue">Nothing</SPAN>

Sheets(1).Rows(1).Delete

Application.Calculation = clcMde
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

 

[Lino]

Nate,

it fails at the .Open...code

see my previous reply.

Lino

 

[NateO]

I did. Something's wrong with your SQL, this is where is bonks out. Three things to verify:

1) Correct Header Names
2) Correct Sheet Names
3) That the workbook is saved.

Are you tri-factor compliant?

 

[Greg Truby]

Hi Nate,

I'm a little reticent about tacking on a post here - don't want to distract from your helping Lino here but I haven't used SQL like this in Excel VBA and I can see where this might be pretty handy so a couple of questions are occuring to me as I read your posts:

1. Why are you going out of your way to not clear out row 1 on Sheet(2)? Why not Sheets(2).Cells.ClearContents? (And why .ClearContents instead of .Clear?)

2. Would the "ADODB.Connection" object / "Provider=Microsoft.Jet.OLEDB.4.0..." reference require the user to have Access installed?

{Edit}Just saw a question #3...
From [Sheet1$a1:iv65536] - one should use a dollar sign here, not an exclamation point? {EndEdit}

Thanks for any insights and please forgive the intrusion.

 

[Lino]

trifactor compliant?

what's that mean?

i have a trimode cell phone?

is there a library that i need to add under tools references in the editor?

thanks,

Lino

 

[NateO]

Hi Nate,

I'm a little reticent about tacking on a post here - don't want to distract from your helping Lino here but I haven't used SQL like this in Excel VBA and I can see where this might be pretty handy so a couple of questions are occuring to me as I read your posts:

No worries Greg.

1. Why are you going out of your way to not clear out row 1 on Sheet(2)? Why not Sheets(2).Cells.ClearContents? (And why .ClearContents instead of .Clear?)

A) Indeed, that's a typo. Unless you have headers in row 1 and want to copy to a2, you might as well take out row 1 as well.
B) Either way. ClearContents, I don't know, preserve pre existing formatting? Less overhead? Don't see the need for clear, while one could use it.

2. Would the "ADODB.Connection" object / "Provider=Microsoft.Jet.OLEDB.4.0..." reference require the user to have Access installed?

Don't think so. It would require an installed Jet OLE DB Provider, 4.0 might be nice.

Check it out:

VBE -> Help ->About Microsoft Visual Basic... -> System Info... Drill down on Applications -> Microsoft Office Environment -> OLE DB Providers. Make sure there's an item named: Microsoft Jet 4.0 OLE DB Provider. You will also want to make sure that you have msado__.dll on properly installed your system, Microsoft ActiveX Data Objects __ Library. However neither should be confused with the Microsoft Access ___ Object Library.

Lino, as I've set this up to work on the late bind, if you have the OLE DB provider installed and the correct DLL installed properly on your PC, you need not explicitly set a reference to it.

trifactor compliant?

what's that mean?

Are my three checkpoints valid?

Hope this helps.