CODEWITHSUNDEEP
excelvba
KnutFH
KnutFH

Reputation: 13

Issue with passing parameter to function

After hours of troubleshooting, I still cannot seem to find any good solution to this problem on my own. I've never done any VBA before, so this is mostly based on trial and error.

The function extractData_test() will define some variables, and then pass them on to other functions needed to do the work. There are still some more functions, but I've left them out, since they do not take any role in my issue.

I went for this solution since I need to do an extractData() for many, many sheets. 

Function extractData_test()
   'Define variables
    Dim Token1 As String
    Dim Token2 As String
    Dim WSout As String

   'Set attributes of the lines that should be returned, and to which worksheet.
    Token1 = "TROLLEY"
    Token2 = "TP"
    WSout = "testWS2"

    Sheets(WSout).Activate
    Sheets(WSout).UsedRange.ClearContents
   'Call Functions.FromRowNum //Line removed
    Call exData(Token1, WSout, Functions.FromRowNum)
   'Call Functions.FromRowNum //Line removed
    Call exData(Token2, WSout, Functions.FromRowNum)         
End Function

The function exData() will find lines in a source sheet that matches the criteria defined by the Token attribute. It will then copy the matching lines from the source sheet to the output sheet.

I need to call the exData() twice, with different parameter, since I need to match two different criteria. There could be several more calls on exData() too.

The problem arises when pasting on the second call. I have made a parameter "FromRowNum" that I want to pass into exData() when calling it. This parameter tells the function from which line it should start pasting. The FromRowNum function will just find the last row in ActiveSheet. But I'm not sure if I have got everything right. 

Function FromRowNum()
    Set WSout = ActiveSheet
    With WSout
        Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
        FromRowNum = LastCell.Row
    End With
End Function

EDIT: I forgot to describe what is actually happening. All the functions run fine, and they give an output, but the output is wrong. The first call of exData does what I expect. But on the second call, it will paste on row 1+NumberOfRowsInResult. In my test case this means that it will paste results from the second call from row 999. What I want to happen is to paste from the first empty row (after the first call is finished).

Here is the function exData().

Function exData(Tokens, WSoutX, FromRowNumParam) 'Changed from FromRowNum to FromRowNumParam

    Dim WS As Worksheet
    Dim LastCell As Range
    Dim y As Long
    Dim x As Long
    Dim WSout As Worksheet

    'PasteFromRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

    Set WSout = Worksheets(WSoutX)
    x = 0
    xx = 0
    n = 0
    m = 0
    rownumber = inf

    Set WS = Worksheets("data")
    With WS
        Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
        y = LastCell.Row
    End With

    Dim i As Long, iMatches As Long
    Dim aTokens() As String: aTokens = Split(Tokens, "|")

    For Each cell In Sheets("data").Range("C:C")
    x = x + 1
        If x = y Then Exit For

            For i = 0 To UBound(aTokens)
            n = n + 1
                If InStr(1, cell.Value, aTokens(i), vbTextCompare) Then
                rownumber = x
                Exit For
         End If
         Next
            If rownumber = x Then Exit For
    Next

    For Each cell In Sheets("data").Range("C:C")
    xx = xx + 1
        If xx = y Then Exit For
            For j = 0 To UBound(aTokens)
            If InStr(1, cell.Value, aTokens(j), vbTextCompare) Then
                m = xx
            End If
        Next
    Next

    numrows = m - rownumber

    Sheets("data").Range(rownumber & ":" & rownumber, m & ":" & m).Copy Sheets(WSoutX).Range(FromRowNumParam& ":" & FromRowNumParam, numrows & ":" & numrows) 'Changed from FromRowNum to FromRowNumParam

End Function

SOLUTION I implemented all the changes KazJaw suggested, and got further, though I still had some problems. Please see changes added to the previous code examples.

The line

Sheets("data").Range(rownumber & ":" & rownumber, m & ":" & m).Copy Sheets(WSoutX).Range(FromRowNumParam& ":" & FromRowNumParam, numrows & ":" & numrows)

had to be changed to

Sheets("data").Range(rownumber & ":" & rownumber, m & ":" & m).Copy Sheets(WSoutX).Range(FromRowNumParam& ":" & FromRowNumParam, FromRowNumParam+numrows & ":" & FromRowNumParam+numrows)

The end of the paste range was less than the start, causing problems. Therefore the need to add FromRowNumParam+numrows

Upvotes: 1

Views: 1042

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

First of all, your Function exData should be rather built as procedure Sub exData.

Second, you don't need to call this lines Call Functions.FromRowNum as it doesn't do anything. Returned function value isn't passed anywhere.

Third, to be sure you call correct function pass parameter to exData in this way:

Call exData(Token1, WSout, Functions.FromRowNum)

Forth, which could be biggest problem. You need to change FromRowNumber parameter in this line 

Function exData(Tokens, WSoutX, FromRowNum)

to anything different like:

Function exData(Tokens, WSoutX, FromRowNumParam)

and change FromRowNum variable accordingly within the function. If not, each time you use FromRowNum variable within your function you rather call FromRowNum function instead of using value which was passed to the function.

Upvotes: 2

Related Questions