If Two Cells Match Insert Data Two Rows Down - Help

[jbenfrancis1]

Hi, i have done a lot of searching and found some useful stuff but cannot piece it together to make it work. Here is my dilemma.

Sheet1 has data on it and the corresponding date.
Sheet2 has historical data on it with dates.

Sheet2 has column headers for each day so for example C6 is 2/21/2022, D6 is 2/20/2022 and so on all the way out to BJ6.

I am trying to write some code that will look at the date on Sheet1 and search for it on Sheet2 row 6. If they match, then add the data from Sheet1 2 rows below the date on Sheet2

This code allow it to search the range correctly and cycle through

Dim b As Range
Set b = Sheets("Sheet2").Range("C6:BJ6")
For Each c In Sheets("Sheet1").Range("C2")
    If IsNumeric(Application.Match(c, b, 0)) Then
       'I don't know what to put here to say stop and insert data once match is found
        Exit Sub
        End If
    Next c

 

[JoeMo]

Hi, i have done a lot of searching and found some useful stuff but cannot piece it together to make it work. Here is my dilemma.

Sheet1 has data on it and the corresponding date.
Sheet2 has historical data on it with dates.

Sheet2 has column headers for each day so for example C6 is 2/21/2022, D6 is 2/20/2022 and so on all the way out to BJ6.

I am trying to write some code that will look at the date on Sheet1 and search for it on Sheet2 row 6. If they match, then add the data from Sheet1 2 rows below the date on Sheet2

Can you provide some details about the data from sheet1 you want to add to sheet2 when a match is found? Perhaps you can post some sample data for sheet1 using XL2BB.

 

[jbenfrancis1]

This is an example, to pull the data from sheet1 over to sheet2 only if the date on sheet1 matches. Sheet1 will have new data in it everyday (yesterdays data) and a new column is added to shet2 with yesterdays date. Trying to use macros though so when we have long weekends and holidays it will match dates and add in correct spots

Example .xlsx
	B	C	D	E	F	G	H
2		21-Feb		20-Feb		19-Feb
3	Book
4	Author
5	ISBN
Sheet2

Example .xlsx
	A	B	C	D	E
1
2		Date	2/21/2022
3
4
5		Book	The Wild
6		Author	J. Wild
7		ISBN	9059635412
Sheet1

 

[JoeMo]

In your OP you indicated the dates in Sheet2 are in row 6, but that's not what your sample shows. Are the dates on sheet2 in merged cells or are they just centered across two adjacent cells?

 

[jbenfrancis1]

In your OP you indicated the dates in Sheet2 are in row 6, but that's not what your sample shows. Are the dates on sheet2 in merged cells or are they just centered across two adjacent cells?

Yeah sorry, They should be in row six let me send you an upodated version. And yes C and D are merger, thats the formatting of the book I am working in

Example .xlsx
	A	B	C	D	E	F	G	H
1
2
3
4
5
6			21-Feb		20-Feb		19-Feb
7		Book
8		Author
9		ISBN
Sheet2

 

[jbenfrancis1]

Example .xlsx
	A	B	C	D	E	F	G	H
1
2
3
4
5
6			21-Feb		20-Feb		19-Feb
7		Book
8		Author
9		ISBN
Sheet2

Had to merge everything correct.

 

[JoeMo]

I'm still a bit confused - thought you wanted the data from col C in sheet1 to be placed two rows below the matching date in sheet2??

 

[jbenfrancis1]

Alright let me give this one last try, sorry I should read my own questions better. Here is a fresh booklet of each. I would like data in sheet1 to file into sheet2 based off date matching. Let me know if you need additional info. These should exactly how my sheets are layed out.

Example .xlsx
	A	B	C	D
1
2		Date	2/21/2022
3
4		Volume	Percent
5	Book 1	100	57.14
6	Book 2	50	28.57
7	Book 3	25	14.28
Sheet1

Example .xlsx
	A	B	C	D	E	F	G	H
1
2
3
4
5
6			21-Feb		20-Feb		19-Feb
7			Volume	Percent	Volume	Percent	Volume	Percent
8		Book 1
9		Book 2
10		Book 3
Sheet2

 

[JoeMo]

OK. Give this a try on a copy of your workbook.

Sub jbenfrancis1()
Dim b As Range, Fnd As Variant
With Sheets("Sheet2")
    Set b = .Range("C6:BJ6")
    Fnd = Application.Match(CLng(Sheets("Sheet1").Range("C2")), b, 0)
    If IsError(Fnd) Then
        MsgBox "Date not found in sheet2, row6 - exiting sub"
        Exit Sub
    Else
        .Cells(8, Fnd + 2).Resize(3, 2).Value = Sheets("Sheet1").Range("B5:C7").Value
    End If
End With
End Sub

 

[jbenfrancis1]

OK. Give this a try on a copy of your workbook.

Sub jbenfrancis1()
Dim b As Range, Fnd As Variant
With Sheets("Sheet2")
    Set b = .Range("C6:BJ6")
    Fnd = Application.Match(CLng(Sheets("Sheet1").Range("C2")), b, 0)
    If IsError(Fnd) Then
        MsgBox "Date not found in sheet2, row6 - exiting sub"
        Exit Sub
    Else
        .Cells(8, Fnd + 2).Resize(3, 2).Value = Sheets("Sheet1").Range("B5:C7").Value
    End If
End With
End Sub

Thank you for the help and for being patient! Works like a charm