Vlookup - Month Instead of Day

[Guest]

Hi,

This NG provided me with the formula below to find a number on Col B based
on the date(s) on Column A.

Please edit the formula to find the number on Col B based on the "month" on
Col A.

Thank you.

Sub FindNumber()
Dim rngData As Range
Dim strPWord As String

Set rngData = Worksheets("FindNumber").Range("A:B")
strPWord = CStr(Application.VLookup(CLng(Date), rngData, 2, 0))
MsgBox strPWord

End Sub

 

[Guest]

Hi Danny,

I really don't understand what you are trying to achieve. You say that you
want to find a number in column B based on a month in Column A. In your
sample code you have rngData set to both column A and Column B so this is
confusing me.

Do you want to loop through all the data in column A and find matches to
months in column B. If so, where do you want to place the results or do you
simply want them in the MsgBox as per the code sample so that you keep
clicking OK and it finds the next one.

Perhaps you can post a sample of the data in both Column A and Column B and
then what you expect the output to be.

Regards,

OssieMac

 

[Guest]

Hi,

The formula below find the number for today, 1234.

DATE Number
10/23/07 1234
10/24/07 2345
10/25/07 3456

I'd like to macro to be edited if Col A shows Month and year. I tried to
edit the formula by:

strPWord = CStr(Application.VLookup(CLng(Format(Date, "mmm yyyy"), rngData,
2, 0))

but it did not work.

Month Number
Oct 2007 4567
Nov 2007 5678
Dec 2007 6789

Thank you.

 

[Guest]

Hi Danny,

Two options depending on the type of data in the column with the month and
year.

I was not able to use vlookup when the month and year are dates formatted as
mmm yyyy.

You can nest the formatting formula for strToFind into the other code. I
just find it easier it easier to understand what is being done if they are
kept separate.

First option:-

Sub FindNumber()
'This works provided that the month and year in column A
'are in text format (Not a date formatted as "mmm yyyy")

Dim rngData As Range
Dim strToFind As String
Dim strPWord As String

Set rngData = Worksheets("FindNumber").Range("A:B")

strToFind = Format(Date, "mmm yyyy")

strPWord = WorksheetFunction.VLookup(strToFind, rngData, 2, 0)

MsgBox strPWord

End Sub


Second option:-

Sub FindNumber_2()
'This works with the month and year in column A as
'dates formatted as "mmm yyyy"
'Also works with month and year as text.

Dim rngData As Range
Dim strToFind As String
Dim strPWord As String
Dim foundCell As Range

Set rngData = Worksheets("FindNumber").Columns("A")

strToFind = Format(Date, "mmm yyyy")

Set foundCell = rngData.Find(What:=strToFind, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

strPWord = foundCell.Offset(0, 1)

MsgBox strPWord

End Sub

Regards,

OssieMac

 

[Guest]

Hi again Danny,

I forgot to say that you need to handle the error if not found. This applies
to both macros.

Regards,

OssieMac