VBA Code error - Invalid procedure call or argument

M

MHamid

Active Member
Joined
Jan 31, 2013
Messages
472
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hello,

I am tweaking the below code and am getting a runtime error (Run-time error '5': Invalid Procedure call or argument". What am I missing?

VBA Code: 
Sub HierarchyAutomation()
  Dim sh1 As Worksheet
  Dim dic As Object, dic2 As Object, dic4 As Object, dicar1 As Object, dicar3 As Object, dicar5 As Object
  Dim i As Long, j As Long
  Dim lr1 As Long, lr2 As Long, lr3 As Long, lr4 As Long, cola As Long, colb As Long
  Dim a As Variant, c As Variant, e As Variant
  Dim b1 As Variant, b2 As Variant, b3 As Variant, b4 As Variant, b5 As Variant, b6 As Variant, b7 As Variant, b8 As Variant, b9 As Variant, b10 As Variant
  Dim ar1 As Variant, ar3 As Variant, ar5 As Variant
  Dim lv As String, lv2 As Variant, lv3 As Variant, lv4 As Variant
  
  Set sh1 = Sheets("DSMT")
  Set dic = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic4 = CreateObject("Scripting.Dictionary")
  Set dicar1 = CreateObject("Scripting.Dictionary")
  Set dicar3 = CreateObject("Scripting.Dictionary")
  Set dicar5 = CreateObject("Scripting.Dictionary")
  
  'data Mapping
  lr1 = sh1.Range("GT" & Rows.Count).End(xlUp).Row
  b1 = sh1.Range("HL3:HL" & lr1).Value 'Managed Segment (Home)- NodeNumber
  b3 = sh1.Range("IY3:IY" & lr1).Value 'Managed Segment (Impacted)-Node Number
  b5 = sh1.Range("KM3:KM" & lr1).Value 'Accountable Executive
  b7 = sh1.Range("IE3:IE" & lr1).Value 'Managed Segment Hierarchy (Home)
  b9 = sh1.Range("JS3:JS" & lr1).Value 'Managed Segment Hierarchy (Impacted)
  
  ReDim b2(1 To lr1 - 2, 1 To 17)  'result
  ReDim b4(1 To lr1 - 2, 1 To 17)  'result
  ReDim b6(1 To lr1 - 2, 1 To 17)  'result
  ReDim b8(1 To lr1 - 2, 1 To 17)  'result
  ReDim b10(1 To lr1 - 2, 1 To 17)  'result
  
  ar1 = Array("", "AC", "F", "U", "K", "P", "AE", "CC", "AJ", "BN", "A", "BS", "BX", "AO", "AT", "BI", "AY", "BD") 'MS ID Level columns
  For i = 1 To UBound(ar1)
    'stores position 1, 2, 3... and its respective search column
    dicar1(Columns(ar1(i)).Column) = i
  Next
  
  ar3 = Array("", "DK", "DW", "DE", "CS", "CY", "DQ", "FM", "EC", "FS", "CM", "FY", "GE", "EI", "EO", "EU", "FA", "FG") 'SOEID Columns
  For i = 1 To UBound(ar3)
    'stores position 1, 2, 3... and its respective search column
    dicar3(Columns(ar3(i)).Column) = i
  Next
    
  ar5 = Array("", "AA", "G", "V", "L", "Q", "AF", "CD", "AK", "BO", "B", "BT", "BY", "AP", "AU", "BJ", "AZ", "BE") 'MS ID Name
  For i = 1 To UBound(ar5)
    'stores position 1, 2, 3... and its respective search column
    dicar5(Columns(ar5(i)).Column) = i
  Next
  
  'data DSMT - Managed Segment ID Range
  lr2 = sh1.Cells.Find("*", , xlValues, xlPart, , xlPrevious).Row
  lr4 = sh1.Cells.Find("*", , xlValues, xlPart, , xlPrevious).Row
  a = sh1.Range("A4", sh1.Range("CC" & lr2)).Value
  e = sh1.Range("A4", sh1.Range("CF" & lr4)).Value
  
  'stores MS_Level_ID in dictionary and the column to which it belongs
  For j = 1 To UBound(a, 2) Step 3
    For i = 1 To UBound(a, 1)
      If a(i, j) = "" Then Exit For
      dic(a(i, j)) = j + 0      'move 0 because start in column A
    Next
  Next
  
  'stores MS_Level_Name in dictionary and the column to which it belongs
  For j = 1 To UBound(e, 2) Step 3
    For i = 1 To UBound(e, 1)
      If e(i, j) = "" Then Exit For
      dic4(e(i, j)) = j + 1     'move 1 because start in column B
    Next
  Next
  
  'check column "HL" - Managed Segment (Home)- NodeNumber
  For i = 1 To UBound(b1, 1)
    lv = Replace(b1(i, 1), ")", "(")
    lv2 = Split(lv, "(")
    For Each lv2 In Split(lv, "(")
      If dic.exists("(" & lv2 & ")") Then
        cola = dic("(" & lv2 & ")")     'gets column to which it belongs
        colb = dicar1(cola)             'gets the column where the x is to be placed
        b2(i, colb) = "X"
      End If
    Next
  Next
  
  'check column "IY" - Managed Segment (Impacted)-Node Number
  For i = 1 To UBound(b3, 1)
    lv = Replace(b3(i, 1), ")", "(")
    lv2 = Split(lv, "(")
    For Each lv2 In Split(lv, "(")
      If dic.exists("(" & lv2 & ")") Then
        cola = dic("(" & lv2 & ")")     'gets column to which it belongs
        colb = dicar1(cola)             'gets the column where the x is to be placed
        b4(i, colb) = "X"
      End If
    Next
  Next
  
  'check column "IE" - Managed Segment Hierarchy (Home)
  For i = 1 To UBound(b7, 1)
    'lv3 = Replace(b7(i, 1), "--", "--")
    'lv4 = Split(lv3, "--")
    'For Each lv4 In Split(lv3, "--")
    '  If dic4.exists("--" & lv4 & "--") Then
    '    cola = dic4("--" & lv4 & "--")     'gets column to which it belongs
    '    colb = dicar5(cola)             'gets the column where the x is to be placed
    '    b7(i, colb) = "X"
    '  End If
    'Next
    lv3 = Replace(b7(i, 1), "--", "--")
    lv4 = Split(lv3, "--")
    For Each lv3 In Split(lv3, "--")
        If dic4.exists(lv4) Then
          cola = dic4(lv4)
          colb = dicar5(cola)
          b7(i, colb) = "X"
        End If
    Next
  Next
  
  'check column "JS" - Managed Segment Hierarchy (Impacted)
 ' For i = 1 To UBound(b9, 1)
 '   lv3 = Replace(b9(i, 1), "--", "--")
 '   lv4 = Split(lv3, "--")
 '   For Each lv4 In Split(lv3, "--")
 '     If dic4.exists("--" & lv4 & "--") Then
 '       cola = dic4("--" & lv4 & "--")     'gets column to which it belongs
 '       colb = dicar5(cola)             'gets the column where the x is to be placed
 '       b9(i, colb) = "X"
 '     End If
 '   Next
 ' Next
  
  'data DSMT-SOEID List
  lr3 = sh1.Cells.Find("*", , xlValues, xlPart, , xlPrevious).Row
  c = sh1.Range("CM4", sh1.Range("GE" & lr3)).Value
  
  'stores MS_Level_ID in dictionary and the column to which it belongs
  For j = 1 To UBound(c, 2) Step 3
    For i = 1 To UBound(c, 1)
      If c(i, j) = "" Then Exit For
      dic2(c(i, j)) = j + 90      'more 90 because start in column CM
    Next
  Next

  'check column "KM"
  For i = 1 To UBound(b5, 1)
    lv = Replace(b5(i, 1), ")", "(")
    lv2 = Split(lv, "(")
    For Each lv2 In Split(lv, "(")
      If dic2.exists("(" & lv2 & ")") Then
        cola = dic2("(" & lv2 & ")")     'gets column to which it belongs
        colb = dicar3(cola)             'gets the column where the x is to be placed
        b6(i, colb) = "X"
      End If
    Next
  Next
  
  sh1.Range("GU3").Resize(UBound(b2, 1), UBound(b2, 2)).Value = b2
  sh1.Range("IH3").Resize(UBound(b4, 1), UBound(b4, 2)).Value = b4
  sh1.Range("JV3").Resize(UBound(b6, 1), UBound(b6, 2)).Value = b6

MsgBox ("Macro is Finished")

End Sub

The error occurs in this section. More specifically where it states "If dic4.exists(lv4) Then".
The data for variant b7 has the business name with [#]. Each business name is separated by "--" and the business line path is separated by a ';'. Below is an example of what the data may look like (less or more business names). What I am trying to match is "business name [L#]"
business name [L6] 26395 -- business name [L5] 6 -- business name [L4] 5 -- business name [L3] 3 -- business name [L2] 20448 -- Top business name[L1] 1;business name [L7] 22440 -- business name [L6] 9905121 -- business name [L5] 5000 -- business name [L4] 4923 -- business name [L3] 25302 --business name [L2] 4921 -- Top business name [L1] 1
VBA Code: 
  For i = 1 To UBound(b7, 1)
    'lv3 = Replace(b7(i, 1), "--", "--")
    'lv4 = Split(lv3, "--")
    'For Each lv4 In Split(lv3, "--")
    '  If dic4.exists("--" & lv4 & "--") Then
    '    cola = dic4("--" & lv4 & "--")     'gets column to which it belongs
    '    colb = dicar5(cola)             'gets the column where the x is to be placed
    '    b7(i, colb) = "X"
    '  End If
    'Next
    lv3 = Replace(b7(i, 1), "--", "--")
    lv4 = Split(lv3, "--")
    For Each lv3 In Split(lv3, "--")
      [COLOR=rgb(184, 49, 47)]  [/COLOR]If dic4.exists(lv4) Then
          cola = dic4(lv4)
          colb = dicar5(cola)
          b7(i, colb) = "X"
        End If
    Next
  Next


Any help is appreciated.


Thank you,
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
MHamid said:
The error occurs in this section. More specifically where it states "If dic4.exists(lv4) Then".
Click to expand...

I would probably need some sample data to step through your code but see if this helps.

VBA Code: 
    lv3 = Replace(b7(i, 1), "--", "--")
    lv4 = Split(lv3, "--")                         ' XXX lv4 becomes an array here
    For Each lv3 In Split(lv3, "--")
        If dic4.exists(lv4) Then                ' XXX lv4 can't be used as a key because its an array
 
Upvote 0
VBA Code: 
    lv3 = Replace(b7(i, 1), "--", "--")
    lv4 = Split(lv3, "--")
    For Each lv3 In Split(lv3, "--")
        If dic4.exists(lv4) Then
          cola = dic4(lv4)
          colb = dicar5(cola)
          b7(i, colb) = "X"
        End If
    Next

Shouldn't the looping variable be lv4 instead of lv3, i.e. For Each lv4 In Split(lv3, "--")?
 
Upvote 0
rlv01 said:
VBA Code: 
    lv3 = Replace(b7(i, 1), "--", "--")
    lv4 = Split(lv3, "--")
    For Each lv3 In Split(lv3, "--")
        If dic4.exists(lv4) Then
          cola = dic4(lv4)
          colb = dicar5(cola)
          b7(i, colb) = "X"
        End If
    Next

Shouldn't the looping variable be lv4 instead of lv3, i.e. For Each lv4 In Split(lv3, "--")?
Click to expand...
Ha, yes it was supposed to be lv4.
I'm no longer getting the error message, but I'm not getting any X results I'm expecting.
As I've mentioned previously, the data for variant b7 has the business name with [#]. Each business name is separated by "--" and the business line path is separated by a ';'. Below is an example of what the data may look like (less or more business names). What I am trying to match is "business name [L#]". So I need to search for Business Name [L6] or Business Name [L7]
business name [L6] 26395 -- business name [L5] 6 -- business name [L4] 5 -- business name [L3] 3 -- business name [L2] 20448 -- Top business name[L1] 1;business name [L7] 22440 -- business name [L6] 9905121 -- business name [L5] 5000 -- business name [L4] 4923 -- business name [L3] 25302 --business name [L2] 4921 -- Top business name [L1] 1
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Macro running long
Replies
6
Views
607
MHamid
M
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
385
NdNoviceHlp
NdNoviceHlp
A
  • Solved
VBA to declare column headers and use in further code
Replies
6
Views
466
AggieJess27
A
N
  • Solved
How to Merge Multiple VBA scripts as one?
Replies
7
Views
333
StephenCrump
S
N
  • Question Question
VBA Split and Fill only doing one line at a time. Help.
Replies
2
Views
238
NeoSez
N

Forum statistics

Threads
1,224,813
Messages
6,181,109
Members
453,021
Latest member
Justyna P

Share this page

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