Commenting the below macro

nr6281

New Member
Joined
Jun 19, 2019
Messages
37
HI All,

Could someone please help me comment out the below macro on what each or a line does.

This is used to auto assign task but I am trying to tweak few things but dont know where exactly i need to change things up. If someone can help me comment the line for example 'This splits colums or 'this assigns name would be nice.

Code:
[COLOR=#333333]Sub test()[/COLOR][COLOR=#3E3E3E][COLOR=#333333][INDENT]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">    Dim a, i As Long, dic As Object, dicAgnt As Object
    Dim r As Range, LastR As Range, flg As Boolean
    Set dic = CreateObject("Scripting.Dictionary")
    Set dicAgnt = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("tasklist").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If a(i, 3) = "Yes" Then
            If Not dicAgnt.exists(a(i, 2)) Then
                ReDim w(1 To 1)
            Else
                w = dicAgnt(a(i, 2))(0)
                ReDim Preserve w(1 To UBound(w) + 1)
            End If
            w(UBound(w)) = a(i, 1)
            dicAgnt(a(i, 2)) = Array(w, 0)
        End If
    Next
    Sheets("master").Cells.Clear
    With Sheets("raw data").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        a = .Columns("s").Value
        For i = 2 To UBound(a, 1)
            If Not dic.exists(a(i, 1)) Then
                dic(a(i, 1)) = Empty
                If Not Evaluate("isref('" & a(i, 1) & "'!a1)") Then
                    Sheets.Add(, Sheets(Sheets.Count)).Name = a(i, 19)
                End If
                Sheets(a(i, 1)).Cells.Clear
                .AutoFilter 19, a(i, 1)
                Union(.Columns("A:B"), .Columns("E"), .Columns("H"), _
                .Columns("J"), .Columns("N:O"), .Columns("R:V")).Copy
                With Sheets(a(i, 1)).Cells(1)
                    .PasteSpecial xlPasteFormats
                    .PasteSpecial xlPasteColumnWidths
                    .PasteSpecial xlPasteValues
                    With .CurrentRegion.Columns("i")
                        For Each r In .Cells
                           If dicAgnt.exists(r.Value) Then
                                w = dicAgnt(r.Value)
                                w(1) = w(1) + 1
                                If w(1) > UBound(w(0)) Then w(1) = 1
                                r(, 5).Value = w(0)(w(1))
                                dicAgnt(r.Value) = w
                            End If
                        Next
                    End With
                    .CurrentRegion.Offset(IIf(flg, 1, 0)).Copy
                End With
                With Sheets("master")
                    If flg Then
                        Set LastR = .Range("a" & Rows.Count).End(xlUp)(2)
                    Else
                        Set LastR = .Cells(1)
                    End If
                    LastR.PasteSpecial xlPasteFormats
                    LastR.PasteSpecial xlPasteColumnWidths
                    LastR.PasteSpecial xlPasteValues
                    flg = True
                End With
                .AutoFilter
            End If
        Next
        Application.Goto .Cells(1)
    End With
End Sub</code>
[/INDENT]

[/COLOR]


[/COLOR]
[COLOR=#3E3E3E][B][RIGHT][/RIGHT]
[/B][/COLOR]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Hi nr6281, you are asking someone to comment the code but have you had any dealings with the scripting dictionary to understand how it works?
 
Last edited:
Upvote 0
Hi,

I am using the code this is what the code does,

Splits data from Raw file to multiple sheets and assigns task based on the name of the company.

I am not sure what you mean by scripting dictionary
 
Upvote 0
That answers the question....

All of this part of the code is using the scripting dictionary

Code:
Set dic = CreateObject("[COLOR="#FF0000"]Scripting.Dictionary[/COLOR]")
    Set dicAgnt = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("tasklist").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If a(i, 3) = "Yes" Then
            If Not dicAgnt.exists(a(i, 2)) Then
                ReDim w(1 To 1)
            Else
                w = dicAgnt(a(i, 2))(0)
                ReDim Preserve w(1 To UBound(w) + 1)
            End If
            w(UBound(w)) = a(i, 1)
            dicAgnt(a(i, 2)) = Array(w, 0)
        End If

Really commenting it won't help much if you don't understand it, have a read of the links below

https://excelmacromastery.com/vba-dictionary/
http://www.snb-vba.eu/VBA_Dictionary_en.html#L_0
 
Last edited:
Upvote 0
No but there is no point commenting the scripting dictionary part unless you have a basic understanding how it works. If you tell us exactly what you are trying to amend then maybe someone can at least point you to those parts
 
Upvote 0
HI Mark,

I am trying to have the names assigned to task right after i:e (Column L) and not Column N.

I removed two columns from the original code that is

Code:
[/COLOR][COLOR=#333333]Union(.Columns("A:B"), .Columns("E"), .Columns("H"), _[/COLOR]
[COLOR=#333333]                .Columns("J"), .Columns("L"), .Columns("N:O"), .Columns("R:T")).Copy[/COLOR][COLOR=#333333]

Before it was R:V now its only R:T


 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top