Posts by ruppk825

Thank you. I'll test today and revert back shortly.

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)

Sub factsetrefresh()


' Insert new column in Archer Archive sheet


Sheets("Archer Archive").Select


Columns("B:B").Select


Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove




' Insert new column in Tyler Archive sheet


Sheets("Tyler Archive").Select


Columns("B:B").Select


Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove




' Simulate pressing Alt+SRW keys


Application.SendKeys "%", True


Application.SendKeys "S", True


Application.SendKeys "R", True


Application.SendKeys "W", True




' Wait for 20 seconds


Application.Wait Now + TimeValue("0:00:20")




' Copy values from Formula sheet column B and paste in Archer Archive sheet column B


Range("B8").Select


Sheets("Formula").Select


Columns("B:B").Select


Selection.Copy


Sheets("Archer Archive").Select


Range("B1").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




' Copy values from Formula sheet column F and paste in Tyler Archive sheet column B


Sheets("Formula").Select


Columns("F:F").Select


Selection.Copy


Sheets("Tyler Archive").Select


Range("B1").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False




' Save active workbook


ActiveWorkbook.Save


End Sub

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.

Thank you Carim, and added Pecoflyer.

Hello -

I provided the current table structure I am trying to reference in Columns A-O, with the table I'm trying to build with a formula in Columns T-Y.

In short, using the table structure established in Columns A-O, how to return the tag in Row #1, for the smallest number associated with each Name/Date. Any thoughts? Workbook is attached.

Hello Pecoflyer -

I've just reviewed your file, and it is exactly what I'm looking for. Thank you very much. I'll need to study your use of SUMPROD to get better at this myself.

I appreciate your time on this.

Best,

-Kyle

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

This thread can be considered closed. I changed tactics and went with ROW() referencing for the SUM/OFFSET ranges. Thanks all.

Additional Notes:

1. Date in Cell A2 changes all other dates below.

2. I'm also using an IF statement in tangent of SUM/OFFSET, to return blank if date is not EOM.

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 bosco_yip -

Formula above is exactly what I'm looking for. Looks like I need to become more familiar with OFFSET. Thank you!

Thanks, I'll take a look.

Hello -

The CUSIP list on Sheet2 won't always be in the same order. This is the problem I'm attempting to solve.

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 @Logit & @Carim - Thank you for your responses, both methodologies work and have their pros and cons. I appreciate you sharing your information and expertise. This issue can be considered closed.

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