VBA - Matching specific date horizontally and insert value

[minhlac]

Hi all,

I found this forum very helpful so I sign up and this is my first post in this forum.

Please help! I have data as below, date from column D will keep going to the right (as a diary record).

	A	B	C	D	E	F	G	H	I	J
1	Item	Quantity	Delivery date	04/08	05/08	06/08	07/08	08/08	09/08	10/08
2	red	2	07/08				2
3	blue
4	yellow
5	pink	5	09/08						5

<tbody>
</tbody>

I would like to have a solution with Excel VBA carry out two tasks

1. For the example above, for item "red" when input delivery date and quantity then it will scan through row 2 for a matching date and leave the quantity at the intersecting cell of the same row looking up to that date column. Therefore we have the result 2 in cell "G2". In general, VBA will look up value (date format) in column C to match value (also date format) of any column from D onward, find the intersecting cell of the value current row and matched date column, then pick up value in column B (same row) and to insert in intersecting cell.

2. The intersecting cell value will stay as "text" only without formula format since column B and C will be dynamic changes based on user input.

I hope that would make sense.
Thank you in advance for your time.

 

[fhqwgads]

hope this works

Sub mrexcelmatchdatetest1()

Dim i As Integer
Dim datecol As Integer
Dim lastrow As Integer

lastrow = Range("A1").End(xlDown).Row

For i = 2 To lastrow
    
    If Range("C" & i) <> "" Then
        datecol = Application.WorksheetFunction.Match(Range("C" & i), Rows(1), 0)
        
        Cells(i, datecol).Value = Range("B" & i).Value
    Else
    
    End If

Next i

End Sub

 

[minhlac]

Hi fhqwgads,

It works like a charm. Thank you for your help.

 

[minhlac]

Hi fhqwgads,

your code works fine with column C manually text input, when I replace with a formula to calculate delivery date and it reset all the date on column C to 02/01/1900. Would you have a look again to modify the previous code.

Thank you.

 

[fhqwgads]

hmm not sure what went wrong
what exactly are you putting in column C before running the sub?

 

[minhlac]

Hi fhqwgads ,

Initially, Column C was text input (manually).

Now, Column C (in date format) = ETA date + Today() ~~~> where ETA date is another column retrieved from another worksheet.

So Column C values are now formulated instead of text input...This is when your previous code call an error because it reset all the value (in date format back to 02/01/1900). Even though I tried to change the date format to number it won't work.

Thank you in advance for your help.

 

[fhqwgads]

dont thank me just yet, i dont have all the answers. i just do the best i can with what i know

i'm not sure why all values in C are changing to 02/01/1900 after running the code. the code shouldnt affect C as far as i can tell

what do the in values C look like before running the code?

 

[minhlac]

C values are just going to look like a date (ei. 31/07/2018...etc) from the formula I have provided above.

Formula Today() will give the date format and plus ETA (number of days) will result in a final date of delivery which is also in date format.

 

[fhqwgads]

i just dont know what went wrong
i'm sorry my suggestion didnt work out for you. hopefully someone else can give a better solution

 

[minhlac]

Thank you anyway.