.range = Concatenate with dates

[gheyman]

using VBA I need to make the value in C1 equal some text and two dates from another tab (E7 and F7)

"Supplier Quotes where RFQ Date is between" Date from E7 "and" Date from F7

Sheets("Search").Range("E7")
Sheets("Search").Range("F7")

Sub RunSearch()
' Refresh Search results
'
    Sheets("Search_Results").Select
     With Sheets("Search_Results")
        .Range("A4").Value = "Refreshed: " & Now
' THIS IS WHERE I NEED HELP
       .Range("C1").Value = ??????

    End With
    
    Range("A6").Select
    Selection.ListObject.TableObject.Refresh
    

End Sub

Thanks

 

[Joe4]

Try:

.Range("C1").Value = "Supplier Quotes where RFQ Date is between " & _
        Format(Sheets("Search").Range("E7"), "mm/dd/yyyy") & " and " & _
        Format(Sheets("Search").Range("F7"), "mm/dd/yyyy")

Change the date formats to your liking.

 

[Rick Rothstein]

Try that line of code this way...

.Range("C1").Value = "Supplier Quotes where RFQ Date is between" & Format(Sheets("Search").Range("E7").Value, "mm/dd/yyyy") & "and" & Format(Sheets("Search").Range("F7").Value, "mm/dd/yyyy")