Get Cell Value

[Vegas01]

Morning everyone

I am trying to get this formula to work and it does not seem to like when the cells are not together

=IFERROR(GetBlueCellValue(F2,H2,J2) * D2, 0)+IFERROR(GetBlueCellValue(G2,I2,K2), 0)

I need to pick Column F or H or J and multiple by Column D and add this together with G or I or K depending which one is blue.

I have the formula working with the cells are together. but not now it is in this format.

Thanks in advance
Nikki Liddell

 

[StephenCrump]

What does "does not seem to like" mean? Are you perhaps getting a #VALUE! error?

What is GetBlueCellValue? Is it your macro? An add-in?

Is it set up to expect three arguments (or a variable number of arguments) like this? Or perhaps a single range?

 

[Vegas01]

What does "does not seem to like" mean? Are you perhaps getting a #VALUE! error? - The formula is returning nil, when I evaluate the formula, it is giving an #value error on the following parts (F2,H2,J2), (G2,I2,K2)

What is GetBlueCellValue? Is it your macro? An add-in? - it is my VBA code and the current workbooks and is working on other pages where is use a range of columns like (F2:J2).

Is it set up to expect three arguments (or a variable number of arguments) like this? Or perhaps a single range? I was hoping that the formula can look up 3 separate columns and pick up the blue on.

I hope this helps, thanks in advance.

 

[StephenCrump]

If your Function is expecting a single range argument, and you're providing three ranges, it will necessarily error.

Perhaps you could modify your code along these lines:

Function GetBlueCellValue(ParamArray rng()) As Variant

    Dim r1 As Variant, r2 As Variant
    Const MyBlue = 15773696
    
    For Each r1 In rng
        For Each r2 In r1
            If r2.Interior.Color = MyBlue Then GoTo Success
        Next r2
    Next r1

    GetBlueCellValue = CVErr(xlErrNA)
    Exit Function
    
Success:
    GetBlueCellValue = r2.Value

End Function

If you get stuck, please post your code.