Posts by ruppk825

    Hello All -


    Background

    • FactSet is a financial software, similar to Bloomberg. FactSet, again similar to Bloomberg, has a non-native ribbon when the excel addon is installed.
    • The excel shortcut to "Refresh all FactSet formulas" in the workbook, is ALT+S+R+W.

    Macros Purpose

    • When macro is submitted, insert new columns on different tabs.
    • Simulate keyboard submitting of ALT+S+R+W (to update all FactSet formulas in workbook).
    • Macro to wait 20 seconds, in order for FactSet formulas to actually update.
    • Then, proceed with copy/paste information to different tabs etc.

    Macro Issue/Problem

    • At least with how VBA is written now, the application.wait function is being triggered the moment the macro is run, even though in the code, this should occur after application.sendkeys to simulate ALT+S+R+W.

    Thread Purpose

    Has any user seen application.wait go "out of turn" sort of speak? I cannot figure out how to get the macro to pause between simulating ALT+S+R+W, and copy/pasting data to different tabs. Any thoughts?


    VBA (also attached in word doc)

    Moderator Comment: I have added Code Tags to your VBA. Please read the forum rules you agreed to abide by when you joined. These include how to add code tags.


    Hello -


    I'm trying to find Weighted Averages of Column C and D, by letter in Column A.


    As the placement of this file will change, how can I find weighted averages of the rates without having to use SUMPROD in specific ranges, since placement will change month over month.


    Is there a way to utilize SUMPROD with IF statements? I've attached workbook that contains how I'm doing it manually, but am curious if there is a better way.


    File Note: Letters BB and DD are two blocks of information. Assume you cannot sort, and bring them together. They need to be kept separate, which is the crux of my inquiry into this problem.


    Thanks

    Hello -

    In attached workbook, I needed the Monthly Interest and Monthly Serv Fee columns to population with the monthly sum, if the date was the last day of the month; I currently am using SUM with OFFSET, and refencing the number of days in a given month in the OFFSET formula.


    This works nicely, except at the very start of the date range, if the beginning date is not the 1st business day of the month. This is because the OFFSET formulas is then trying to sum above Row 1, which obviously returns #REF!.


    Does anyone have any ideas on how to fix this problem, or rework my approach using SUM with OFFSET here?


    OFFSET formula within SUM formula: OFFSET(B2,(-DAY(EOMONTH(A2,0))+1),0)

    Hello -


    Sheet1 is Summary tab and will remain consistent (column A will not re-order, CUSIP will stay in exact positioning month over month).


    Sheet2 is pulled from 3rd party. CUSIP order is subject to change, starting list in column A is subject to change (Column A list starts in row 2 one month, then starting in row 20 the next month etc.).


    On Sheet1/Summary, I'm looking for a formula that can (1) Identify the row number on Sheet2 the same CUSIP is in, and then (2) COUNTIF the number of cells above zero.

    Obviously the COUNTIF above zero part is easy, but I cannot figure out how to identify the corresponding row number on Sheet2 that belongs to the same CUSIP. Any thoughts?


    To summarize: Sheet2 will be re-ordered every month, so I cannot hard link specific row numbers, and need to identify which row number the CUSIP on Sheet1 is on Sheet2 each month. Then do a COUNTIF above zero etc.

    Hello -


    I've attached workbook Filter by Merged Cells. Raw Data (tab) contains simple table, with column A containing merged cells. Expectation Mockup tab details outcome I am trying to build towards.


    Problem: Use Filter tool in Excel to return all items in columns B to C, for item selected in A (column A contains merged cells).


    Is there an easy way to achieve this? Since I cannot utilize Center Across Selection vertically, how can I return all items in column B to C, for single item in merged cell A?


    Thanks,

    -Ruppk825