Code help

[Daveo]

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

 

[Bob Phillips]

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]

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)

Hi Bob,

That's great - thankyou very much!!

Cheers - David