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

    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)

    Dim LastRow As Long

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


    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
    Provided Answers: 23
    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
    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
    out on a limb
    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
    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