Excel VBA copying cell into another worksheet but using vlookup to find correct row based on date

[philhendry]

Hey, Firstly I have look all over the place and couldn't find the answer to this.

I am a bit of a novice when it comes to VBA with Excel

i currently have a "TEST" sheet
and a "TEST2" Sheet

on the TEST sheet i have a button which has current code

Private Sub CommandButton2_Click()
    Dim DDate As Date
    Dim MonthNum As Integer
    DDate = Range("C2") 'Cell With Date in it
    MonthNum = Month(DDate)
 
    If MonthNum = 1 Then
        Range("C2").Copy Range("C5")
    ElseIf MonthNum = 2 Then
        'Range("C2").Copy (Sheets("TEST2").Range("C16"))
        Range("C2").Copy Range("C6")
    ElseIf MonthNum = 3 Then
        Range("C2").Copy Range("C7")
    ElseIf MonthNum = 4 Then
        Range("C2").Copy Range("C8")
    ElseIf MonthNum = 5 Then
        Range("C2").Copy Range("C9")
    ElseIf MonthNum = 6 Then
        Range("C2").Copy Range("C10")
    ElseIf MonthNum = 7 Then
        Range("C2").Copy Range("C11")
    ElseIf MonthNum = 8 Then
        Range("C2").Copy Range("C12")
    ElseIf MonthNum = 9 Then
        Range("C2").Copy Range("C13")
    ElseIf MonthNum = 10 Then
        Range("C2").Copy Range("C14")
    ElseIf MonthNum = 11 Then
        Range("C2").Copy Range("C15")
    ElseIf MonthNum = 12 Then
        Range("C2").Copy Range("C16")
    Else
    End If
   
    Application.CutCopyMode = False
End Sub

currently the date in C2 is 14/02/2022 so i've only coded MonthNum = 2 to go to TEST2 as its february .

but in sheet TEST 2
i know i need to use vlookup in VBA to find the right column but i keep getting errors
in TEST 2 column b is the dates in the month so B2 - B30 for feb is 1/2/22 - 28/2/22

 Dim vlook As Long
    Dim Mdate As Long
    Mdate = Range("C2")
    Set Myrange = Sheets("TEST2").Range("B1:C33")
    vlook = Application.WorksheetFunction.VLookup(Mdate, Myrange, 1, False)
    MsgBox "Date is " & Mdate & " on date " & vlook & " date"

i wrote this code
to see if i could get the vlookup to show me the right row to put in , so TEST2 C16 would be 14/2/22 but i want the code to find it itself

 

[Fluff]

Hi & welcome to MrExcel.
I'm a bit confused. You say that you have dates in Test2 starting in B2 with 1st Feb, but you say that the 14th should go in C16 which 15th Feb
So do your dates start in B2 or B3?
Also if where are the dates for the other months?