Finding specific column values in a variable row

  • Thread starter Thread starter Chris M.
  • Start date Start date
C

Chris M.

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.
 
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
 
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
 
Back
Top