+ Reply to Thread
Results 1 to 4 of 4

Same coding couldn't use twice inside single macro

  1. #1
    Registered User
    Join Date
    01-03-2016
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    17

    Question Same coding couldn't use twice inside single macro

    Hi,

    I have below coding to use twice inside a single macro but it prompts error when it comes to second coding run which is highlighted in red color below.

    How to use this coding two times inside a same macro. Please help!!!

    Coding:
    [CODE]
    Dim Source As Worksheet: Set Source = ThisWorkbook.Worksheets("Active MPN")
    Dim Destination As Worksheet: Set Destination = ThisWorkbook.Worksheets("Active MPN")
    Dim Records As Object: Set Records = CreateObject("Scripting.Dictionary")
    Dim Data As Variant
    Dim Index As Long
    Dim Row As Integer: Row = 1

    Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2

    For Index = LBound(Data, 1) To UBound(Data, 1)
    If Records.Exists(Data(Index, 1)) Then
    Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & " | " & Data(Index, 2)
    Else
    Records.Add Data(Index, 1), Row
    Destination.Cells(Row, 1).Value2 = Data(Index, 1)
    Destination.Cells(Row, 2).Value2 = Data(Index, 2)
    Row = Row + 1
    End If
    Next Index
    Set Records = Nothing

    'Few coding run


    Data = Source.Range("A1", "B" & Source.Rows(Source.UsedRange.Rows.Count).Row).Value2

    For Index = LBound(Data, 1) To UBound(Data, 1)
    If Records.Exists(Data(Index, 1)) Then 'debug appear on this line
    Destination.Cells(Records(Data(Index, 1)), 2).Value2 = Destination.Cells(Records(Data(Index, 1)), 2).Value2 & " | " & Data(Index, 2)
    Else
    Records.Add Data(Index, 1), Row
    Destination.Cells(Row, 1).Value2 = Data(Index, 1)
    Destination.Cells(Row, 2).Value2 = Data(Index, 2)
    Row = Row + 1
    End If
    Next Index
    Set Records = Nothing
    [\CODE]
    Last edited by Yuvaraaj; 02-05-2016 at 12:11 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Same coding couldn't use twice inside single macro

    Delete the first line of this.

    Set Records = Nothing
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    01-03-2016
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    17

    Re: Same coding couldn't use twice inside single macro

    Thanks it runs perfect!!

  4. #4
    Registered User
    Join Date
    01-03-2016
    Location
    Chennai
    MS-Off Ver
    2013
    Posts
    17

    Re: Same coding couldn't use twice inside single macro

    Hi Alpha,

    I receive a error in if i do remove first line on the macro Set Records= Nothing.

    Error:
    While second Macro runs it didn't do the correct job. It combine non duplicated values.

+ 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. Replies: 1
    Last Post: 02-05-2015, 05:44 PM
  2. Limit replication of coding inside spreadsheet
    By PaulBas in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-16-2014, 04:01 PM
  3. Macro to autmate IE pages, in drodown list I couldn't select value
    By sathishdhivya in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-17-2014, 04:56 PM
  4. [SOLVED] Vb macro coding for transpose - complete coding?
    By Dharani Suresh in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 08-02-2013, 11:16 PM
  5. [SOLVED] Vb macro coding for transpose - complete coding?
    By Dharani Suresh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-02-2013, 05:04 AM
  6. [SOLVED] Tried to Build a Macro, but couldn't make it repeat for multiple rows.
    By homike2 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-06-2013, 09:37 AM
  7. Replies: 1
    Last Post: 12-21-2005, 12:10 PM

Tags for this Thread

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