Custom function with offset

[Moonbeam111]

Im trying to create a custom function that incorporates an offset. Looking at my code may make it easier to tell what I'm trying to do.

Function test (rng As Range)
    Dim cel As Range
    Dim s As Double
    For Each cel In rng
       If cel = "FALSE" Then
       s = s + cell.Offset(0, 3)
        End If
    Next cel
    test = s
End Function

I'm not sure what I'm doing wrong. I don't get any errors but the cell I'm using the function on remains as 0. Any suggestions?

 

[Joe4]

Can you show us some sample data, and walk us through a simple example, explaining what you are expecting to see?

 

[Moonbeam111]

Instead of explaining in too much detail, Ill just snapshot a portion of my spreadsheet.

As you can see, I have a checkbox next to a task that needs to be completed (there are others but this is just one). My problem is taking that crossed out task (I checked it using the checkbox) and finding the value from the offset of E10 --> G10 and adding it to G7. But only if that task is not crossed. (In this example it is, however).

Whenever a task is checked the words TRUE or FALSE are put in the same cell as the checkbox, depending on if its been checked (true) or not (false). Its hard to see because I changed the text color to blend in with the background.

If I put in =test(E10) in the formula bar on cell A1, according to my VBA code, I get a 0 instead of the desired result.

Hopefully I have made things more clear now.

 

[Joe4]

You have a few errors in your code.

First, I assume that you have already linked the value of the checkbox of the cell (that does not happen automatically).
Second, you have an error in your range declaration. You are using a range object of "cel" in your loop, but then using "cell" when offsetting.
Thirdly, column G is only two columns away from column E, not three. So you show be using 2 instead of 3 in your offset.

Your code should look like this:

Function test(rng As Range)
    Dim cel As Range
    Dim s As Double
    For Each cel In rng
       If cel = False Then
       s = s + cel.Offset(0, 2)
        End If
    Next cel
    test = s
End Function

I tested it out, and it seems to return a value for me when the check box is NOT checked (FALSE is NOT checked, TRUE is checked).

I highly recommend turning on Option Explicit for all your VBA code. This forces you to declare all your variables before using them. This is a great tool that helps you catch typos (like declaring "cel" but then trying to use "cell"). Had you had it turned on, it would have identified that typo error when you tried to use the code.
See: Option Explicit in Excel VBA

 

[Moonbeam111]

Thank you so much. It works as I was hoping it would now.

 

[Joe4]

You are welcome.
Glad I was able to help!