MATCHING 2 COLUMN AND PUT INTO TEMPLATE SHEET

[sal21]

I have this 2 sheets:

GAF
CORPORATE

How to matching range H:N into CORPORATE with the column H in GAF and when the value into range H:N is found in column H of GAF insert the line into template the value of cells..

Example:
Into range H2:N2 of CORPORATE are present: 1, 2, 3, 4, 6, 7, 15
into column H of sheet GAF are present many cells with the same value 1, 2, 3, 4, 6, 7, 15

Copy all line matched in TEMPLATE

Finished the first range of matching go to next line of CORPORATE range H3:N£ clear the value into TEMPLATE amnd insert the new value amtched with the same tecinque...

Do until the value in E of CORPORATE is blank...

Attached a TEMPLATE with the value of first rangeH2:N2 of CORPORATE

hope i am clear.
Attaced example

http://www.mytempdir.com/970855

 

[agihcam]

just create a new sub and cut and paste the code. I think you can do that easily.

 

[sal21]

just create a new sub and cut and paste the code. I think you can do that easily.

Maked this, but recopy also blank cells and value in CORPORATE...
I want matching only column F and range H:S from RETAIL_POE

Sub test()
Dim V As Long
Sheets("TEMPLATE").Columns("A").NumberFormat = "@"

For V = 2 To Sheets("RETAIL_POE").Range("f" & Rows.Count).End(xlUp).Row
With Sheets("GAF").Columns("d")
Set c = .Find(Sheets("RETAIL_POE").Cells(V, "f").Value, , , xlWhole)
If Not c Is Nothing Then
f = c.Address
Do
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = c.Offset(, -3).Resize(, 2).Value
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 2).Resize(, 8).Value = c.Resize(, 8).Value
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 10).Resize(, 3).Value = c.Offset(, 10).Resize(, 3).Value
Set c = .FindNext(c)
Loop Until f = c.Address
End If

End With
If Sheets("RETAIL_POE").Cells(V + 1, "d").Value <> Sheets("RETAIL_POE").Cells(V, "d").Value Then
MsgBox "Block for " & Sheets("RETAIL_POE").Cells(V, "d").Value & " copied", vbInformation + vbOKOnly, "End of Block"
End If
Next
Application.ScreenUpdating = False
End Sub

 

[agihcam]

I want matching only column F and range H:S from RETAIL_POE

is this a new requirement? you only mentioned to compare columnF from previous post and the code is doing that.

 

[sal21]

I want matching only column F and range H:S from RETAIL_POE

is this a new requirement? you only mentioned to compare columnF from previous post and the code is doing that.

i am sorry... but in this day ny head is "fired", yes new requirement.

 

[agihcam]

how about;

Sub test()
Dim v As Long
Dim myrng As Range
Sheets("TEMPLATE").Columns("A").NumberFormat = "@"
For v = 2 To Sheets("RETAIL_POE").Range("f" & Rows.Count).End(xlUp).Row
With Sheets("GAF").Columns("d")
Set c = .Find(Sheets("RETAIL_POE").Cells(v, "f").Value, , , xlWhole)
Set myrng = Sheets("RETAIL_POE").Range("h" & v & ":s" & v)
If Not c Is Nothing Then
f = c.Address
Do
If Application.WorksheetFunction.CountIf(myrng, c.Offset(, 4).Value) > 0 Then
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = c.Offset(, -3).Resize(, 2).Value
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 2).Resize(, 8).Value = c.Resize(, 8).Value
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 10).Resize(, 3).Value = c.Offset(, 10).Resize(, 3).Value
End If
Set c = .FindNext(c)
Loop Until f = c.Address
End If
End With
If Sheets("RETAIL_POE").Cells(v + 1, "d").Value <> Sheets("RETAIL_POE").Cells(v, "d").Value Then
MsgBox "Block for " & Sheets("RETAIL_POE").Cells(v, "d").Value & " copied", vbInformation + vbOKOnly, "End of Block"
End If
Next
Application.ScreenUpdating = False
End Sub

 

[sal21]

how about;

Sub test()
Dim v As Long
Dim myrng As Range
Sheets("TEMPLATE").Columns("A").NumberFormat = "@"
For v = 2 To Sheets("RETAIL_POE").Range("f" & Rows.Count).End(xlUp).Row
With Sheets("GAF").Columns("d")
Set c = .Find(Sheets("RETAIL_POE").Cells(v, "f").Value, , , xlWhole)
Set myrng = Sheets("RETAIL_POE").Range("h" & v & ":s" & v)
If Not c Is Nothing Then
f = c.Address
Do
If Application.WorksheetFunction.CountIf(myrng, c.Offset(, 4).Value) > 0 Then
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 2).Value = c.Offset(, -3).Resize(, 2).Value
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 2).Resize(, 8).Value = c.Resize(, 8).Value
Sheets("TEMPLATE").Range("a" & Rows.Count).End(xlUp).Offset(, 10).Resize(, 3).Value = c.Offset(, 10).Resize(, 3).Value
End If
Set c = .FindNext(c)
Loop Until f = c.Address
End If
End With
If Sheets("RETAIL_POE").Cells(v + 1, "d").Value <> Sheets("RETAIL_POE").Cells(v, "d").Value Then
MsgBox "Block for " & Sheets("RETAIL_POE").Cells(v, "d").Value & " copied", vbInformation + vbOKOnly, "End of Block"
End If
Next
Application.ScreenUpdating = False
End Sub

... no dubt you are a Wizard!!!!!!
Now the project is perfect!
Tks for all and tks for patience...

Note:
Insted to use offset statement is possible to use column refrence when the macro copy the value, in this case i change easly the cells wath i want,-)???

Similar:

Worksheet("TEMPLATE").Range("A"& Rows.Count) = Worksheet("variuos value from GAF").Range("A")
Worksheet("TEMPLATE").Range("B"& Rows.Count) = Worksheet("variuos value from GAF").Range("D")

ecc....
in this case is very easly to change the reference of column and row, i cannot know whell the medot OFFset;-(

Tks but have solved this prob about range and cell of offset, tks...

Is possible to limit the copy of matched value from GAF to TEMPLTE only if in GAF column S is present the value "CE"????