VBA function doesn't work properly

**sumonrezadu** · 08-28-2009, 02:27 AM

I can't understand why my rate1() function (my macro) doesn't work properly. While I'm dragging M5 cell in Sheet2 down to M14 cell it gives the wrong result. Even when I change value in Sheet1 and refresh data of pivot table in Sheet2, my function rate1() isn't updated. But my problem is solving (getting the correct result) when I'm going Edit Mode (pressing function key F2) in active Cell M5 or M6 or M7..so on and pressing Enter each time.

Is there any explanation?

**DonkeyOte** · 08-28-2009, 02:40 AM

Only one cell is ever "Active" so your UDF in it's present form can only ever return the string for the active cell, you need therefore to pass a range reference to the UDF so it knows to which data it should be referring when generating the string, eg:

Function rate1(rngValues As Range) As String
Dim rngCell As Range
For Each rngCell In rngValues.Cells
    If rngCell.Value <> "" Then rate1 = rate1 & "+" & rngCell & "*" & rngCell.Offset(4 - rngCell.Row)
Next rngCell
rate1 = Replace(rate1, "+", "", 1, 1)
End Function

Called from your cell as:

M5: =RATE1(B5:K5)
copied down

You could do the same thing with native functions if preferred...

=REPLACE(REPT("+"&B5&"*"&B$4,B5<>"")&REPT("+"&C5&"*"&C$4,C5<>"")&REPT("+"&D5&"*"&D$4,D5<>"")&REPT("+"&E5&"*"&E$4,E5<>"")&REPT("+"&F5&"*"&F$4,F5<>"")&REPT("+"&G5&"*"&G$4,G5<>"")&REPT("+"&H5&"*"&H$4,H5<>"")&REPT("+"&I5&"*"&I$4,I5<>"")&REPT("+"&J5&"*"&J$4,J5<>"")&REPT("+"&K5&"*"&K$4,K5<>""),1,1,"")

**sumonrezadu** · 08-28-2009, 09:05 AM

Thanks a lot. Now I fathom. It really help me much. Many many thanks again.
But could it be possible to get my desired result from my DataSheet? That is without generating Pivot Table how can I get desired result in another sheet using formula or function?

**DonkeyOte** · 08-28-2009, 09:18 AM

You could certainly write code to do it but IMO the PT is an effective approach given that in essence the code "could" be relatively slow given the need to either a) iterate records or b) apply filters etc...
all in all a subroutine seems a little unnecessary given the PT gives you the requisite info to populate your final output easily enough.