Hi Nikki -
This version clears a contiguous list of CC's in column A before making the
new list. Let me know if adjustments are needed.
Sub Nikki()
Dim ws1, ws2 As Worksheet
Dim tbl As Range
Set ws1 = Worksheets("Team Numbers")
Set ws2 = Worksheets("Site Allocation")
Set tbl = ws1.Range("A1").CurrentRegion
ws2.Range("A1") = "CC"
'Clear pre-existing data from output range
If ws2.Range("A1").Offset(1, 0) <> "" Then
ws2.Range("A1").End(xlDown).ClearContents
End If
For Each itm In tbl.Columns(4).Cells
If IsNumeric(itm.Value) And itm.Value > 0 Then
os = os + 1
ws2.Range("A1").Offset(os, 0) = itm.Offset(0, -3)
End If
Next 'itm
End Sub
--
Jay
"Nikki" wrote:
> Thank you. I have tried that with a limited sheet and it works fine. Just
> one extra part that would be handy:
>
> If I adjust the amounts in 'Team Numbers' by adding a value to a CC that
> didn't previously have on then it does add that to the list is 'Site
> Allocation'. BUT if I delete a value for a CC then it shortens the list in
> 'Site Allocation' but still leaves the last lines at the bottom. Is there a
> way to get the visual basic code to delete these lines?
>
> Thanks
>
> Nikki
>
> "Jay" wrote:
>
> > Hi Nikki -
> >
> > Two methods come immediately to mind to achieve your goal. The first would
> > use an advanced filter to copy the 'CC' field results that meet the criterion
> > to an output range on the 'Team Numbers' sheet and then use formulas on the
> > 'Site Allocation' sheet to refer to the output range. However, this is not
> > my preferred choice.
> >
> > My preference is to use the following visual basic procedure. Copy it to a
> > standard module, modify as necessary (or repost for further instructions),
> > and test run it.
> >
> > The procedure assumes the following (adjust as needed):
> > The worksheet "Site Allocation" exists before the procudure is run.
> > The output range on the "Site Allocation" worksheet starts in cell A1.
> > The table begins in cell A1 of the "Team Numbers" worksheet.
> >
> > Sub Nikki()
> > Dim ws1, ws2 As Worksheet
> > Dim tbl As Range
> >
> > Set ws1 = Worksheets("Team Numbers")
> > Set ws2 = Worksheets("Site Allocation")
> > Set tbl = ws1.Range("A1").CurrentRegion
> > ws2.Range("A1") = "CC"
> >
> > For Each itm In tbl.Columns(4).Cells
> > If IsNumeric(itm.Value) And itm.Value > 0 Then
> > os = os + 1
> > ws2.Range("A1").Offset(os, 0) = itm.Offset(0, -3)
> > End If
> > Next 'itm
> >
> > End Sub
> >
> > --
> > Jay
> >
> >
> > "Nikki" wrote:
> >
> > > Hi,
> > >
> > > I have a table in on worksheet called 'Team Numbers' that has the following:
> > > CC Team No Deapartment L4 L3
> > > 11 11 Finance 9 2
> > > 14 4 IS 4
> > > 15 4 IS 4
> > > 16 0 Networks
> > > 31 0 HR
> > > 211 10 Networks 10
> > > 213 7 Networks 7
> > >
> > > I have another worksheet called 'Site Allocation'
> > > In the sheet I want to have a formula that does something like this:
> > > In 'Team Numbers' look at first row in column 4 (L4) if this is >0 then put
> > > the number from column 1 (CC). If the number in column 4 is 0 or blank then
> > > I don't want it to put anything. I then want it to do the same thing again
> > > but for the second row.
> > > The list that appears in the 'Site Allocation' sheet needs to only have the
> > > CC's listed that have numbers in L4 but I don't want any gaps.
> > > e.g
> > > 11
> > > 14
> > > 15
> > > 211
> > > 213
> > >
> > > Any ideas?
> > >
|
Similar Threads
