Snasui Excel Forum

Code: Select all

Dim dayOfMth As Variant, dataAll As Range
Dim rngCur As Range, r As Range
With Workbooks("Daily_rate.xlsx").Worksheets("AVG_SGD")
    Set dataAll = .Range("b3:m33")
End With
With Workbooks("BOT_rate.xlsm").Worksheets("Rate_BOT")
    dayOfMth = VBA.Split(VBA.Mid(.Range("a1"), VBA.InStr(.Range("a1"), "as of") + 6), " ")
    Set rngCur = .Range("b:b").Find("SGD").Offset(0, 3)
End With
For Each r In dataAll
    If r.Parent.Cells(r.Row, "a").Value = "Day " & dayOfMth(0) And _
        Application.Text(r.Parent.Cells(2, r.Column), "[$- ]mmmm") = dayOfMth(1) Then
        r.Value = rngCur.Value
        Exit For
    End If
Next r

Code: Select all

Dim strMth As String, d As Date
Dim rngSource As Range, rngTg As Range
With Workbooks("Source_data.xlsx").Worksheets("Data")
    strMth = Application.Text(.Range("a1").Value, "mmm")
    d = .Range("a1").Value
    Set rngSource = .Range("a3:d3")
End With
With Workbooks("DailyWork.xlsm")
    With .Worksheets(strMth)
        Set rngTg = .Range("a" & .Rows.Count).End(xlUp).Offset(1, 0)
        rngTg.Value = d
        rngTg.Offset(0, 1).Resize(1, 4).Value = rngSource.Value
    End With
End With