I am having troubles with using two methods together. I know how to find the address of the first or last occurance of a MAX in a range. I also know how to create a MAXIF function using criteria to determine what to consider for the max calculation. I do not know how to return the address of the result from the MAXIF.
I am using Office 2003 on WinXP.
Ultimately I want to take the address of the first result of a MAXIF and use it to do a lookup for a corresponding value. I'm not sure if I will be able to do this with a combination of functions, or if I will need a macro.
Where each named range is a column of 20 continuous cells.
This actually sits within a larger formula {=IF(MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))=0,EditAsset_DatePurchased+(EditAsset_MaintenanceCycle*30.4167),MAX(IF(EditAsset_M aintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))+(EditAsset_MaintenanceCycle*30.4167))}
Now I need to achieve a similar thing, but in a master list of all the data which necessitates a different layout so the data can't be in 2 columns, and can't be continuous. So:
Max the dates in cell BE7, BI7, BM7, BQ7, BU7, BY7, CC7, CG7, CK7, CO7, CS7, CW7, DA7, DE7, DI7, DM7, DQ7, DU7, DY7, EC7...
...IF the value of the corresponding cell two columns to the left of each is "Scheduled Maintenance",
I tried creating 2 named ranges (one for the cells in each bullet point above) and using that in the array formula, but that didn't work and as this formula needs to go down 8,000 rows it would require 16,000 named ranges which isn't really practical.
Ideally I think I would perform this calculation in VBA and just output the answer to the relevant cell. [The VBA code will be triggered via a Worksheet_Change event when one of the date cells, or the 'maintenance cycle' cell elsewhere is changed].
I think this could be done by taking the values from the cells above into two arrays, and then 'max if' those arrays within VB somehow - but I don't know how to create the arrays or then perform 'max if' within VBA.
Alternatively a formula would suffice - I could insert this formula into the output cell via the VBA Worksheet_Change event when an input cell (date or maintenance cycle) is changed, and then have the same VBA code copy this cell and paste the value - can't have up to 8,000 array formulas live in my sheet for performance reasons! But I don't think this will be easily possible via a formula and VBA would be cleaner.
construct a formula that finds a value in a range , then returns the cell address of that value.
Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.
I have tried the address & match function but gives me an N/A error.
when I use the range.find function to find a certain value in a column.. i want to return the address of the cell.. and save it in a range variable. how would I do that?
so this is what i have now.. but Rng does not return as a range.. it returns 69... when i use ctrl + G and type ?rng
set rng = .range(A:A).find(what:=69, After:=.Cells(1, 1), Searchorder:=xlByRows, searchdirection:=xlPrevious) ok so say it picks up..... cell A69... how do i get it to save range A69
I have a range of cells (say A1:D8). I wanna find the address (say $B$2) of the minimum value in that range.
This command does it but it only works on a column. it does not accept a range spanning more than one column. =CELL("address",INDEX(B2:B7,MATCH(MIN(B2:B7),B2:B7,0))) this will tell me the address of the minimum value.. but in a column... I need a range of many columns.
So I tried converting things to a Table.. but it never worked. basically the MATCH command is my problem. It only accepts single-column-ed ranges.
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
I would like to write a formula that looks at the last month with a percent value greater than zero, then return the percent value in a specific cell. In the example below I want to display 85.0% in the cell. It should update each time data is entered for the following month.
Here is the source:
MonthPercentage October 96.0% November 2.0% December 70.0% January 85.0% February 0.0% March 0.0% April 0.0% May 0.0% June 0.0% July 0.0% August 0.0% September 0.0%
I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell:
= SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))
Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types - without success. Can anybody help me?
Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)
and wish to use formulae similar to MAXIF and MINIF to find the last date and the first date of Paypal transactions (for example) if I have just the word "Paypal" in cell D1
I have a excel worksheet that contains hostnames in column a. I want to run the dos cmd "tracert "hostname from column a", then, import the IP address from the cmd results in column b of my worksheet.
Sub GT()Dim cell As rangeDim BeforeNull As StringDim FirstNull As StringDim InitialData As StringDim B As VariantFor Each cell In range("A1:A8")Alpha:InitialData = cellFirstNull = InStr(1, InitialData, " ", vbTextCompare)If FirstNull = 1 ThenInitialData = Right(InitialData, Len(InitialData) - 1)cell = InitialDataGoTo AlphaEnd IfIf FirstNull "" And FirstNull "0" Then BeforeNull = Trim(Left(InitialData, FirstNull - 1))If FirstNull = "" Or FirstNull = "0" Then BeforeNull = InitialDatacell = BeforeNullNextEnd Sub
Now after the final Next, I need the macro to select the last cell changed. Is there a way to do this?
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.
I have another named range that actually refers to a range. Call it AllData.
Column A Row2 56 Row3 44 Row4 65
AllData is a named range that refers to the range A2:A65536 AllData_UsedRange refers to A2:A4 by way of this formula. =OFFSET(AllData,0,0,COUNTA(AllData))
How to I obtain an address of AllData_UsedRange in VBA code?
These do not work... ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
I want a function that allows me to enter a range and then returns the address of the cell with the minimum value. It is the Address I am interested in, not the value.
Does anyone know the formula that produces the address (e.g. $A$1) of a cell within a table? I found the formula that gives the address of a single cell within a single row (or the address of a single cell within a single column). But I need the formula that gives the address of a cell where the row intersects the column. If I know the column header name and the row header name, how do I find the cell address for the two dimensional intersection?
I know that I can return the value of a defined name range, the address, and even the value of the define name, but if you are given a range address, how do you find its corresponding defined name in code?
I have a data table, called Table1 in Sheet1. In a simple form it is
Customer ID Comment
A asdf
B jkl;
So in VBA, I need to figure a way to give me the cell address of the matching cell in Comments. So say Customer ID starts at A1 ... that means the Comment for Customer A is B2.
I'm still getting to grips with the Find Command. Basically, I have the below code that looks at a line in a sheet(ws_Site) based on the Line ID Number that is in column A and then Finds that that LineID_Value in a different sheet(ws_main) and copies a value from column I on that line across. Problem is, if the cell I is blank it copies it across and essentially clears the cell if there was data in it.
How can I adjust the below code to only set ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value if LineID_Value is not blank?
For Each LineID In ws_Site.Range("A7:A" & SiteRows) ' Loop through all the cells in range
LineID_Value = LineID.Offset(0, 8).Value
'Dim c With ws_main Set c = .Range("A7:A3000").Find(LineID, LookIn:=xlValues) 'If Not c Is Nothing Then
I am using VLOOKUP to find the size of a cam to be installed in a tablet press, based on the product code it will be running.
The array has two columns: (W) Product Code, (X) Cam Size.
Array: W4:X437
The user selects the Product Code from a drop-down list in cell E5.
The resulting Cam Size is displayed in cell E7. The VLOOKUP works fine.
=IFERROR(VLOOKUP(E5,W4:X437,2,FALSE),"")
Occasionally, the cam size has to be updated. The user would then select a new cam size from a drop-down list in cell E9.
I have a "Update Cam Size" command button.
What I need to happen is for the value in E9 to replace the value in the array that is displayed in E7. Obviously, I have to know the location of the cell in the array, but I can't figure that part out. I've tried ADDRESS and MATCH functions, but it comes back with "#N/A" Value not available error.
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
When I use VLOOKUP formula within a VBA routine ex: For LoopCounter = 1 to LastRow With Worksheets("0MATERIAL") .Cells(RowCounter, 5).FormulaR1C1 = "VLOOKUP(RC[-2],'0MAT_UNIT'!R1C1:R65520C12,4,FALSE)" End With RowCounter = RowCounter + 1 Next LoopCounter
How can I find out what row or cell address that the VLOOKUP found the data? The above code works perfectly but the problem is I need data from that same row in columns 4-11. I replicated the .Cells line above and made each one a VLOOKUP (for column 4, 5, 6, etc...) but this kills the performance. I have around 32,000 rows in sheet 0MATERIAL and around 50,000 rows within sheet 0MAT_UNIT
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
