array with numbers an percentages

[mpjohnston]

I have an array:

Dim test(1 to 4) As Variant
test(1) = 0
test(2) = 0
test(3) = 0
test(4) = 0

Then I pull some information and place them into 1 - 4. array position
1 and 2 are numbers (ie 200, 40, 54, etc). Array position 3 and 4 are
percentages (ie 16.67%, 15%, 100%, 50%, etc)

When I try adding test(3) + 16.67% I get an error of type mismatch.
Then again if cell B3 contains 16.67% and I make cell C3 =B3+0 there
is no problem

Anyone know of a work around? Should I not be using variant?

Thanks,
Mike

 

[Alan Beban]

I have an array:

Dim test(1 to 4) As Variant
test(1) = 0
test(2) = 0
test(3) = 0
test(4) = 0

Then I pull some information and place them into 1 - 4. array position
1 and 2 are numbers (ie 200, 40, 54, etc). Array position 3 and 4 are
percentages (ie 16.67%, 15%, 100%, 50%, etc)

When I try adding test(3) + 16.67% I get an error of type mismatch.
Then again if cell B3 contains 16.67% and I make cell C3 =B3+0 there
is no problem

Anyone know of a work around? Should I not be using variant?

Thanks,
Mike

The following works for me

Sub abc()
Dim test(1 To 4)
test(1) = 40
test(2) = 80
test(3) = "16%"
test(4) = "50%"
MsgBox Application.Sum(test(3), "20%")
End Sub

Alan Beban

 

[mpjohnston]

Ohhh that works very closely to what I was looking for, my cell
unfortunately comes up as "0%50.00%"

Here is a my syntax where it calculates:

If Not Evaluate(isItBlank) = 0 Then
amtCount = amtCount + 1
For i = 1 To 11
tempNumber =
"=LOOKUP(2,1/(qperiodresponseabandcall!A1:A1000=""" & breezeSearch(h) &
"""),qperiodresponseabandcall!" & cellLoc3(i) & "1:" & cellLoc3(i) &
"1000)"
tempNumber = Evaluate(tempNumber)
Range("" & cellLoc(i) & "27").Select
ActiveCell.FormulaR1C1 = tempNumber 'gives me 50.00%
groupCount(i) = groupCount(i) + tempNumber
Range("" & cellLoc(i) & "26").Select
ActiveCell.FormulaR1C1 = groupCount(i) 'gives me
0%50.00%
Next i
End If

 

[mpjohnston]

Guess I should have read your code better, the answer was right there:

changed
groupCount(i) = groupCount(i) + tempNumber

to
groupCount(i) = Application.Sum(groupCount(i), tempNumber

 

[Myrna Larson]

I think the point is that VBA must be interpreting 16% as text, whereas the
the SUM formula converts it to 0.16.