Creating Variables with VBA, maybe?!

AmandaM

New Member
Joined
Dec 19, 2018
Messages
11
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
Have you thought about using a Pivot Table
Insert Tab >>Tables>>PivotTable>>
 

AmandaM

New Member
Joined
Dec 19, 2018
Messages
11
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.
 

AmandaM

New Member
Joined
Dec 19, 2018
Messages
11
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...

Code:
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.

Code:
SourceData:= _
        "MyDivs!R1C12:R" & reinA & "C13"
Any ideas? and THANKS!!
 

AmandaM

New Member
Joined
Dec 19, 2018
Messages
11
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! :rolleyes:

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
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

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,199
Office Version
2013
Platform
Windows
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

Code:
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

New Member
Joined
Dec 19, 2018
Messages
11
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! :LOL:

Thanks a million!

~ Amanda
 

Forum statistics

Threads
1,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top