formula array

Hi, This is a problem I have been trying to solve for quite sometime. Every month I have the calculate monthly returns, (current month's price/previous month's price)-1 (or (current month's price/previous month's price)/previous month's price, either work.) I want to make this process...

I am attempting to get a formula that will pull the "Policy Record Number" into the "Related Policy Record Number" Field Upon "Policy #'s Matching. Now the "working table" is going to have duplicate values, which seems to be where my issues are coming in. Right now I can get the first record to...

Hi all, Is it possible to use a function (if statement or an array?) to change the color of a cell without using conditional formatting. I don't want to use conditional formatting because the workbook is already full of formulas and takes a fair amount of time to update. Thank you.

This is the code i currently have; Sub Renewals() ' Renewals Macro ' To add special offers for drivers ' Keyboard Shortcut: Ctrl+Shift+R Dim FullForm As String Dim SpecialOffer1 As String Dim SpecialOffer2 As String Dim SpecialOffer3 As String Dim SpecialOffer4 As String FullForm =...

Hello, I am trying to figure out a way to have a test column that can flag (i.e. YES or No) if a row has duplicate value across two or more columns like the following: <tbody> A B C D E Date 1 Date 2 Date 4 Date 5 Duplicate Dates? 12/23/2015 10:24 12/24/2015 20:22 12/29/2015 10:16...

Hi all, I am trying to correct an error on many spreadsheets (100+) we have located out on a network drive. I had a formula that was incorrectly written and needs to be updated. I've written the code below to be used in a loop macro that opens up each spreadsheet and updates the formulas (if...

Hi everyone, I've been working on a Formula Array code for the Index Sumproduct function to do a multi-criteria lookup. In the Formula Array code, RE, RA, i, RISKC, GENDER, FACE_BAND, PRODUCT, and IA are all variables. I've placed quotes around them accordingly and executed the code, but, for...

Hello Everybody, Currently I am working with a spreadsheet where I have to collect addresses of UK business Directors. Some of the directors have multiple addresses. UK zip code consist of two segment and I have to ignore the address where the first segment of zip code starts with W1 , SW1...

Hi there all. I know how to do a sheet1.cells(x,y).formularray = "=formula" but I'm trying to accomplish something a bit different. Using INDEX MATCH within VBA I can return a simple single column match, such as: sheet1.cells(x,y) =...

This is a table that effectively has the same problem as the one I'm experiencing. <tbody> 100 Blue 1 =PRODUCT(IF(AND($A$1:$A$9>=100,$B$1:$B$9=B1),$C$1:$C$9,1)) 120 Red 2 =PRODUCT(IF(AND($A$1:$A$9>=100,$B$1:$B$9=B2),$C$1:$C$9,1)) 10 Green 3...

Dear All, After a lot of effort (I am a brand new in VBA) I have managed to create a macro to do the following: 1. Copy time vs. Volume data (with variable lengths). A18:B18 2. Calculate deltaT and deltaVolume. (A19:B19). 3. For a constant DR located in cell C6 in the same w/s the following is...

I have the code : For Each cl1 In Table1 cl1.FormulaArray = _ "=MAX(IF(RC[-4]=(IF(VLOOKUP(RC[-1],Correspondance!R[" & i & "]C[-5]:R[" & j & "]C[-4],2,FALSE)=Log_WK!C[-3],Log_WK!C[-2],0)),Log_WK!C[-5],0))" If Not IsError(cl1) Then Result = cl1 Else...

On the code below, I try to put the value of Correspondance1 into the Match formula but it doesn't work. Do you have an idea why it doesn't work? Name1 = Sheets("SLA").Cells(Step1_Row, Step1_Clm).Value Correspondance1 = Application.WorksheetFunction.VLookup(Name1...

Hello everyone, I'm hoping someone can help me reconcile a large amount of information/data while retaining its identifying information. Let me elaborate: I have the following information/table. Each column contains: = SUM(IF(tDLoNo = $B1, IF(LEFT(tDSys, 5) = "IEDMS", IF(tDYrFlag =...

Hi! Good day. I'm having a trouble on how can i start up to transfer formula array to vba codes. Anyone who could help, please give me some advise for starting-up. This is the sample codes that am going to change in vba codes. Formula: {=SUM(IF(tdlono= &B1, IF(LEFT(tdsys, 5) = "IEDMS"...