CODEWITHSUNDEEP
excelvba
TJLD22
TJLD22

Reputation: 145

excel macro: ByRef argument type mismatch

i write a code as follow:

Call search(xx, yy, "APM Output", ">> State Scalars", label1)

label1:
      ...........

this the script of Sub search

Sub search(row As Variant, col As Variant, wkst As String, str As String, label_num As Name)
For row = 1 To 100
  For col = 1 To 100
    strTemp = Worksheets(wkst).Cells(row, col).Value
    If InStr(strTemp, str) <> 0 Then
        GoTo label_num
    End If
  Next
Next
End Sub

i want to call sub search(..) firstly, and then go to label1. The problem is that is says that "ByRef argument type mismatch" for label_num.What should be the correct type for label_num in Sub search(..,..,..,label_num)?

i add some original script, these are what I want to convert into a sub()

For xx = 1 To 100
    For yy = 1 To 100
        strTemp = Worksheets("APM Output").Cells(xx, yy).Value
        If InStr(strTemp, ">> State Scalars") <> 0 Then
            GoTo label1
        End If
    Next
Next
label1:
    For uu = 1 To 100
        For vv = 1 To 100
            strTemp = Worksheets("APM Output").Cells(uu, vv).Value
            If InStr(strTemp, ">> GPU LPML") <> 0 Then
                GoTo label2
            End If
        Next
    Next
label2:
    For mm = 1 To 100
        For nn = 1 To 100
            strTemp = Worksheets("APM Output").Cells(mm, nn).Value
            If InStr(strTemp, ">> Limits and Equations") <> 0 Then
                GoTo label3
            End If
        Next
    Next

Upvotes: 0

Views: 3639

Answers (2)

Larry
Larry

Reputation: 2794

As a good practise, please avoid using label at all cost!

I am going to answer you just modifying your code, I guess you want to save the values of xx,yy,uu,vv,mm,nn

the following code is how to avoid using label

Dim found1 As Boolean
Dim found2  As Boolean
Dim found3 As Boolean
  found1 = False
  found2 = False
  found3 = False
  For i = 1 To 100
   For j = 1 To 100
       strTemp = Worksheets("APM Output").Cells(i, j).Value
       If InStr(strTemp, ">> State Scalars") <> 0 And Not found1 Then
           found1 = True
           xx = i
           yy = j
       End If

       If InStr(strTemp, ">> GPU LPML") <> 0 And Not found2 Then
           found2 = True
           uu = i
           vv = j
       End If
       If InStr(strTemp, ">> Limits and Equations") <> 0 And Not found3 Then
           found3 = True
           mm = i
           nn = j
       End If
   Next j
Next i

to make your function into a sub, simply do 

Sub my_search(ByRef rowNum As Long, ByRef colNum As Long, ByVal searchString As String, ByVal height As Long, ByVal width As Long, ByRef ws As Worksheet)
Dim i As Long
Dim j As Long
Dim found As Boolean
found = False
Dim strTemp
With ws
    For i = 1 To height 
       For j = 1 To width 
           strTemp = ws.Cells(i, j).Value
           If InStr(strTemp, searchString ) <> 0 And Not found1 Then
               found = True
               rowNum = i 'assigning rowNum 
               colNum = j 'assigning colNum
               Exit For
           End If
       Next j
       If found Then
        Exit For
        End If
    Next i

End With
End Sub

and call it 3 times, for example:

my_search xx,yy,">>State Scalars", 100, 100, Worksheets("APM Output")

Upvotes: 1

brettdj
brettdj

Reputation: 55672

Your sub doesn't make much sense to me as

  1. if it finds ">> State Scalars" it exist the For loop and goes to label1
  2. if it doesn't find ">> State Scalars" reaches label1 anyway

You could use the code below

  • searches your 100 by 100 cell range in a single shot
  • either finds your partial string match or doesn't
  • it could be easily modified to stop searching after a successful (or failed) search

code

Sub ReCut()
Dim rng1 As Range
Dim rng2 As Range
Set rng2 = Worksheets("APM Output").Range("A1:CV100")
Set rng1 = rng2.Find(">> State Scalars", , xlValues, xlPart)
If Not rng1 Is Nothing Then
MsgBox "Found >> State Scalars value at " & rng1.Address(0, 0) & vbNewLine & "This is the equivalent of your exit on INSTR"
Else
End If
Set rng1 = rng2.Find(">> GPU LPML", , xlValues, xlPart)
If Not rng1 Is Nothing Then
MsgBox "Found >> GPU LPML value at " & rng1.Address(0, 0) & vbNewLine & "This is the equivalent of your exit on INSTR"
Else
End If
End Sub

Upvotes: 3

Related Questions