Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I move data from a row range to a column range | Excel Discussion (Misc queries) | |||
Using a range of dates to add data in a different column? | Excel Worksheet Functions | |||
Range of data within a column | Excel Worksheet Functions | |||
making the range a set of data within a column | Excel Worksheet Functions | |||
Serching in document | Excel Programming |