Serching column/range for specified data
 

Hi,

I'm pretty much a newbie when it comes to excel, so please bear wit
me.

What I want is to be able to search down a column or in a range for th
first occurance of a specific piece of data, then use another cell fro
the same row in a calculation.

For example - Column A contains a date, column B contains misc. data
column C contains the data that needs to be searched. So I want to b
able to search down column C until the first occurance of the numbe
"1" is found, then use the date in column A of the same row t
calculate the number of days between that and a date specifie
elsewhere in the workbook.

Is this possible? If yes, how?

I have a lot more I would like to know but I think this will do fo
starters.

TIA to all

--
Message posted from http://www.ExcelForum.com

Serching column/range for specified data
 

your task would be much easier if the column which you need to be
searched in the first column in your table. You can then use the
VLOOKUP function. Lets say your data lie in the range A1:C5 as follows

1 abc 1-jan-04
2 bcd 2-jan-04
3 hjd 3-jan-04
2 hkn 4-jan-04
4 jkm 5-jan-04

lookup value is 2 which lies in cell A10

formula to be used
=VLOOKUP(A10,A1:C5,3)

A10 has the value to be lookedup
A1:C5 is the range or your table
3 is the column index nos i.e. the column from where you want your
value for the lookup value

Mangesh


---
Message posted from http://www.ExcelForum.com/

Serching column/range for specified data
 

For example - Column A contains a date, column B contains misc. data &
column C contains the data that needs to be searched. So I want to be
able to search down column C until the first occurance of the number
"1" is found, then use the date in column A of the same row to
calculate the number of days between that and a date specified
elsewhere in the workbook.

This code does the basics of what you want. You'd have to edit it for
specifics:
I assume no header row, and a date value in every row of the A column.
I use the .Cells(row, column) function, so we use column 1 for A, column 2
for B, etc...


dim r as long
dim lastRow as long
dim foundRow as long
dim searchFor as string

searchFor = "1" ' value to look for
foundRow = 0

with Sheets("Sheet1") 'or whatever sheet name you use
lastRow = .Range("A:A").Count

for r = 1 to lastRow
if .Cells( r, 3).Value = searchFor then
foundRow = r
exit for
endif
next

if foundRow = 0 then 'value not found
exit sub
endif

WorkOutDate( .Cells( foundRow, 1).Value) 'or whatever function you want
to perform on the date held in .Cells( foundRow, 1).Value

end with


Iain King

Serching column/range for specified data
 

=Index(A:A,Match(1,C:C,0),1)

for code

Dim rng as Range, res as Variant
Dim dDate as Date
set rng = Range("A:A")
res = Application.Match(1,Range("C:C"),0)
if not iserror(res) then
dDate = rng(res).Value
else
msgbox "1 not found"
End if

--
Regards,
Tom Ogilvy


"Mark K " wrote in message
...
Hi,

I'm pretty much a newbie when it comes to excel, so please bear with
me.

What I want is to be able to search down a column or in a range for the
first occurance of a specific piece of data, then use another cell from
the same row in a calculation.

For example - Column A contains a date, column B contains misc. data &
column C contains the data that needs to be searched. So I want to be
able to search down column C until the first occurance of the number
"1" is found, then use the date in column A of the same row to
calculate the number of days between that and a date specified
elsewhere in the workbook.

Is this possible? If yes, how?

I have a lot more I would like to know but I think this will do for
starters.

TIA to all.


---
Message posted from http://www.ExcelForum.com/

Serching column/range for specified data
 

Thanks for the input. While I don't really understand it all, at least
it gives me somewhere to start.

Thanks again.


---
Message posted from http://www.ExcelForum.com/