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

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

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

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