How do I make a column array variable within a function? I've done nested vlookups to get a column number, but the function I'm using doesn't use a "number", it uses a column aray. '=AVERAGEIFS(L:L,K:K,">="&B$2,K:K,"<="&B$3). The "L:L" needs to be moved according to Market, (or column choosen).
View 2 RepliesI have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
I have the following line in VBA which works.
[Code]....
I have a variable for the column adress of the second part of the formula so it looks like this:
[Code] ....
But it says I don't have the correct syntax.
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
For example (thanks to Shg for this code): ....
Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below
=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)
I would like to write a formula to return a value from a column that relates to an array within which my lookup value exists. See the tables below:
ID
V1
V2
V3
105
27
3
149
[Code] .......
The tables above are on different sheets within a workbook. I would like to write a formula that returns the "ID" numbers from column "A" in the first table, based on values in columns B-D in that first table, into column "C" in the second table. For example, in this case, the ID number that corresponds with the value "12491" would be "109", since 12491 corresponds with 109 in the first table.
FYI, the "Value" numbers in the second table are calculated based on their rank (high-low) within the matrix in the first table.
I tried the INDEX-MATCH function, but it doesn't seem to work if I'm trying to find a value within a 2-D array- it only works if I'm looking in a single column.
I'm trying unsuccessfully to write a function that looks for a column in a pivot table and returns the entire column as a text string. The function should take a pivot table and a text string as an argument. The text string is the name of one of the columns. The function needs to return the entire column (excluding the header and footer rows) as an array.
In the example I pasted below, the function should take the pivot table (PivotTable1) and a color, say "Red", as arguments, and then return the values in the Red column as a 48x1 single vector array, in the order shown, starting with 43 and ending with 1. I can only post a text version of the spreadsheet; I was not able to post the actual spreadsheet.
Average of Cost
Color
Date
Hour
[Code]....
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of values from a 10 x 10 grid that does not have values in every cell.
(I would also like to eliminate any blank cells from the final column.)
I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.
I am trying to use the ARRAY.JOIN function as recommended by Domenic in previous posts. I need to output a single column of all the values from a 10 x 10 array that does not have values in every cell. (I would also like to eliminate any blank cells from the final column.) I can only get ARRAY.JOIN to output the first value of whatever range I select, not a column of values.
how to properly execute that function. One earlier comment was that I was not specifying criteria. I'm not sure from the description and help section of "morefunc" as to what those criteria specs are, other than specifying the whole array. I am attaching a test file with the 10x10 grid & some missing values in A1:J10. Cell L1 has the ARRAY.JOIN function with the entire grid specified, but the output is only the first cell of the grid.
I'm only starting to get to grips with arrays. I have what I consider to be a lot of data that I need to 'cut' into separate workbooks. I have written some code that does this by simply looping through each line, 250k+, checking against a variable and copying the row into a separate sheet. This took longer than it would have doing it manually. It was suggested to me that I use arrays to speed up the process. I have managed to store the test data into an array but am struggling to find a way to loop through and pull out an entire 'row' from the array based on a variable. I have looked for 2 days in various places to find some way to loop through the data held in the array, but to no avail.
That code will appear here from about 8am GMT tomorrow. I know that once I've cracked this I'm on the road to some very significant time saving and comprehensive report writing.
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 hv following code , which is working fine.
Sub M_0_1_project_20080107_ask_how_to_make_last_row_as_variable()
Range("F1") = "month"
Range([e2], [e6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=month(RC[-1])"
Range("g1") = "yr"
Range([f2], [f6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=YEAR(RC[-2])"
End Sub
i like to change to code as following , but it cannot work , hope some one can help me.
Private Sub Worksheet_BeforeDoubleClick _
lastrow = 6
Range("F1") = "month"
Range([e2], [e[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=month(RC[-1])" _
Range("g1") = "yr"
Range([f2], [f[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-2])" _
End Sub
As i have many variable with same function, tried of keep change last row number.
So I'm trying to use the SEARCH function in excel to search for a particular text in a cell. I am trying to create a macro for it.
View 9 Replies View RelatedI am trying to make a spreadsheet that will have a weekly schedule and will recognize and make some sort of message box or something when a person's name is used twice in the same range. For example, if Smith is in B2 and in B4 then message box. I'm not sure how to do this.
My thought was to make a variable range to check if anything in B2:B7 is equal to say, B3. But the range B2:B7 would have to exclude the cell that is compared, B3. Otherwise it is always true.
I have 2 worksheets - "Results" and "Historical". I am trying to record a macro that copies data from "Results" and then pastes into Historical. The data I want to copy will always be in "Results" A1:A10. When I want to update the macro (which will be weekly), I want it to copy A1:A10 and then paste into "Historical". Simple enough. Here is the problem:
Since this will be updated weekly, the first time I update I will want "Results" data copied into "Historical" A1:A10. The next time I update, I want the data copied into "Historical" B1:B10; then C1:C10, etc. Essentially I want the keep a running total of all of my prior data.
So I need the macro to be able to say something like - paste date into the first available column. If there is data in A1:A10, then paste into B, then C, D. How do I make the Macro variable?
I would like have a input box in which a user enter a number and then I would like to be able to use that number in other worksheets within the same workbook. How do I declare the variable for use with other sheets. I know I would first use
Dim intRows As Interger
But now how do I make it global.
I am not sure if I am using the correct terminology when I see "array". What I want to do in VBA is what I know how to do in non-vba excel functions (cntrl + shift + enter). For example, I have a range of cells that need to meet one condition ( ""):
Code:
If Sheet8.Range("C" & rw) "" Or Sheet8.Range("D" & rw) "" Or Sheet8.Range("E" & rw) ""...etc Then
How do I bundle this in one package. What you see below is obviously incorrect, but I am trying to accomplish this with proper syntax.
Code:
If Or(Sheet8.Range("C" & rw & ":Z" & rw) "")
I am trying to make a calculation using variable text.
My outcome cell is D3 and my two variable cells are C3 and F3. In C3 I want to be able to insert a staff members name, of which we have 6 different members of staff. In F3 i need to insert the week number which runs 1 through 6. The outcome is the individuals place on the week roster which needs to be a number between 1-6 inclusive. SO basically if F3=1 & D3=J.SMITH then I need D3 to equal 1 keepng in mind that if F3 & D3 have 6 possible variables.
I have made a very basic spredsheet which using an assumption table makes a particular column of values which works out the revenue. Then I just drag the box down to the number of rows I want it to go down.
However, I want to be able to enter a number in a CELL away from the tale eg. 50 and have the number of rows in the column go down to 50
How to make + symbol variable in the below formula?
This formula is in G25 and symbol is in F25 has + & - symbols
=(NOW()-E$592)+E614
I have the following checkboxes objects, in my form:
CheckBox1, CheckBox2, CheckBox3, ...
I want to know what is the value of each one in code.
I would like to run a calculation based on selected active range in sheet. I tried several methods and cannot make it work.
View 2 Replies View RelatedMy company has complicated time sheets because we have several tasks that are billed differently to different people. Once a month we have to sit down and compile everything from several forms and so forth. I have created a worksheet that pulls all the numbers together so that they can simply be copied and pasted into our reports. The hope was to simply copy this worksheet into a time sheet workbook and it will pull out all of the correct numbers. Although all the time sheet workbooks are set up the same way, whenever I copy the file into another time sheet workbook the program keeps its references from the workbook it was in. Make sense? is there a formula that I can insert into an array to tell it to pull the information from the worksheet with the same name, but in the active workbook?
here is one of the equations I am working with:
{=TRANSPOSE('1st week'!A10:L48)}
so it would look something like this maybe
{=TRANSPOSE('[active workbook]1st week'!A10:L48)} but this doesn't work of course.
Code:
Application.ScreenUpdating = False
Dim c As String
Dim d As String
[Code]....
this works perfectly but I have found one problem, I want excel to find on the existing arrays "0,00" and "0", only full match and not only partial like for example:
-154.09, which triggers "0" for the second array and 120,00, that triggers "0,00" how to I change to only find full match with the existing arrays?
I have a large table of data sorted by date and I need to get the last value for a given variable. in certain instances using LOOKUP(9.999999E+307,sheet1!A:A) works but not when I'm trying to get values for past dates
Her is an example table:
Date
Month Value
Variable
1/2/14
1
11
[Code]...
If i need to find the last value for the month of February, in this case 514, what combinations of formulas should I use? I feel like this should be easier than I making it out to be.
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
View 14 Replies View RelatedOn sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:
=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))
Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.
I've successfully copied the array of equations using the VBA that Pjoaquin enlightened me with from my last thread. The outcome was Sheet2!A2:O2 being successfully populated with the equations from my first sheet... but here comes the problem: I'm looking to autofill A2:O2 down to the last record in Column P. But the number of records in this table is varable.
View 2 Replies View RelatedI am trying to change the variable value in my following code through array.
What I want is that both the statements
Debug.Print testarray(0) & "........" & testarray(1) & "......." & testarray(2)
Debug.Print custname & "........" & custaccount & "......." & worthcredit & vbCrLf & vbCrLf
should deliver me the same values i.e changedname 123456 and true
for testarray(0),testarray(1),testarray(2) i am getting the values but I am not able to change the variable values for custname ,custaccount and worthcredit, although I am accessing the same elements.
Here is full ....