I am trying to pull two cells from one page into a summary type sheet. One of the two cells will pull only one line of similar text it finds in column, and returns it to the first cell on summary sheet, then it finds the next line of text that is different and placing it on summary sheet. Then it finds the designation to one of those rows of text and returns it to the corresponding cell.
EXAMPLE:
Plumbing Data Input (Tab)
Cell G8 is first cell in column which can run down to 1,000.
This cell uses a Data Validation pull down to select a fixture type (heading of column) which will then populate 3 other cells (to the right) with fixture units for a calculation.
Cell H8:H1000 will require me to input a designation for that type of fixture and a quantity in the next cell as I go down column and input data.
Plumbing Design (Tab)
Cell B9 I am trying to do a macro to pull only one fixture type at a time (and only one of that type from Plumbing Data Input tab) and place it in the first cell B9. From here cell E9 needs to be populated with the corresponding fixture designation that was given to that fixture type. So I may have 30 - "F.V. Water Closets (1.6 gpm)" and a designation of "WC-1" and another 20 fixtures of "ADA F.V. Water Closet (1.6 gpm)" and a designation of "WC-2". All of the 30 WC-1 fixtures are input in 30 cells and the 20 WC-2 fixtures are in another 20 differnet cells. There are a lot of different fixtures that will be in this input tab, but I only want to pul one similar one from all of those and place it in cell.
Here is the Macro I was trying to use, but fails to return anything. Again I am very green when it comes to Macros.
Sub SortData()
Dim rg As Range, c As Range, i As Integer, SL As Object
Set rg = Sheets("Plumbing Data Input").Range("G8:G:1000") '<< adapt range here
With CreateObject("System.Collections.SortedList")
For Each c In rg
If c <> "" Then .Item(c.Value) = c.Value
Next c
For i = 1 To .Count - 1
Sheets("Plumbing Design").Range("B" & i + 9) = .getbyindex(i)
Next i
End With
End Sub
Thanks
J
Bookmarks