How to concatenate formula and string characters using function code

[vbvba]

Hi,

I have written a function code to concatenate strings and formula, which is failing after so many attempts.


Function CAT_SUM(a As Integer, b As Integer)
CAT_SUM = a - b
If CAT_SUM.Value < 0 Then
ActiveCell = "the percentage has decreased by " & CAT_SUM & ""
Else
ActiveCell = "the percentage has increased by " & CAT_SUM & ""
End If
End Function




The aim behind this code is that if I subtract A2:A65536 cell value and B2:B65536 cell value, based on the result (positive or negative) it should display in C2:C65536 value as "The value has increased by " the value" (if it is positive") or "The value has decreased by " the value" (if it is negative"), the function formula should apply to all the cells starting from C2 (depends if the value is there in A and B cells).

Please assist me in the code using function

 

[kennedy_clan]

Try:

Function CAT_SUM(a As Range, b As Range) As String
CAT_SUM = a - b
If a - b < 0 Then
CAT_SUM = "the percentage has decreased by " & CAT_SUM & ""
Else
CAT_SUM = "the percentage has increased by " & CAT_SUM & ""
End If
End Function

Hope it works!!

 

[AlphaFrog]

Put something like this in C1 to evaluate A1-B1

=IF(AND(A1<>"",B1<>""),"the percentage has " & IF(A1>B1,"increased by ","decreased by ") & ABS(A1-B1), "")

 

[stoolpidgeon]

It's not completely clear what you're trying to do.. how's this?

Function CAT_SUM(a As Range, b As Range)
Dim temp As Single
temp = a.Value - b.Value
If temp < 0 Then
    CAT_SUM = "The % has decreased by " & Math.Abs(temp) & ""
Else
    CAT_SUM = "The % has increased by " & temp & ""
End If
End Function

It's worth pointing out that it's easier to achieve this with worksheet functions but if the purpose of this exercises is to teach yourself VBA then this code should do the trick.

 

[vbvba]

Hi,

Thanks for all your assistance on providing me perfect solution, specially stoolpidgeon code has helped me to learn different way of writing code using function.
However, now I would like to know if I want to change a bit in the query which I have requested.
i.e. the result of A-B will be available in C column, based on the value in C column positive or negative the string character's (i.e. "The % has decreased by " & Math.Abs(temp) ), should appear in D column, please assist me in tweaking the code to acheive this.

 

[vbvba]

Please ignore my previous question, I have solved the issue succesfully.
Thanks for all your help which will help me to learn and improvise in VBA.