I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?
I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry
mnger = "TA"
if analyst = 1
anlyst = 1
if month = Jan
mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
Range("display cell").Value = Range(rangename).Value
The problem is, I am getting an object error every time I try to refer to a range this way.
If I type:
Range("display cell").Value = Range("JanTA1").Value, it works
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _
strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B
Expiry Date________Redeemed by
15/08/2014
15/02/2014
15/08/2014________John
15/02/2010________Marc
15/02/2011________Bob
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:....
Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
I would like to use a excel formula to resolve this problem.
Column C is the anwser I pretend.
In the same cell it will return the name of the correct range.
Ex: Between 200 and 300 there are numbers involved that are included in Range 1 and Range 2 (see second table)
Cell
A
B
C
1
From
To
Result: (How to return this value?)
2
200
300
Range1, Range2
3
301
400
N/A
4
401
500
N/A
Table to Check Value to Return:
Cell
A
B
C
1
120
280
Range 1
2
281
300
Range 2
3
600
650
Range 3
I need to format a spread sheet to return a value from a range of 4 values (A,B,C,D) by testing a data range against a table that establishes the true value for each separate possible value within the data range.
In other words, ItemXXX will have a value that is contained within a table on a separate worksheet within the book. I need to test the value of ItemXXX against the range of values on Sheet B and return the corresponding value from sheet B to the main spread sheet.
=If($Bxxx=SheetB!(D18:D35);A;IF(Bxxx=SheetB!(D36:44;B;If($Bxxx=SheetB!(D(45:D56);C;(IF(Bxxx=SheetB!(D57:D65);D;"RFI"))))
But I find nothing in the help pages that tells me it is possible to test a range of cells agains a single criteria in an IF statement.
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
headers:PERAPDSH Answers RA RA
RA PD PD
RA SHSH
RA RA
I have a sheet with the following data
i can have anywhere from 3000- 10000 rows of data
I need a formula that can give me the anwers column.
i tried lookups and nested IF statements.......
is there a better solution to this without looping have a macro do a loop?
I have a lot of data to process on an ongoing project. I need to be able to perform a calculation on the last two numbers on a row including the date the reading was collect. Through the year additional data will be collected and I want the spreadsheet to calculate a value based the last two entered data points for each piece of equipment. how to get the last number in an array but I do not know how to get the second to last number. I included an example.
View 5 Replies View RelatedI have 10 columns and 10 rows of data. Not all of the 100 cells contain data.
I am looking for a formula that will return the value contained in the right most non zero or blank cell
Eg Row 1 =
1, 2, 4, 2, blank, 4, 3, blank, 0, blank
In Column 11 I want a formula that returns 3. i.e. it reads accross until it finds a value and returns it.
The target data is not neccesarily unique, the highest or the lowest.
I have a row with many numbers. I would like to write a formula that will identify the the first occurence of a non-zero number. I just need to identify what this first non-zero number is.
View 4 Replies View RelatedI have a range of cells and I want to return the first value, penultimate value and last value. I have a formula to return the first value and another for the final value but I'm struggling to get a formula to work that will return the penultimate value.
If, for example, the range is P4:AD4 then not every cell will necessarily have a value as some cells are blank.
Q1: In the range for the lookup I'm performing, the column with the possible match is the 2nd column. I've tested my function with this range, but it fails (range is $A$x:$K$x, with possible matches residing in column B). If I change the range so the 1st column has the desired data ($B$x:$K$x), it works. Can I modify the function to search using the 2nd column so I don't have to rearrange the columns in my worksheet?
Q2: Can I return an entire rold of data? If yes, how? I only know how to return one cell.
i have a range of cells ( address ) (d16:d21). what i need i16 to do is return the second from last row that has data in it so for example if data is
27 arcacia ave
hillybrook
hills
mountains
london
w4 3rd
it would return the answer london. however if the data was
27 arcacia ave
hillybrook
hills
mountains
In worksheet1 I have a set of data. Column A is a value, column B and C are dates. In worksheet2 I have dates in Row 1 going out horizontially. I would like to create a fomula that returns the value in column A in worksheet1, if the date is between column B and C. I couldn't get my array to work. it's been a couple of years since I've wrote any indepth formulas.
View 7 Replies View RelatedI keep tabs on my league's standings and I would love to make a formula to keep track of record over the last 10 games. However, the part that's stumping me is that the right end of the range consistently increases (the last cell with a value will go from J4 to K4 to L4 and so on). How to create a formula that will auto-update (something like 10 cells from the last entered value) as I add more information to the worksheet? The two values in the cells are "W" and "L". I only need a formula to return the "W" values within the last 10 filled cells from the last filled cell of the row.
View 3 Replies View RelatedI have a list of names in sheet1 column A and 3 more list of names on sheet2, sheet3 and sheet3. What I'm trying to do is find a match for each name on sheet1 column A on the other sheets and depending on which sheet it was found, I need a specific value returned to sheet1 column B.
For example, if A1 was found on sheet2, then on B1 I would need to return the value "Morning" but if it was found on sheet3, then I need the value "Afternoon" and if found on sheet3 I need the value "evening" returned to B1 and then the same for every other name on the list found on sheet1 column A.
What I tried was this.
This goes in Sheet1 B1 (then I would drag it down to search for the rest)
=IF(AND(MATCH(A1,'sheet2'!$E:$E,0)),"Morning",IF(AND(MATCH(A1,'sheet3'!$E:$E,0)),
"Afternoon", IF(AND(MATCH(A1,'sheet4'!$E:$E,0)),"Evening","Un-assigned"))
Now the problem with this is that it only works if there is a match on sheet2 but if there is no match, it just returns "N/A" and it does not move on to sheet3 and/or sheet4.
I also tried with vLOOKUP instead of MATCH and the same thing happens.
I also tried this and the only thing I get is "Un-Assigned"
=IF(AND(A1='sheet2'!E:E),"Morning",IF(AND(A1='sheet3'!E:E),"Afternoon",IF(AND(A1='sheet4'!E:E),"Evening","Un-Assigned")))
01112
2
02111
03111
04111
05111
06114
2
3
4
07111
08112
2
09111
10112
2
Clomun-1: Numbers written serially from 1-10 based on some logic.
Column-2: Start from non empty column, increment if next column is empty and increment until a non-empty column found.
Column-3: The row where max number need to be written.
column-4: Expected max number to be written from column-2.
I can managed to write max number on the same row where column have the max number in the range. As you can see column-2 dynamic size of range need to be used by the max function. I can solve the problem using macro but trying solve using cell formula.
1
1
1
2
2
2
1
1
1
3
1
1
1
[Code] .....
Clomun-1: Numbers written serially from 1-10 based on some logic.
Column-2: Start from non empty column, increment if next column is empty and increment until a non-empty column found.
Column-3: The row where max number need to be written.
column-4: Expected max number to be written from column-2.
I can managed to write max number on the same row where column have the max number in the range. As you can see column-2 dynamic size of range need to be used by the max function.
I have an array of numbers, how do I return these numbers as a range.
I want to be able to call something like sum(arrayRange) from a sheet.
i have a combobox that uses a named range as the rowsource. does anyone know how I can limit the return list to only the third column of the range? My partial code below is to be used to pick the number of days associated with a particular project type. The range"Project_Durationlbx" has three columns in it. THe first is the Project type and the third is the number of days. can the rowsource of a combobox be written to hold a formula?
If Me.cbxProjType.Value "Non Sourcing Activity" Then
Me.cbxNSAWorkDays.RowSource = "Project_Durationlbx"
I have seen a few entries where the rowsource is made dynamic but was unable to adapt the reasoning myself.
Return from a range of cells the value that is unique within the range. Note: assume there is no finite set of unique values that I can lookup within the range. attached workbook. Need direction on a cell formula in B7 that isolates any unique value.
View 5 Replies View RelatedI want to write a procedure similar to MMULT. I find that
Public Function aa_ok(arg1 As Range)
works to get the data.
I have not been able to figure how to get the result back to the spreadsheet.
Documentation sez that args are passed by ref, but I am unable to modify args and see the change in the spreadsheet (thank god).
I find that statements like
aa_ok = Worksheets("Sheet1").Range("A1").Value
work just fine but statements like
Worksheets("Sheet1").Range("A5").Value = Worksheets("Sheet1").Range("A1").Value
don't, even though I copied that statement from the help system.
I tried
Function calculate(ByRef X As Range, ByRef Y As Range)
calculate = X * Y
End Function
which I got here - and it doesn't work on my system.
I get a block of cells with repeats of #VALUE! where I expect my result to be displayed.
I have Excel 2000 9.0.2720 and VBA Retail 6.0.8714
I have looked at security and it is set low.
I need a formula/tip that will return the last non-blank item in a list or array. I am updating a worksheet each month with that month's metric and want the end user not to have to scan to the right to see the current value, but see it at the front of the worksheet. For example:
A1 is labeled "Current Value"
B1 : M1 are labeled "JAN" : "DEC"
B2 : G2 have values 45, 54, 32, 65, 14 & 23 respectively
H2 : M2 are blank
I need a formula in A2 that returns the last non-blank item in B2 : M2. In this instance, 23. When I enter a value into H2 (the "JUL" column), I want A2 to reflect that new number. =IF won't do, due to the 7 nest limit. I've seen this solution before but despite arduous searches, I can't find it.
I am trying to display part of a worksheets new sheet based on some criteria. I have a worksheet with a list. I have a date field in the list. I need to breakup the into several sheets based on the dates. I want all of items in the list with a February date put onto a tab titled February and all items on the list with a March date on a tab titled March. I haven't been able to find a funtion or code that will do this. The new tabs don't have to be created through automation. i can create those manually. i am looking for a formula or possible code to put on the new sheets that will look up and return specific rows from the main worksheet with the master list. i attached a sample of my worksheet. I need to search by Column O titled Date of Next Review.
View 4 Replies View RelatedI have exported data that will possibly have duplicate row entries of any number. I sort the data ascending by 'Agent Name' and then by 'Login Time'. Since vlookup returns the very first occurence grabbing the first in time is easy, but I'm having trouble grabbing the last logout time.
This array formula was my latest attempt:
=VLOOKUP(B9,INDEX(A2:A6,MAX(IF(A2:A6=B9,E2:E6)),0):$E$6,5,0)
I am having trouble using vlookup and offset to arrive at a solution. I have a row a 5 formula based items. For example:
C 1 2 3 4
1 C 2 3 4
1 2 C 3 4
1 2 3 C 4
1 2 3 4 C
I have been using the match function to find the location of C, but I want to output the items in the same row as C, in the order they appear if C was removed.
I have a simple list of alphanumeric asset numbers,
AKR DC 0001
AKR DC 0002
AKR DC 0004
And so on, I want to run a piece of code from a user form which will return the highest value in the range plus one on the numeric portion. I can find the largest value easily with a sort and last used cell value and I could drag out the numeric portion, increment and crowbar it back in but I'm sure there is a more elegant way.
All asset codes follow the same naming convention and all reside in column a of the active worksheet.