vba - vlookup and return row #

[slym34]

Hi all..
I am trying to create a macro and I need it to return the row # based on a vlookup of a date and not 100% sure how to do that.. Hoping to get a little assistance.

I am looking up a date in column A, and need it to return the row #, then I can do the rest of the macro I need. The date I need to look up varies, and is based on the following function I have in a cell elsewhere, but if there is an easier way in vba to write i'm open:

=IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1)

which basically says if it's monday lookup the date from 3 days ago (friday), otherwise look up yesterday's date. So today it returns 6/8/09 and I need to find the row that has 6/8/09 as the value in column A.

Now the dates in column a, are all formulas that say previous row's date + 1, so the only row that has the actual date in it is the very first one, then we just add a day. Not sure if that matters, but figured I'd give all info. Please let me know if further explanation is needed. Thanks!

John

 

[xenou]

Match is your ticket. Its already set up to give you the index of the array, and if the array is the whole column, that's the row number:

=Match(=IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1),A:A,0)

 

[slym34]

How do you properly put that in vba? I tried application.worksheetfunction, but it didn't take? Thx

 

[xenou]

Ah, there's a typo, I should have no second equals sign:

=Match([COLOR="Red"][B]=[/B][/COLOR]IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1),A:A,0)

Should be:

=Match(IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1),A:A,0)

You can record your "formula" by turning on the macro recorder, hitting F2 with the cell selected, then enter (enters the formula again for the recorder). This will record the "formula" of the cell. VBA strictly speaking doesn't use the Today() function and I would probably write it differently. In VBA you just use the keyword Date for the date. This function doesn't really handle cases with errors (no match found). I'm not sure...might crash.

Function RowWithDate()
If Weekday(Date) = 2 Then
    x = WorksheetFunction.Match(Date - 3,Range("A:A"),0)
Else
    x = WorksheetFunction.Match(Date - 1,Range("A:A"),0)
End if
RowWithDate = x
End Function

 

[slym34]

great thanks.. will give that a whirl.. thanks for the help!

 

[seenfresh]

Alexander,

Will the Match function in VB only return the correct row # if the values in the array start from Row(1) as you mention before?

If so, would it be better to use a the Find method or a "For", "Loop" method to locate the true row address?

Perhaps the following code would suffice or shed some light into how to substitute the TODAY function in VB since it is not a valid function in VB language.

Sub FindMatch2()
Dim iRow As Long, iCol
Dim MyRange As Range
Dim myrow As String


If Weekday(Date) = 2 Then
myvalue = Date - 3

Else

myvalue = Date - 1

End If

iRow = ActiveSheet.UsedRange.Rows.Count
iCol = Columns.Count

Set MyRange = ActiveSheet.Range(Cells(1, 1), Cells(iRow, iCol))


With MyRange

Set c = .Find(myvalue, LookIn:=xlValues)
If Not c Is Nothing Then
myrow = c.Row

End If
End With

MsgBox myrow


End Sub

Just a thought.

 

[xenou]

No problems there. Find would work well here too. One thing about VBA is whether to use a sub or a function. The former might actually be part of a larger set of instructions, whereas the latter just returns the row number as an integer. In both cases, we should be sure that either a match is always found or else errors are handled if no match is found.

Good stuff. In practice, I'm not sure we need VBA - Match works well right on the spreadsheet too. Edit: actually, I guess it depends on the larger context of what's going on, really, whether VBA makes it work better.