Function not passing back value

[robs3131]

Hi all,

I'm tyring to use a function to pass back a string value to the calling sub,
but it's not doing so. The value is being set correctly in the Function,
however, when it is passed back, it appears to have a value of "". The
calling sub and the function are below. Thanks for your help!

Calling sub:

Sub linkpayvalidation()
'RUNS TO MOVE ALL PAYMENT RECORDS THAT DON'T HAVE A CORRESPONDING CHECK
SUBMITTED TO A TEMP SHEET AS THESE WILL NOT _
BE USED TO CLEAR TRANSACTIONS

Dim linkpayv As String

'Clear Linkpaywip sheet
With Sheets("Link Pay WIP")
.Cells.ClearContents
End With


'Call linkpayv function with determines which case to select
Call Module36.linkpayv

Select Case linkpayv

Case "nonemissingids"
'No Linkshare records exist -- nothing needs to be done

Case "missingids"
'Linkshare records exist, at least one of which has a Merchant ID
With Sheets("Payment Sales Master")
'Filter on all records with no value in col B (Linkshare
only) and no value in col E (no merchant ID)
.AutoFilterMode = False
.Columns("A:O").AutoFilter
.Rows("1:1").AutoFilter Field:=2, Criteria1:="="
.Rows("1:1").AutoFilter Field:=5, Criteria1:="="
'Clear A1 to help with moving of records to WIP sheet
.Range("A1").ClearContents
.Range(.Range("A1").End(xlDown),
..Range("A65536").End(xlUp).Offset(0, 255)).Copy
With Sheets("Link Pay WIP")
.Range("A1").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
'Clear cells just copied over
.Range(.Range("A1").End(xlDown),
..Range("A65536").End(xlUp).Offset(0, 255)).ClearContents
.Range("A1").Value = "Date"
'Sort out blank rows
.AutoFilterMode = False
.Columns("A:O").AutoFilter
.Cells.Sort Key1:=.Range("A2"), Order1:=xlAscending,
Header:=xlYes, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With

End Select

End Sub


Function being called:

Public Function linkpayv() As String
'CALCULATES WHETHER OR NOT THERE ARE PAYMENT RECORDS THAT NEED TO BE
MOVED TO WIP SHEET (THESE ARE RECORDS THAT _
AREN'T GOING TO GO THROUGH TRANSACTION CLEARING)

Dim strpayvcase As String

'Put in sumif formula into Payment Sales Master to identify all records
needing to be moved to temp sheet
With Sheets("Payment Sales Master")
.AutoFilterMode = False
.Columns("A:O").AutoFilter
'Filter on blanks of column B (column B is only populated in
Performics records)
.Rows("1:1").AutoFilter Field:=2, Criteria1:="="
'Filter on blanks of column E (column E is only populated by
merchant ID numbers for Linkshare records only
.Rows("1:1").AutoFilter Field:=5, Criteria1:="="
If .Range("A65536").End(xlUp).Address() <> .Range("A1").Address() Then
strpayvcase = "missingids"
Else
strpayvcase = "nonemissingids"
End If
.AutoFilterMode = False
.Columns("A:O").AutoFilter
End With

linkpayv = strpayvcase


End Function

 

[Gary''s Student]

You need to assign the output of the function to a variable in the main
routine. You can't just call it like a sub.

 

[Tim Williams]

dim s as String

s = linkpayv()
select case s

.....etc


Tim

 

[robs3131]

Thanks! I updated the code of the calling procedure to below -- it works
now. But I would like to know if this is the best way to write this
code...I'm afraid that maybe I'm leaving something out which would result in
the macro running correctly most of the time, but not all of the time. I
declared 'lpv' as a public variable as I'm going to call it in other
procedures within the module.

Public lpv As String

Sub linkpayvalidation()
'RUNS TO MOVE ALL PAYMENT RECORDS THAT DON'T HAVE A CORRESPONDING CHECK
SUBMITTED TO A TEMP SHEET AS THESE WILL NOT _
BE USED TO CLEAR TRANSACTIONS

Dim lpv As String

'Clear Linkpaywip sheet
With Sheets("Link Pay WIP")
.Cells.ClearContents
End With


'Call linkpayv function with determines which case to select
lpv = Module36.linkpayv

Select Case lpv

Case "nonemissingids"
'No Linkshare records exist -- nothing needs to be done

Case "missingids"
'Linkshare records exist, at least one of which has a Merchant ID
With Sheets("Payment Sales Master")
'Filter on all records with no value in col B (Linkshare
only) and no value in col E (no merchant ID)
.AutoFilterMode = False
.Columns("A:O").AutoFilter
.Rows("1:1").AutoFilter Field:=2, Criteria1:="="
.Rows("1:1").AutoFilter Field:=5, Criteria1:="="
'Clear A1 to help with moving of records to WIP sheet
.Range("A1").ClearContents
.Range(.Range("A1").End(xlDown),
..Range("A65536").End(xlUp).Offset(0, 255)).Copy
With Sheets("Link Pay WIP")
.Range("A1").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
'Clear cells just copied over
.Range(.Range("A1").End(xlDown),
..Range("A65536").End(xlUp).Offset(0, 255)).ClearContents
.Range("A1").Value = "Date"
'Sort out blank rows
.AutoFilterMode = False
.Columns("A:O").AutoFilter
.Cells.sort Key1:=.Range("A2"), Order1:=xlAscending,
Header:=xlYes, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End With

End Select

End Sub