+ Reply to Thread
Results 1 to 2 of 2

find record but pull wrong row data for record

Hybrid View

  1. #1
    Registered User
    Join Date
    07-13-2022
    Location
    Ga
    MS-Off Ver
    2010
    Posts
    25

    find record but pull wrong row data for record

    ok I am fairly new to VBA. programmer but in Cobal old school.
    2 workbooks, both have ID in column A. Take ID from Wb1 find in Wb2 want to copy column M from Wb2 to column M in Wb1.
    Wb1 has 605 records this were ID to find is, Wb2 has 7021 records this contains what i want to copy. My code works but when checking the data it was wrong.
    if stepped thru with each loop thru code it adds 1 to the row it finds the reocord in and moves that.

    first pass is correct, second pass it is off by 1 (pulls the info 1 row below). Third pass is off by 2 (pulls the info 2 rows below). fourth pass is off by 3 (pulls the info 3 rows below) ECT.

    I just can't figure this out, several ways work just same results!! HELP

    [code]

    Sub Vend_move()

    Dim wslr&, ws1lr&, i&, r&, f, wsRng, ws1Rng
    Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet
    Dim wb1 As Workbook, wb2 As Workbook

    Set wb1 = Workbooks("Order_Pad")
    Set wb2 = Workbooks("asset_history")

    Set ws = wb1.Worksheets("MainOrder")
    Set ws1 = wb2.Worksheets("asset_history")

    'Set ws2 = Worksheets("Sales")
    wslr = ws.Cells(Rows.Count, "A").End(xlUp).Row
    ws1lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row

    wsRng = ws.Range("A2:N" & wslr).Value
    ws1Rng = ws1.Range("A2:N" & ws1lr).Value
    r = 2

    For i = 1 To wslr - 1
    Set f = ws1.Range("A:A").Find(wsRng(i, 1), ws1.Range("A1"))
    If Not f Is Nothing Then

    f.Cells(i, 7).Copy ws.Cells(r, "M")
    r = r + 1

    'GoTo z:
    'Else

    End If
    'z:
    Next i

    [code/]

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,037

    Re: find record but pull wrong row data for record

    Change the workbook names (in red) and sheet names (in blue) to suit your needs.
    Sub CompareData()
        Application.ScreenUpdating = False
        Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, dic As Object, i As Long
        Set desWS = Workbooks("wb1.xlsx").Sheets("Sheet1")
        Set srcWS = Workbooks("wb2.xlsx").Sheets("Sheet1")
        v1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 13).Value
        v2 = desWS.Range("A2", desWS.Range("A" & Rows.Count).End(xlUp)).Value
        Set dic = CreateObject("Scripting.Dictionary")
        For i = LBound(v1) To UBound(v1)
            If Not dic.exists(v1(i, 1)) Then
                dic.Add v1(i, 1), v1(i, 13)
            End If
        Next i
        For i = LBound(v2) To UBound(v2)
            If dic.exists(v2(i, 1)) Then
                desWS.Range("M" & i + 1) = dic(v2(i, 1))
            End If
        Next i
        Application.ScreenUpdating = True
    End Sub
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select the record in a spreadsheet that matches the selected record in a listbox control
    By vancouver_ron in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2022, 11:50 AM
  2. Replies: 1
    Last Post: 10-18-2020, 10:29 AM
  3. Copying A Record/Row from an ODBC-Downloaded Query When Record Meets Criteria
    By Saabra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-07-2020, 02:55 PM
  4. Replies: 1
    Last Post: 05-17-2016, 12:02 PM
  5. Replies: 4
    Last Post: 12-17-2012, 10:14 AM
  6. Replies: 1
    Last Post: 11-16-2012, 09:25 AM
  7. Command Buttons for Viewing Record, Next Record and Previous Record
    By david1987 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-23-2012, 06:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1