Code help
Hi,
I was wondering if anyone can help with the following. I have 3 worksheets. Sheet 1 contains data like the following: Name Group --------- --------- David 1 Peter 1 Jen 2 Paul 3 Sheet 2 contains data like the following Group Date 1 Date 2 Date 3 ..... --------- --------- ---------- --------- 1 Yes Yes No 2 No Yes Yes 3 No No Yes Sheet 3 contains the following headings: Name Group Date 1 Date 2 Date 3 ...... -------- -------- --------- --------- ---------- I would like to write a bit of code that would populate Sheet 3 with the persons name and all the dates they have a "Yes" in, according to the group they are in. i.e. The code looks up Sheet 2 for the dates corresponding to that persons group and then populates sheet 3 with all the dates. Is this possible? Many thanks, David |
Code help
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim iRow As Long With Worksheets("Sheet1") Worksheets("Sheet3").Range("A1:E1").Value = Array("Name", "Group", "Date 1", "Date 2", "Date 3") iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 2 To iLastRow Worksheets("Sheet3").Cells(i, "A").Value = .Cells(i, "A").Value Worksheets("Sheet3").Cells(i, "B").Value = .Cells(i, "B").Value iRow = Application.Match(.Cells(i, "B").Value, Worksheets("Sheet2").Columns(1), 0) Worksheets("Sheet3").Cells(i, "C").Value = _ Worksheets("Sheet2").Cells(iRow, "B").Value Worksheets("Sheet3").Cells(i, "D").Value = _ Worksheets("Sheet2").Cells(iRow, "C").Value Worksheets("Sheet3").Cells(i, "E").Value = _ Worksheets("Sheet2").Cells(iRow, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Daveo" wrote in message ups.com... Hi, I was wondering if anyone can help with the following. I have 3 worksheets. Sheet 1 contains data like the following: Name Group --------- --------- David 1 Peter 1 Jen 2 Paul 3 Sheet 2 contains data like the following Group Date 1 Date 2 Date 3 ..... --------- --------- ---------- --------- 1 Yes Yes No 2 No Yes Yes 3 No No Yes Sheet 3 contains the following headings: Name Group Date 1 Date 2 Date 3 ...... -------- -------- --------- --------- ---------- I would like to write a bit of code that would populate Sheet 3 with the persons name and all the dates they have a "Yes" in, according to the group they are in. i.e. The code looks up Sheet 2 for the dates corresponding to that persons group and then populates sheet 3 with all the dates. Is this possible? Many thanks, David |
Code help
On Sep 11, 11:02 am, "Bob Phillips" wrote:
Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim iRow As Long With Worksheets("Sheet1") Worksheets("Sheet3").Range("A1:E1").Value = Array("Name", "Group", "Date 1", "Date 2", "Date 3") iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 2 To iLastRow Worksheets("Sheet3").Cells(i, "A").Value = .Cells(i, "A").Value Worksheets("Sheet3").Cells(i, "B").Value = .Cells(i, "B").Value iRow = Application.Match(.Cells(i, "B").Value, Worksheets("Sheet2").Columns(1), 0) Worksheets("Sheet3").Cells(i, "C").Value = _ Worksheets("Sheet2").Cells(iRow, "B").Value Worksheets("Sheet3").Cells(i, "D").Value = _ Worksheets("Sheet2").Cells(iRow, "C").Value Worksheets("Sheet3").Cells(i, "E").Value = _ Worksheets("Sheet2").Cells(iRow, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Daveo" wrote in message ups.com... Hi, I was wondering if anyone can help with the following. I have 3 worksheets. Sheet 1 contains data like the following: Name Group --------- --------- David 1 Peter 1 Jen 2 Paul 3 Sheet 2 contains data like the following Group Date 1 Date 2 Date 3 ..... --------- --------- ---------- --------- 1 Yes Yes No 2 No Yes Yes 3 No No Yes Sheet 3 contains the following headings: Name Group Date 1 Date 2 Date 3 ...... -------- -------- --------- --------- ---------- I would like to write a bit of code that would populate Sheet 3 with the persons name and all the dates they have a "Yes" in, according to the group they are in. i.e. The code looks up Sheet 2 for the dates corresponding to that persons group and then populates sheet 3 with all the dates. Is this possible? Many thanks, David Hi Bob, That's great - thankyou very much!! Cheers - David |
All times are GMT +1. The time now is 10:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com