Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest value
Hello! I have a small problem that I do not know exactly how to solve. I have a macro that that searches a list in Excel for a certain date e.g. 2006-02-14. The date is stored in the array strSlutDatumArray(1). I look using rng(1).Offset(j, 0) (never mind that but it just loops down in a list). I apply this code on many lists. Almost all lists consist of dates based on a 5 day week. However some lists only contain one date per week. Assume that I am searching for the date 2006-01-10. The lists contain only 1 date per week. I want to somehow check so that I choose the date closest to 2006-01-10 in the list. Assume that the list looks like: 2006-01-01 2006-01-08 2006-01-15 Then I want to stop my search at the date closest to 2006-01-10, i.e. 2006-01-08. Can anyone help me with how to do that? Any help is very much appreciated! Thanks a lot in advance! The code I am using for searching the list is: Do Until IsEmpty(rng(1).Offset(j, 0)) = True Or rng(1).Offset(j, 0).text = strSlutDatumArray(1) = True j = j + 1 Loop |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest value
Arne,
I assume you want the closest to the start of the week. if if the date is 2006-01-07 it should be recorded as 2006-01-01. The easiest way to do it is use the vlookup with the false attribute. -- HTHs Martin "Arne Hegefors" wrote: Hello! I have a small problem that I do not know exactly how to solve. I have a macro that that searches a list in Excel for a certain date e.g. 2006-02-14. The date is stored in the array strSlutDatumArray(1). I look using rng(1).Offset(j, 0) (never mind that but it just loops down in a list). I apply this code on many lists. Almost all lists consist of dates based on a 5 day week. However some lists only contain one date per week. Assume that I am searching for the date 2006-01-10. The lists contain only 1 date per week. I want to somehow check so that I choose the date closest to 2006-01-10 in the list. Assume that the list looks like: 2006-01-01 2006-01-08 2006-01-15 Then I want to stop my search at the date closest to 2006-01-10, i.e. 2006-01-08. Can anyone help me with how to do that? Any help is very much appreciated! Thanks a lot in advance! The code I am using for searching the list is: Do Until IsEmpty(rng(1).Offset(j, 0)) = True Or rng(1).Offset(j, 0).text = strSlutDatumArray(1) = True j = j + 1 Loop |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest value
Hello Martin! Thank you very much for your help! vlookup seems like a very
suitable command. However I have some trouble with the syntax, perhaps you can help me. I tried using: vlookup(strStartDatumArray(1); rng(1); rng(1); False) where straStartDatumArray(1) contains the date that I want to find. I want to find it below the cell rng(1) and I want to return that very value (rng(1)). rng(1) is a position in an array that is given like this rng(k)=Worksheets(varWorksheetInfoArray(0)).Cells. Find(varWorksheetInfoArray(k), LookIn:=xlValues) If you have any idea how to make my code work I would be most grateful for your assistance! Thank you very much! "Martin Fishlock" skrev: Arne, I assume you want the closest to the start of the week. if if the date is 2006-01-07 it should be recorded as 2006-01-01. The easiest way to do it is use the vlookup with the false attribute. -- HTHs Martin "Arne Hegefors" wrote: Hello! I have a small problem that I do not know exactly how to solve. I have a macro that that searches a list in Excel for a certain date e.g. 2006-02-14. The date is stored in the array strSlutDatumArray(1). I look using rng(1).Offset(j, 0) (never mind that but it just loops down in a list). I apply this code on many lists. Almost all lists consist of dates based on a 5 day week. However some lists only contain one date per week. Assume that I am searching for the date 2006-01-10. The lists contain only 1 date per week. I want to somehow check so that I choose the date closest to 2006-01-10 in the list. Assume that the list looks like: 2006-01-01 2006-01-08 2006-01-15 Then I want to stop my search at the date closest to 2006-01-10, i.e. 2006-01-08. Can anyone help me with how to do that? Any help is very much appreciated! Thanks a lot in advance! The code I am using for searching the list is: Do Until IsEmpty(rng(1).Offset(j, 0)) = True Or rng(1).Offset(j, 0).text = strSlutDatumArray(1) = True j = j + 1 Loop |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find closest value
Arne:
Try where in my example the lookup range is in the current workbook (where the macro is) and the table is in A1:B6 on Sheet1 and contains: (1,a),(2,b),(3,c),(4,d),(5,e),(6,f) Function newvlookup() Dim ans As Variant Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sheet1") ans = Application.WorksheetFunction.VLookup(2.5, ws.Range("A1:B6"), 2, True) Set ws = Nothing newvlookup = ans End Function ' the answer is b. ' note use true to get the nearest value. -- HTHs Martin "Arne Hegefors" wrote: Hello Martin! Thank you very much for your help! vlookup seems like a very suitable command. However I have some trouble with the syntax, perhaps you can help me. I tried using: vlookup(strStartDatumArray(1); rng(1); rng(1); False) where straStartDatumArray(1) contains the date that I want to find. I want to find it below the cell rng(1) and I want to return that very value (rng(1)). rng(1) is a position in an array that is given like this: rng(k)=Worksheets(varWorksheetInfoArray(0)).Cells. Find(varWorksheetInfoArray(k), LookIn:=xlValues) If you have any idea how to make my code work I would be most grateful for your assistance! Thank you very much! "Martin Fishlock" skrev: Arne, I assume you want the closest to the start of the week. if if the date is 2006-01-07 it should be recorded as 2006-01-01. The easiest way to do it is use the vlookup with the false attribute. -- HTHs Martin "Arne Hegefors" wrote: Hello! I have a small problem that I do not know exactly how to solve. I have a macro that that searches a list in Excel for a certain date e.g. 2006-02-14. The date is stored in the array strSlutDatumArray(1). I look using rng(1).Offset(j, 0) (never mind that but it just loops down in a list). I apply this code on many lists. Almost all lists consist of dates based on a 5 day week. However some lists only contain one date per week. Assume that I am searching for the date 2006-01-10. The lists contain only 1 date per week. I want to somehow check so that I choose the date closest to 2006-01-10 in the list. Assume that the list looks like: 2006-01-01 2006-01-08 2006-01-15 Then I want to stop my search at the date closest to 2006-01-10, i.e. 2006-01-08. Can anyone help me with how to do that? Any help is very much appreciated! Thanks a lot in advance! The code I am using for searching the list is: Do Until IsEmpty(rng(1).Offset(j, 0)) = True Or rng(1).Offset(j, 0).text = strSlutDatumArray(1) = True j = j + 1 Loop |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Find Closest Coordinate Match | Excel Discussion (Misc queries) | |||
Find number closest to 0 | Excel Discussion (Misc queries) | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
Find closest match and copy | Excel Discussion (Misc queries) | |||
How do I find a column entry closest to a particular value | Excel Worksheet Functions |