We help IT Professionals succeed at work.

defining and comparing the value of a cell in VB

morinia
morinia asked
on
I have the following statement in VB;

PROD = Cells(stateRow, 2).Value

I am trying to compare the contents of this cell (which changes relative to the row)
The length of the contents is variable.

I would like to compare the contents of this cell to another cell (PRODX)  on a spreadsheet to use in a sumifs statement in VB.

The challenge is the the value of PRODX can be the value of PROD  & "*"  

Can someone tell me how to code a sumif in VB comparing PROD to PROD X.

This is an example of what I am using when I am matching two fields that are the same length

totYr = Round(Application.WorksheetFunction.SumIfs(Range("pAuths"), _
                              Range("pEvent"), evtType, _
                              Range("pYr"), Pyr, _
                              Range("ProdX"), PROD) / Members, 2)
Comment
Watch Question

So if I understand correctly, what you want is a function that returns the sum of the values in a range, but only counting values that have a corresponding cell equal to  PROD or PRODX?

If so, why not simply use "If (.value = PROD) Or (.value = PRODX) Then _(...)" to compare?

If instead, what you meant was that you want to compare PROD against PRODX, but also validate if PRODX = PROD & "*" and add that number even if there's an offending asterisk, that's also very simple.

To ignore the ending asterisk, simply add a condition similar to: If (PRODX = PROD) Or (PRODX = PROD & "*") Then _(...)

If VB complains or fails to recognize the second comparison, simply assign a temporary local variable in the function and let it = PROD & "*", and compare PRODX to it.

I'd also take the opportunity to point out that calls to WorksheetFunction methods are much slower than retrieving the values once, doing the comparison in full VBA code, and then setting cell values or returning if you're using a UDF.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.