VBA Find Function

[smartpat19]

HI All,

Working on my first find function. I am trying to return the range. The formatting is dates and I am trying the find what cell the date is in row 7. Right now I am returning nothing.

Sub Retainage_Macro()

Dim proforma As Workbook
Set proforma = ActiveWorkbook
Dim master As Workbook

Dim Current_Month As Range

Set Sht = Sheets("Project Summary")
Set cfs = Sheets("Cash Flow Template")

Start = cfs.Range("E1").Value

Set Current_Month = cfs.Range("7:7").Find(What:=Start, Lookat:=xlWhole, MatchCase:=False)

MsgBox (Current_Month)

Thank you

 

[Joe4]

Does it return anything if you change the last line to:

MsgBox Current_Month.Address

Does either the dates in cell E1 or row 7 actually have a time component to them?

 

[smartpat19]

Does it return anything if you change the last line to:

MsgBox Current_Month.Address

Does either the dates in cell E1 or row 7 actually have a time component to them?

No Time component. The dates are all populated using the edate formula and are formatted to look like "MMM-YY"

 

[Joe4]

The dates are all populated using the edate formula and are formatted to look like "MMM-YY"

Can you please post the exact formula?

 

[smartpat19]

Can you please post the exact formula?

=EDATE(I7,1)

This is what i'm using to create the schedule of dates in row 7 to allow them to be dynamic.

 

[Norie]

Does this work?

Sub Retainage_Macro()
Dim proforma As Workbook
Dim master As Workbook
Dim sht As Worksheet
Dim cfs As Worksheet
Dim Current_Month As Range
Dim Res As Variant
Dim Start As Date

    Set proforma = ActiveWorkbook
        
    Set sht = Sheets("Project Summary")
    Set cfs = Sheets("Cash Flow Template")
    
    Start = cfs.Range("E1").Value
    
    Res = Application.Match(Start, cfs.Rows(7), 0)
    
    If Not IsError(Res) Then
        Set Current_Month = cfs.Cells(7, Res)
        MsgBox Current_Month.Value
    Else
        MsgBox "Start not found!"
    End If

 

[smartpat19]

Does this work?

Sub Retainage_Macro()
Dim proforma As Workbook
Dim master As Workbook
Dim sht As Worksheet
Dim cfs As Worksheet
Dim Current_Month As Range
Dim Res As Variant
Dim Start As Date

    Set proforma = ActiveWorkbook
       
    Set sht = Sheets("Project Summary")
    Set cfs = Sheets("Cash Flow Template")
   
    Start = cfs.Range("E1").Value
   
    Res = Application.Match(Start, cfs.Rows(7), 0)
   
    If Not IsError(Res) Then
        Set Current_Month = cfs.Cells(7, Res)
        MsgBox Current_Month.Value
    Else
        MsgBox "Start not found!"
    End If

Only receiving "Start not found". Is it the formulas in the cells or formatting? Thank you for your help!

 

[smartpat19]

Update:

Sub Retainage_Macro()

Dim proforma As Workbook
Set proforma = ActiveWorkbook
Dim master As Workbook

Dim Current_Month As Range

Set sht = Sheets("Project Summary")
Set cfs = Sheets("Cash Flow Template")

Start = cfs.Range("E1").Value

Set Current_Month = cfs.Range("7:7").Find(What:=Start, Lookat:=xlWhole, MatchCase:=False)

MsgBox Current_Month.Address

End Sub

When Formatting the cell to Short Date, this macro works. However, the current formatting of "MMM-YY" does not work. How Can i adjust this to work on "MMM-YY" Formatting?

Thank you for your help

 

[Norie]

smartpat19

Excel VBA has always had problems finding dates whether you use the Find method or Application.Match.

In my code you could try wrapping Start in CDbl, of CInt.

    Res = Application.Match(CDbl(Start), cfs.Rows(7), 0)

 

[smartpat19]

smartpat19

Excel VBA has always had problems finding dates whether you use the Find method or Application.Match.

In my code you could try wrapping Start in CDbl, of CInt.

    Res = Application.Match(CDbl(Start), cfs.Rows(7), 0)

Thank you!! This works great! Thank you!