Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding specific column values in a variable row
I'm not quite sure how to manage this, but I need to find
the value in specific columns in a variable row. I do a search for a specific value in column A, then, when that row is found, I need to store that row value and find the value in specific columns. For instance, I store data in columns H, J, L, and N, but the row varies and can be found by looking for "Excavation Total" in column A. How would I store the row value, and recall it later? How would I build error handling into this, so that if the value weren't found, no error would be returned on either the search, or the recall? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding specific column values in a variable row
Assuming your data is in A3:N100
Insert in, e.g., Cell O1 =MATCH("Excavation Total",A3:A100,0)+ROW(A3:A100)-1 This will store the row number for Excavation Total Array enter into a 4-cell row =IF(ISERROR(VLOOKUP(INDIRECT("a"&O1),A3:N100,{8,10 ,12,14},0)),"",VLOOKUP(INDIRECT("a"&O1),A3:N100,{8 ,10,12,14},0)) In VBA, as long as the procedure is running, iExc will be the row number and arr will contain the values from Columns H, J, K, N, in the folowing: Dim rng As Range, iExc As Long Dim arr(), i As Long, j As Integer ReDim arr(1 To 1, 1 To 4) Set rng = Range("A3:n100") iExc = rng.Find("Excavation Total").Row i = 1 For j = 8 To 14 Step 2 arr(1, i) = Application.VLookup("Excavation Total", rng, j, 0) i = i + 1 Next Chris M. wrote: I'm not quite sure how to manage this, but I need to find the value in specific columns in a variable row. I do a search for a specific value in column A, then, when that row is found, I need to store that row value and find the value in specific columns. For instance, I store data in columns H, J, L, and N, but the row varies and can be found by looking for "Excavation Total" in column A. How would I store the row value, and recall it later? How would I build error handling into this, so that if the value weren't found, no error would be returned on either the search, or the recall? Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding specific column values in a variable row
In the VBA portion of my last post, I forgot about the part "No error
should be returned". The following will include that requirement, though it isn't clear to me what will happen in your application when no error is returned (watch for word wrap): Dim rng As Range, iExc As Long Dim arr(), i As Long, j As Integer ReDim arr(1 To 1, 1 To 4) Set rng = Range("A3:n100") On Error Resume Next iExc = rng.Find("Excavation Total").Row i = 1 For j = 8 To 14 Step 2 If Not IsError(arr(1, i) = Application.VLookup("Excavation Total", rng, j, 0)) Then arr(1, i) = Application.VLookup("Excavation Total", rng, j, 0) Else arr(1, i) = 0 End If i = i + 1 Next Chris M. wrote: I'm not quite sure how to manage this, but I need to find the value in specific columns in a variable row. I do a search for a specific value in column A, then, when that row is found, I need to store that row value and find the value in specific columns. For instance, I store data in columns H, J, L, and N, but the row varies and can be found by looking for "Excavation Total" in column A. How would I store the row value, and recall it later? How would I build error handling into this, so that if the value weren't found, no error would be returned on either the search, or the recall? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a number of values that adds up to a specific value | Excel Discussion (Misc queries) | |||
Finding Number of cells in a column of other sheet having a specific word in them | New Users to Excel | |||
finding specific values in a within a file | New Users to Excel | |||
Finding specific word in column | Excel Worksheet Functions | |||
Finding intersection of row and column (both variable) in table | Excel Worksheet Functions |