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

philhendry

New Member
Joined
Feb 3, 2022
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
VBA 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

VBA Code:
 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
 
Last edited by a moderator:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,502
Office Version
  1. 365
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,175,502
Messages
5,897,814
Members
434,678
Latest member
Gautam cv

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top