Look up then copy & paste values

  • Hi,
    I am new to VBA & was trying to follow the post shown here.

    I got a little confused & now the values dont end up in the correct cells..
    Please could you assist me & some information here to help you.
    SHEET 1
    Month in cell G3
    First Value in cell J31
    Second Value in cell K31

    SHEET 2
    Months are in column range C5:C17
    First value to be placed in column D
    Second value to be placed in column E

    So i was expecting this to happen.
    Run macro.
    Code looks at SHEET 1 in cell G3 & remembers JULY
    Code also looks at value in cell J31 of which is £12.34
    Code also looks at value in cell K31 of which is 7

    Code then looks at SHEET 2 for a match in column C thus JULY.
    Once JULY has been found in the cell to the right now column D paste the first value £12.34
    I also then require the second value to be placed in the next cell to the right now column E the second value 7

    Here is the code i have in use if you can help please.
    Have a nice day.

  • Hello,
    Looking at the find code

  • Hi,
    So below is the working code.

    I forgot to ask one thing.
    On the G SUMMARY sheet where the values are written etc we have two months of APRIL in column C
    Cell C5 is now named APRIL 1 where cell C17 is now named APRIL 2

    What can you advise so the code is correctly placed in the correct cells with regards APRIL 1 & APRIL 2

    This is for my end of year tax accounts so we have APRIL 1 "being 2019" & APRIL 2 "being 2020"

    Can something be added into the code that if the current year is 2019 then when the codes finds the month it knows only to look at APRIL 1 & not APRIL 2 which will only work if the current year is 2020
    In cell J3 is the year in question.
    Please see attached file above.

    Did i explain correctly.
    I wish to use the same code each year so the year thing needs to represent the pc year date so i dont need to remember to change it all the time.

    Any more info required please advise

  • Hello,
    something like,,,,

  • Morning,
    Thanks for the input i like how it works but its not quite right but i dont think it will take you long to see the problem

    So this is my test.
    On the worksheet called G INCOME in cell G3 APRIL is entered
    In cell G5 i entered the date 10/04/2020
    I then pressed the transfer button.

    I then looked at the worksheet G SUMMARY and the value had transfered ok but was placed in the incorrect cell of which was D33
    With this code we should ONLY be using the range C5:C17 as that is the INCOME range the range of cells below D21:D33 are EXPENSES range and should not be used.

    If i enter in worksheet G INCOME cell G3 10/04/2019 the value MUST be entered in cell D5 because that first APRIL represents 2019

    If i had entered in cell G3 22/04/2020 then that value should be entered in cell D17 becuase that second APRIL represents 2020

    So the values must ONLY be entered into cells D5 or D17

    Hope we can sort this so have a nice day

    Please use photo attached.


  • arrr only for April

  • Hi,
    Just looking at the code.
    This line If "CDate(strDate) < CDate("2/04/2019") Then"

    I think the year for the code needs to be taken from cell J3 so the code updates itself each year.

    My end of tax year is 5th April so on my worksheet i start with April 2019 May 2019 June2019 ............February 2020 March 2020 April 2020

    Hope this helps

    So the code looks at the date in cell G3 & it also looks at the year in cell J3
    It then looks at my first entry in cell G5

    Now the code decides if value in G5 is BEFORE or AFTER 5th APRIL of the year in cell J3

    Once the code has decided it then places the values in cells D5:E5 or D17:E17 where D5 is the year BEFORE & D17 is the AFTER 5th April of the year in cell J3

    Did i explain that correctly for you.

  • Hi,
    Post 09 still cincorrect.
    Pleas4e see my post 10

    With the month APRIL and the year 2019 i type in G5 01/04/2019 but the value is entered in cell D17

    D17 is only for 2020

    D5 is only for 2019

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!