Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2004
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste ID In a Loop (excel 2002)

    Hi,
    would like someone to guide to perform following:

    --> In exel tab A i have following data
    SITE ID:
    111
    222
    333

    --> In exel tab B i have following data
    Sample:
    Company_ID Site_ID
    272 0
    272 0
    272 0

    i need OUTPUT in tab C hav following data:
    Company_ID Site_ID
    272 111
    272 111
    272 111
    272 222
    272 222
    272 222
    272 333
    272 333
    272 333

    pls guide me the code that can read from tab A to gather distinct company site id
    and for each distinct site id ,read the numbers of rows in tab B and perform continue
    loop paste for each site id in tab C

    Thanks
    Gan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Paste ID In a Loop (excel 2002)

    Welcome to Woody's Lounge!

    Try code like this:
    <code>
    Sub TransferAB2C()
    Dim wshA As Worksheet
    Dim wshB As Worksheet
    Dim wshC As Worksheet

    Dim lngMaxRowA As Long
    Dim lngMaxRowB As Long
    Dim lngRowA As Long
    Dim lngRowB As Long
    Dim lngRowC As Long

    ' substitute the correct names
    Set wshA = Worksheets("A")
    Set wshB = Worksheets("B")
    Set wshC = Worksheets("C")

    lngMaxRowA = wshA.Cells(65536, 1).End(xlUp).Row
    lngMaxRowB = wshB.Cells(65536, 1).End(xlUp).Row

    lngRowC = 1
    For lngRowA = 2 To lngMaxRowA
    For lngRowB = 2 To lngMaxRowB
    lngRowC = lngRowC + 1
    wshC.Cells(lngRowC, 1) = wshB.Cells(lngRowB, 1)
    wshC.Cells(lngRowC, 2) = wshA.Cells(lngRowA, 1)
    Next lngRowB
    Next lngRowA

    Set wshC = Nothing
    Set wshB = Nothing
    Set wshA = Nothing
    End Sub</code>

  3. #3
    Lounger
    Join Date
    Dec 2004
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste ID In a Loop (excel 2002)

    thanks alots!!!!! it work welll with your codes......
    merry christmas!!

Posting Permissions

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