Results 1 to 2 of 2

Thread: Need a way to avoid duplicates from a column and to expand past template sheet limits

  1. #1

    Need a way to avoid duplicates from a column and to expand past template sheet limits



    Register for a FREE account, and/
    or Log in to avoid these ads!

    This what I have so far.
    I need a data sheet to be exported to a template sheet according to the columns given. However I need column "F" to not import duplicates in to the template sheet. Also the template sheet has a formula in row A68 and the code I have makes me put the data after it even though I need it before the formula as well as add new rows if there is more than 64 rows of data taken. I say 64 because the blank rows start at 4. Please help cause I feel like I'm almost done with this project yet I'm hitting some road blocks.
    Code:
    Sub Redemption()
        
    Dim wsDatatable As Worksheet
    Dim wsTempelate As Worksheet
    Dim rangeFound As Range
    Dim rangeNames As Range
    Dim NameCells As Range
    Dim stringFirst As String
    Dim stringNames As String
    Dim stringUniqueNames As String
        
        Set wsDatatable = Sheets("DATA INPUT TABLE")
        Set wsTempelate = Sheets("CLASS GROUPING ID")
        Set rangeNames = wsDatatable.Range("A2", wsDatatable.Cells(Rows.Count, "A").End(xlUp))
        
        For Each NameCells In rangeNames.Cells
            If InStr(1, "|" & stringUniqueNames & "|", "|" & NameCells.Text & "|", vbTextCompare) = 0 Then
                stringUniqueNames = stringUniqueNames & "|" & NameCells.Text
                Set rangeFound = rangeNames.Find(NameCells.Text, rangeNames.Cells(rangeNames.Cells.Count), xlValues, xlWhole)
                If Not rangeFound Is Nothing Then
                    stringFirst = rangeFound.Address
                    stringNames = NameCells.Text
                    stringNames = Trim(Left(WorksheetFunction.Trim(stringNames), 31))
                    If Evaluate("IsRef('" & stringNames & "'!A1)") = False Then
                        wsTempelate.Copy After:=Sheets(Sheets.Count)
                        ActiveSheet.Name = stringNames
                    End If
                    With Sheets(stringNames)
                        Do
                            If LCase(wsDatatable.Cells(rangeFound.Row, "I").Text) = "full liquidation" Or LCase(wsDatatable.Cells(rangeFound.Row, "I").Text) = "redemption" Then
                                .Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "E").Value
                                .Cells(Rows.Count, "B").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "F").Value
                                .Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "B").Value
                                .Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "O").Value
                                .Cells(Rows.Count, "E").End(xlUp).Offset(1).Value = wsDatatable.Cells(rangeFound.Row, "L").Value
                            End If
                            Set rangeFound = rangeNames.Find(NameCells.Text, rangeFound, xlValues, xlWhole)
                        Loop While rangeFound.Address <> stringFirst
                    End With
                End If
            End If
        Next NameCells
        
        Set wsDatatable = Nothing
        Set wsTempelate = Nothing
        Set rangeFound = Nothing
        Set rangeNames = Nothing
        Set NameCells = Nothing
        
    End Sub

  2. #2
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    This is a little hard to conceptualize. Suggest you upload a sample workbook including the code you have to date, so that it's easier for someone to see what you're after.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •