Excel VBA function take 2 different data type and loop through collection of class module -

I do sub work but get #VRL! Change the function after the error in the sub-function

Get_Tier2 (ByVal myCategory string, ByVal as myMSRP double) Dim ProductTiers Collection Dim pt ProductTier dim result string Dim CIDTierCount Integer CIDTierCount = ThisWorkbook As As As As Worksheet ("CIDTier") range. ("A: A") Set CurrentRegion.Rows.Count for ProductTiers = New Collection I = 2 CIDTierCount Point = New ProductTier pt.Category = ThisWorkbook.Worksheets ("CIDTier"). Rows (i) .Columns ("b") pt.Tier = ThisWorkbook.Worksheets ("CIDTier") Rows (i) .Columns ('C') pt.MSRP_Low = ThisWorkbook.Worksheets ("CIDTier"). Rows (I). Column ("D") pt.MSRP_High = ThisWorkbook.Worksheets ("CIDTER"). Rows (i). Add the next to each PT in the ProductTiers.ProductTiers ("e") if StrComp (pt.Category, MyCategory) = 0 and pt.MSRP_Low & lt; MyMSRP and myMSRP & lt; Pt.MSRP_High then results = pt.Tier () MsgBox results otherwise 'nothing ends if the next closing sub

When I try to change the sub-function and use it in Excel, it Will return #VALUE to me! String dim ProductTiers store dim pt ProductTier dim result string in the form of the Get_Tier (ByVal myCategory string, ByVal as myMSRP double) in Excel CIDTierCount = CIDTierCount = As in the form of thisWorkbook.Worksheets ("CIDTER"). Range ("A: A"). Current regio ROS Set ProductTears = I = 2 for new collections CIDTIRT set PT = new product pt PT Category = This Workbook Worksheets ("CIDTER"). Rows (i). Column ("b") pt.Tier = ThisWorkbook.Worksheets ("CIDTier") Rows (i). Column ("c") pt.MSRP_Low = ThisWorkbook.Worksheets ("CIDTier"). Rows (i) Column ('D') pt.MSRP_High = ThisWorkbook.Worksheets ("CIDTier"). Rows (i) .Columns ('E') for each pt in ProductTiers.Add pt next ProductTiers then strComp (pt.Category, myCategory) = 0 and pt.MSRP_Low & lt; MyMSRP and myMSRP & lt; Exit Pt.MSRP_High Get_Tier = pt.Tier () function Get_Tier = "no match" end if the next end function

Class modules are following as ProductTier

String as Private PCategory in the form of Private PCategory, classified as double private property in the form of PMSRP double-private PMSRP as string private PMSRP_ (classified as string) as string category = PCategory end Property public property category (string as value) pCategory = Value End Property = pTier termination property as string tier public property let tier (value string) pTier = value end property public property (MSRP_Low) Go string MSRP_Low = pMSRP_Low termination property public property Let MSRP_Low (value as string) pMSRP_Low = value end property as public property property MSRP_High () MSRP_High = PMSRP_Get high end property public property MSRP_High (value as string) is PMSR__ = value end property

and data on the CIDTier tab Column E: 300

offender

  CIDTierCount = ThisWorkbook.Worksheets ("CIDTier"). Range ("A: A"). CurrentRegion.Rows.Count  

If you throw a debug marker on that line and print it then

  thisWorkbook.Worksheets ("CIDTier"). Range ("A: A"). CurrentRegion.Rows.Count  

returns, you get the 1048576 . This is trying to fill you in an integer.

Possible Solutions

Whatever you are doing, it seems that you want to count the rows, you can try to change the following code:

  CIdTreCount = Worksheets ("CITTER"). UsedRange.Rows.Count  

Why it works

In Excel, each worksheet contains a category that is defined as the used category. This range goes from A1 to the top right column, formatted or contains data and the lowest line that is formatted or contains data.