Creating Variables with VBA, maybe?!

[AmandaM]

Hi All,

I have a macro that takes a download from our asset manager and sorts & processes into our report form but I hit a wall with reinvestment's.


I can sort them to a space but I'm not sure how to do sumif or something else to sum the reinvestment's per symbol in each account, e.g., no one has the same symbols or same amount of symbols.

Columns J - M have the reinvestment data, if applicable. I can do an IF to make sure there are reinvestment's, THEN do the sums. I can find the range with a find first blank command in column K... but how do I take that range (L2:M?) and create the data sets in P-Q. I'm doing it manually for now, as if I don't have mutual funds enough already!


Any help is greatly appreciated!

~ Amanda

 

[Michael M]

Have you thought about using a Pivot Table
Insert Tab >>Tables>>PivotTable>>

 

[AmandaM]

Have you thought about using a Pivot Table
Insert Tab >>Tables>>PivotTable>>

I absolutely had not! I'm not sure I could code a macro to do that but I'm willing to try. We use pivot tables very little and I didn't know I could use them with VBA.

 

[Michael M]

You don't need VBA, although you can create them with code !
Have a look here at Debras site on how to create, modify and refresh PT's

https://www.contextures.com/CreatePivotTable.html

And here on how to do it with code.....including sample code AND workbook

https://www.contextures.com/excelpivottableoptionmacros.html

 

[AmandaM]

If I can, I'd rather do it inside the macro. I am trying to make the quarter reporting time as efficient as possible and doing a manual pivot table on a workbook that will not be saved isn't faster than doing the sums manually. I love your idea of putting the pivot table in the macro! I'm having trouble with the range...

If Range("E1").Value <> 0 Then
            Sheets("MyDivs").Select
            Range("L1").Value = "Sym"
            Range("M1").Value = "Amt"
            Range("J1").Select
            Selection.FormulaArray = _
                "=MIN(IF( RC[1]:R[49]C[1]="""",ROW(RC[1]:R[49]C[1])))"
            Range("J1").Value = Range("J1")
            reinA = Range("J1") - 1
            Range("O1").Select
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "MyDivs!R1C12:R" & reinA & "C13", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="MyDivs!R1C15", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion15
    
    Cells(1, 15).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sym")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Amt"), "Sum of Amt", xlSum
    Range("N1").Select
    End If

This line is the error & definitely shows I'm a beginner! I have used variables in cell locations before but this one isn't working.

SourceData:= _
        "MyDivs!R1C12:R" & reinA & "C13"

Any ideas? and THANKS!!

 

[AmandaM]

Wait... I just changed the row to be 50 and it will catch everything, even in our account with the most reinvestment's, and add a "blank" entry. I really need to stop making things harder when trying to make it easier!

Can't thank you enough, Michael M!!!

 

[Michael M]

Is that the entire macro ??
Can you post a sample of generic data, either back here, or upload to a site like dropbox ??
Also, try changing the line to

SourceData:= "MyDivs!L1" & reinA & "C13"

 

[Michael M]

The only problem with selecting an arbitrary row is that one day you may have more rows in your data set and they will be left omitted from the results !!
It would be better to actually find the last row via the code
Maybe like this

Dim lr As Long
lr = Cells(Rows.Count, "J").End(xlUp).Row
If Range("E1").Value <> 0 Then
            Sheets("MyDivs").Select
            Range("L1").Value = "Sym"
            Range("M1").Value = "Amt"
            Range("J1").Select
            [color=red]Selection.FormulaArray = "=MIN(IF(K1:K" & lr & ") ="",ROW(K1:K" & lr & ")))"[/color]
            Range("J1").Value = Range("J1")
            reinA = Range("J1") - 1
            Range("O1").Select
            ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "MyDivs!R1C12:R" & reinA & "C13", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:="MyDivs!R1C15", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion15
    
    Cells(1, 15).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sym")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Amt"), "Sum of Amt", xlSum
    Range("N1").Select
    End If

 

[AmandaM]

Michael M ... Sorry I went awol. Things got crazy!

Hmm, no, it is not the entire macro! My entire macro is 547 lines & also highlights how little I know about VBA. It takes 100+ lines of raw data exported to Excel and refines it into the 3-10 rows I need to copy & insert into the client reports, all written with our phraseology, and then is deleted. It will take my process from 45 minutes down to about 5 minutes. (We all get cookies when awesome things happen and I've earned us several. Haha!) I am not old but trying to find a free photo sharing or file sharing site is bonkers now, mine from college are all dead. Oops!

The only problem with selecting an arbitrary row is that one day you may have more rows in your data set and they will be left omitted from the results !!

I agree finding the blank row is the most efficient way and short cuts usually cause trouble, but in this case, I think it will be okay. We, generally, do not invest heavily in mutual funds and the clients that have them, transferred in to our firm with them. Yes, in theory, we may have a client that transfers in with assets that reinvest over 49 times in a quarter, but it is highly unlikely. Even so, they will be restructuring their portfolio (why people come to us) and manually adding them will be faster for the few quarters those mutual funds last. I will add a check to see if the first blank is over 50, and if so to raise an alarm!

On to the next efficiency project for me. This one stretched my brain like crazy and it was the easy one!

Thanks a million!

~ Amanda

 

[Michael M]

Glad you found a solution...