The attached has a mega array formula that really slows down the recalc on this spreadsheet. I would like to use a macro to write the value of the formula to the worksheet "Database" column J.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False)
'This formula below needs a VBA equivalent
' Target.Offset(0, 4).FormulaArray = "=IF(MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8)))=RC[-2],MIN(IF(R7C3:R35000C3=RC[-7],IF(R7C8:R35000C8>0,R7C8:R35000C8))),0)"
End If
If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _
Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then
Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1)
End If
End Sub
The attached has a sumproduct formula that is slow down the calculation of my workbook. (I know there is also an array formula-that's another thread). I'd like to use a macro to fire on the worksheet change so I don't have the heavy recalc burden. The formula is in worksheet "Database" in column H. So far:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range(Range("rReason").Offset(1, 0), Range("rReason"). _ Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then Target.Offset(0, 1) = Application.VLookup(Target, ValList.Range("ReasonLkUp"), 2, False) 'This formula below needs a VBA equivalent ' Target.Offset(0, 2).FormulaR1C1 = "=IF(RC[-1]>0,SUMPRODUCT(-(R7C7:R35000C7<0)*0.5,--(R7C3:R35000C3=RC[-5]))+SUMPRODUCT(--(R7C7:RC7>0),--(R7C3:RC3=RC[-5]),R7C7:RC7),0)" End If If Not Intersect(Target, Range(Range("rSurname").Offset(1, 0), Range("rSurname"). _ Offset(UsedRange.Rows.Count + 1, 0))) Is Nothing Then Target.Offset(0, 1) = ActiveCell.Offset(0, -2) & " " & ActiveCell.Offset(0, -1) End If End Sub
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
Have a macro that copies a formula from each of 100 workbooks to a new workbook. I want to display these formulas as text and want a macro or someway to display these cells as text. I have tried to record a macro that presses the F2 key, the home key and the apostrophe. This works for the one cell but provides the following macro that does not work for anyother cell.
ActiveCell.FormulaR1C1 = _ "'=VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)" Range("B3").Select
I received assistance from NBVC for combining data from two cells. Post http://www.excelforum.com/showthread.php?p=2135144. I need to convert the formula into macro code. I thought I would be able to convert it on my own, but running into some troubles with run time errors. Here is the Excel formula, which is working fine.
I often have to research long sheets of data, which requires filling data in on my research sheet from several different sources. The research sheet has a file # in A and a vendor # in C. The data sources have the file #s & vendor #s in varying columns, but I use a pivot table to end up with file # in A, vendor # in B and the needed data in C.
The length of the research sheet varies from month to month - often 800-900 rows, so you can imagine how tedious it is to find the correct file #/vendor # combination to plug the data in. (Many of the rows on the research sheet will not have a row on the data source sheet.)
I managed to get an array formula in one of the columns on the research sheet that actually DOES work:
But the problem is that it returns #N/A in any row that doesn't have any data. This messes up the sum formula in the farthest right column. So, I tried to incorporate an ISERROR in with the formula, but couldn't get it to work; it left all the cells blank.
In the meantime, I got to thinking that, since there are up to 3 columns requiring data on the research sheet & each column's data comes from a different source, wouldn't it be great to have a macro where I could use a couple of Input Boxes: 1 that would let me click on the column where the data NEEDS to go & 1 that would let me tell it (either by typing the worksheet name or by clicking on the worksheet " PREFERRED " where the data come from)!
So, I spent the entire day yesterday trying to make any of that work & can NOT figure it out! I got the macro to pop up the first input box, & can key in the column letter, but clicking OK doesn't do anything. Yesterday, I had it so I could click OK & the box would go away, but nothing else happened & the second box wouldn't work right.
THEN, I started trying to figure out how to do an array formula in a macro & my head nearly exploded! I have a terrible time trying to understand written descriptions if they're in "tech speak", so it was all greek to me.
Here is all the farther I got with the macro:
Code:
Sub ClearingRsch() ' Jenny 10092011 With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual .DisplayAlerts = False
I want to have a user-defined function where the user sends a range (i.e. "A1:J1") and each cell is viewed as a member of an array. I can do this if the user sends "A1,B1,C1,D1,E1,F1,G1,H1,I1,J1", but this is very cumbersome. I tried the following code:
Code:
Public Function CreateDenom(DenomValues As Range) As Variant Dim tmpArr() As Variant Dim c As Range For Each c In DenomValues tmpArr(c) = c.Value Next c CreateDenom = UBound(tmpArr) End Function
This won't be the end product of course, but it's a starting point if I can get it to work. However, I get an error stating that "A value used in the formula is of the wrong data type."
I am using strongly typed code, but I am also pulling data from worksheets into variants. e.g.
Dim MyArray As Variant
MyArray = [MyNamedRange]
is there any shortcut to convert the variant to a typed array, which doesn't just involve looping throught the variant and using a casting function, e.g.
In the first loop that executes this command TAG_RANGE gets set to $A$1:$A$39
I want to loop through the values in that range and run tests against them. Is there a function that will take the values in the address range an convert them into an array so that I can use something like this:
Code: For Counter = LBound(TAG_RANGE_ARRAY) To UBound(TAG_RANGE_ARRAY)
[run tests]
Next
Or is there someother direct way to do this other than creating a loop that fills the array element by element
{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))} I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.
I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.
{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))} This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
I have a byte array that contains a 4 byte floating point number. How would I convert this byte array to the single typed floating point number it contains?
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example
I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.
Company 2Country B Company 2Country C Company 3Country C Company 3Country C Company 5Country A Company 5Country C Company 5Country C
For example: - For Company 2, a cell containing "Country B, Country C" - For Company 3, a cell containing "Country C" - For Company 5, a cell containing "Country A, Country C"
I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.
The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.
There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.
How to convert the excel formula into its value? I know the easiest way is to copy and use paste special then "value" options but is there a way to make it automated in case the desired output value has resulted.
HTML example: A1 B1 C1 D1 1 2 A1+B1 Yes/No
on the above example, if D1=Yes, the output C1 will become value equals to 3 and the formula will be remove, if D1=No, the formula will still remain.
the following formula to be converted to vb for my userform.
=ROUNDUP(+J16-50000,-3)*0.00364+352
this is part of the vb that i'm working with and i need to change the part after "Then Amount =" I just can't seem to work out where to put the roundup bit.