If statement user created function

[demuro1]

so I am so new to VB it's lame but I have the following code

Function Adjust(x)
    
If (Range("Ref!$A$16") = 2 And Range("Ref!$A$29") = 1) Then
    
    If Range(x) > 20 And Range(x) < 86 Then
        x = x + 3
    ElseIf Range(x) > 87 And Range(x) < 453 Then
        x = x + 3
    ElseIf Range(x) > 454 And Range(x) < 807 Then
        x = x + 3
    Else
        x
    End If
    
ElseIf (Range("Ref!$A$16") = 3 And Range("Ref!$A$29") <> 1) Then

    If Range(x) > 20 And Range(x) < 86 Then
        x = x + 3
    ElseIf Range(x) > 87 And Range(x) < 453 Then
        x = x + 29
    ElseIf Range(x) > 454 And Range(x) < 807 Then
        x = x + 136
    Else
        x
    End If
    


ElseIf (Range("Ref!$A$16") = 3 And Range("Ref!$A$29") = 1) Then

    If Range(x) > 20 And Range(x) < 86 Then
        x = x + 6
    ElseIf Range(x) > 87 And Range(x) < 453 Then
        x = x + 132
    ElseIf Range(x) > 454 And Range(x) < 807 Then
        x = x + 639
    Else
        x
    End If
    
Else: x

End If


End Function

My question is I know it's not correct. "x" is going to be a cell in the range C11:C96 from the provided worksheet. C11:C96 will eventually be referenced on worksheet1 in column A.

So I'm trying to make this work but I have no idea how to make excel let me use this function nor do I know if the code is correct.

can anyone help me out?

Thanks a million

[Paul]

Hi demuro,

Try this function code in a standard module (open VB Editor, click Insert -> Module, paste the code shown below). In your worksheet, use the formula

=Adjust(C11) (or any other cell reference)

Note that if you change A16 or A29, you'll have to perform a full sheet calculation (CTRL+ALT+SHIFT+F9) for any existing '=Adjust()' formulas to update properly. Also note that because you used ">" and "<" (not ">=" and "<=") there are gaps in the ranges, e.g. 21-85, 88-452.. what happens if the value is 86 or 87? The way it's coded, it will stay 86 or 87. If you need to adjust the code to be all-inclusive of the consecutive ranges, just change the numbers in the Case statements as necessary.

Function Adjust(x)
With Sheets("Ref")
If (.Range("A16").Value = 2 And .Range("A29").Value = 1) Then
    Select Case x
        Case 21 To 85, 88 To 452, 455 To 806
            Adjust = x + 3
        Case Else
            Adjust = x
    End Select
ElseIf (.Range("A16").Value = 3 And .Range("A29").Value <> 1) Then
    Select Case x
        Case 21 To 85
            Adjust = x + 3
        Case 88 To 452
            Adjust = x + 29
        Case 455 To 806
            Adjust = x + 136
        Case Else
            Adjust = x
    End Select
ElseIf (.Range("A16").Value = 3 And .Range("A29").Value = 1) Then
    Select Case x
        Case 21 To 85
            Adjust = x + 6
        Case 88 To 452
            Adjust = x + 132
        Case 455 To 806
            Adjust = x + 639
        Case Else
            Adjust = x
    End Select
End If
End With
End Function

[demuro1]

are you saying that the values wont automatically recalculate? is there any way to have them recalculate every time I make a selection? Maybe something I could place in the function to have it automatically recalculate

also thanks so much for the help, I hope my code was at least passable. Yours works perfectly I just need to see if there's a way to have it recalculate now.

Thanks again