f2e4
06-11-2008, 09:09 AM
Hey again,
Listbox3 located in Sheet("Reports")
Core data located in Sheets("Workload")
Depending on a value (staffmember) selected in Listbox1, I am looking for code that will search for this value in Sheets("Workload") - Column G
Sheets("Workload") - Row 3 has all my headings. Columns B - H are just categories while Column I onwards is week-ending dates.
Once the staff member is found in Column G of Sheets("Workload") I need the value from Columns C (Project name) added to listbox3
As for the week-ending dates, I need 6 columns of data(first column being the current week-ending)
So the end result should be 7 columns of data (1 name and 6 values)
Another twist is that there may be more than one entry for Staffmember but the Project Names are uniqe. So the code would have to list a row of name and 6 values for each Project Name found for that particular staffmember.
Here is some code I have noted down that would probably need to go in it.
Option Explicit
Function ColumnLetter(col As Long)
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
Sub Add_Info_to_Listbox3()
'This code is not any particular order'
'I'm just noting down what needs doing
'Current Week ending date
StartDate = Date + 6 - Weekday(Date)
'Find weekending value in Row 3 of Sheet("Workload")
StartCol = Application.Match(CLng(Start2Date), Worksheets("Workload").Rows(3), 0)
'Find staff member in Column G of Sheets("Workload")
temp = ListBox1.ListIndex
With ListBox1
StaffMember = .List(temp, 1) & " " & .List(temp, 0)
End With
On Error Resume Next
Rw = Sheets("Workload").Columns(7).Find(StaffMember).Row
'Code to find and select 7 columns of data here
'
'and add to listbox 3
'
'
'
'
Sheets("Reports").ListBox1.Activate
End Sub
Example sheet attached in case none of the above made sense
Listbox3 located in Sheet("Reports")
Core data located in Sheets("Workload")
Depending on a value (staffmember) selected in Listbox1, I am looking for code that will search for this value in Sheets("Workload") - Column G
Sheets("Workload") - Row 3 has all my headings. Columns B - H are just categories while Column I onwards is week-ending dates.
Once the staff member is found in Column G of Sheets("Workload") I need the value from Columns C (Project name) added to listbox3
As for the week-ending dates, I need 6 columns of data(first column being the current week-ending)
So the end result should be 7 columns of data (1 name and 6 values)
Another twist is that there may be more than one entry for Staffmember but the Project Names are uniqe. So the code would have to list a row of name and 6 values for each Project Name found for that particular staffmember.
Here is some code I have noted down that would probably need to go in it.
Option Explicit
Function ColumnLetter(col As Long)
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function
Sub Add_Info_to_Listbox3()
'This code is not any particular order'
'I'm just noting down what needs doing
'Current Week ending date
StartDate = Date + 6 - Weekday(Date)
'Find weekending value in Row 3 of Sheet("Workload")
StartCol = Application.Match(CLng(Start2Date), Worksheets("Workload").Rows(3), 0)
'Find staff member in Column G of Sheets("Workload")
temp = ListBox1.ListIndex
With ListBox1
StaffMember = .List(temp, 1) & " " & .List(temp, 0)
End With
On Error Resume Next
Rw = Sheets("Workload").Columns(7).Find(StaffMember).Row
'Code to find and select 7 columns of data here
'
'and add to listbox 3
'
'
'
'
Sheets("Reports").ListBox1.Activate
End Sub
Example sheet attached in case none of the above made sense