Results 1 to 4 of 4

Scripting Dictionary to update values

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Davenport, Iowa
    MS-Off Ver
    Excel 365
    Posts
    206

    Scripting Dictionary to update values

    Hi

    I have a script where I am referencing data from one sheet (A.aa) to another (B.bb) based on if the value is found (Column A), however, I am finding the data to the right of my sheet is being overwritten based on the number of columns that are being skipped. Any help would be greatly appreciated.

    Sub Update_ProjectInput()
    
    Dim Key As String
    Dim a As Workbook
    Dim aa As Worksheet
    Dim b As Workbook
    Dim bb As Worksheet
    Dim r As Long
    On Error GoTo CriticalErrors
    
    Application.ScreenUpdating = False
    Set a = ThisWorkbook
    Set aa = a.Sheets("A.AA")
    Set b = ThisWorkbook
    Set bb = b.Sheets("B.bb (Current Result)")
        
    With CreateObject("Scripting.Dictionary")
        For r = 2 To aa.Range("A" & Rows.Count).End(xlUp).Row
            Key = aa.Cells(r, 1)
            .Item(Key) = r
        Next r
        For r = 3 To bb.Range("A" & Rows.Count).End(xlUp).Row
            Key = bb.Cells(r, 1)
            If .exists(Key) Then bb.Cells(r, 6).Resize(1, 10).Value = aa.Cells(.Item(Key), 2).Resize(1, 5).Value
        Next r
    End With
    
    MsgBox "Completed", vbInformation, "Script Completed!"
    Application.ScreenUpdating = True
    Exit Sub
    
    CriticalErrors:
    MsgBox "An error has occured with the Update_ProjectInput Module, Please contact ", vbCritical, "Crikey!"
    Application.ScreenUpdating = True
    End
    
    End Sub
    Attached Files Attached Files
    Last edited by sriley5; 07-24-2019 at 12:45 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. scripting dictionary
    By madhukar37 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-15-2018, 06:13 AM
  2. [SOLVED] Scripting Dictionary to return unique values
    By sriley5 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2018, 05:48 AM
  3. [SOLVED] Dictionary or Scripting.Dictionary. Binding Referencing Dim-ing. Sub routines and Function
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 07-12-2016, 08:28 AM
  4. Scripting dictionary help
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-14-2014, 09:51 AM
  5. Help using Scripting Dictionary to sum values
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 08-09-2014, 04:15 PM
  6. [SOLVED] Using Dictionary in VBA scripting
    By vnzerem in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-04-2014, 02:54 PM
  7. [SOLVED] Add & calculate Values in Scripting.Dictionary
    By girish.talele in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-21-2012, 06:32 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