Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Loop will not return values for duplicate record

    Hi I am currently battling with a for loop in which values are returned from xlMainSheet to xlGlobalClock if the values in column A of both match. The loop works untill a duplicate value exists in column A of xlGlobalClock in which the required value is only returned (to column H) for the first instance only and not for the duplicate. It is OK to have duplicates of Column A in xlGlobalClock.

    I have pasted the code below, any help with this will be most appreciated I have been battling with it for hours and I'm quite new to Vb

    ================================================== =

    Sub Transfer_Lead_Name()



    Dim xlApp As Excel.Application
    Dim xlBookActive As Excel.Workbook
    Dim xlMainSheet As Excel.Worksheet
    Dim xlGlobalClock As Excel.Worksheet

    Dim iLoop As Long
    Dim iMasterLoop As Long

    Dim gCount As Long

    gCount = 2

    Set xlBookActive = ActiveWorkbook

    Set xlMainSheet = xlBookActive.Worksheets(5)
    Set xlGlobalClock = xlBookActive.Worksheets(3)


    'xlGlobalClock.Cells.ClearContents
    Dim LastRow As Long

    LastRow = Range("A65536").End(xlUp).Row + 1

    'Range("A:G").Clear


    For iLoop = 1 To 65000 Step 1
    If xlMainSheet.Range("A" & iLoop & ":A" & iLoop).Value = "" Then
    Exit For
    End If
    Next iLoop

    For iMasterLoop = 1 To (iLoop - 1) Step 1


    If xlMainSheet.Range("A" & iMasterLoop & ":A" & iMasterLoop).Value = xlGlobalClock.Range("A" & gCount & ":A" & gCount).Value Then


    xlGlobalClock.Range("H" & gCount & ":H" & gCount).Value = xlMainSheet.Range("G" & iMasterLoop & ":G" & iMasterLoop).Value


    gCount = gCount + 1

    End If
    Next iMasterLoop
    End Sub

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    can you run the REMOVE DUPLICATES function (2010) on sheet1 & paste into sheet3,
    then run the macro against sheet3? (no dupes)

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    Hi i need the duplicate for column A.

    e.g Column A value = John - Column H value = 2000 (returned value from loop)
    Column B value = John - Column H value = 3000 (returned value from loop)

    The loop stops at populating first row, but I need to return the H value from another sheet where column A matches in both sheets. If I remove the column A duplicates then the loop works OK

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    keep data in DATAbases, they are very very good at this sort of task, whereas spreadhseets are flaky at best
    use spreadsheets if you MUS to analyse data
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2006
    Posts
    65
    Hi Healdem
    yes i totally agree, I am picking up work already started by my predecessor and the spreadsheet is a combination of manual entries by the user linked to automation. For now the spreadsheet stays

Posting Permissions

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