Help with vb code and custom function

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
625
Code:
    Function CaTaxConstant(Constant) As Double
' Calculates Tax Constants
    Select Case Constant
        Case 0 To 41543.99:         CaTaxConstant = 1 * 0
        Case 41544 To 83087.99:     CaTaxConstant = 1 * 2908
        Case 83088 To 128799.99:    CaTaxConstant = 1 * 6232
        Case Is >= 128800:          CaTaxConstant = 1 * 10096
    End Select
    
End Function
Is an example of code used for a custom function i'm using with other code. I no longer want this to be entered into a cell. I would like to include it in my sub routine instead.
Currently I've got
Code:
Sub Enter2011CaCon()
    
    Range("B18").Select
    ActiveCell.FormulaR1C1 = "=CaTaxConstant(R[-3]C)"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
However I would like to avoid the copy/paste special value and just add the result to the cell which is currently (the following) preceeded by a For, ending with Next (loop)
Code:
        If xCell.Value = "K" Then
            xCell.Offset(0, 1) = K
        End If


-- g
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub Enter2011CaCon()
    Range("B18").Select
    With ActiveCell
        .Value = CaTaxConstant(.Offset(-3).Value)
    End With
End Sub
 

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
625
Try:

Code:
Sub Enter2011CaCon()
    Range("B18").Select
    With ActiveCell
        .Value = CaTaxConstant(.Offset(-3).Value)
    End With
End Sub

What if I didn't want to have the custom function as a function but as part of my sub routine code?
Can I include
Code:
    Select Case Constant
        Case 0 To 41543.99:         CaTaxConstant = 1 * 0
        Case 41544 To 83087.99:     CaTaxConstant = 1 * 2908
        Case 83088 To 128799.99:    CaTaxConstant = 1 * 6232
        Case Is >= 128800:          CaTaxConstant = 1 * 10096
    End Select
in my regular code?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

Code:
Sub Enter2011CaCon()
    Range("B18").Select
    With ActiveCell
        Select Case .Offset(-3).Value
            Case 0 To 41543.99:         .Value = 0
            Case 41544 To 83087.99:     .Value = 2908
            Case 83088 To 128799.99:    .Value = 6232
            Case Is >= 128800:          .Value = 10096
        End Select
    End With
End Sub
 

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
625

ADVERTISEMENT

Yes that was what i was looking for.
To go a few steps further I want to ask for this...

I have it currently searching a range of two columns A and B to row 55.
I checks for a letter or word and if that appears in a cell, the value is entered with a row offset of 1.
as an example
Code:
       If xCell.Value = "K" Then
            xCell.Offset(0, 1) = K
        End If
CODE]
 
Followed by
[CODE]
        K = CaTaxConstant(A)
So instead of the latter custom function referencing value of "A" I would like the code you provided me mixed in with the former code, and find (the predetermined) value of "A".

Its supposed to work like a vlookup without the table. I was given a function. Works great except now I would like it in code.

-- g
 

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
625

ADVERTISEMENT

From Colum A rows 1 through 30 (there's more but this is an example) I have the following
Code:
<TABLE style="WIDTH: 113pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=150><COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 113pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=150>[FONT=Calibri]Hourly Wage[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]Hours Worked[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]Wage Earned[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]Pay Period Description[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]Pay Period[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]Annual Salary[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>[FONT=Calibri]Province[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]P[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]I[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]F[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]F2[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]U1[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]HD[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]F1[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]A[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]R[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]AR[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]K[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]FT[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]K1[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]K2[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]K3[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]K4[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]T3[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]Withheld?[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]LCF[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]T1[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri][/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]CPP[/FONT]</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71 height=20>[FONT=Calibri]EI[/FONT]</TD></TR></TBODY></TABLE>


In my code I have something like this for most of what I have listed above
Code:
        If xCell.Value = "V1" Then
            xCell.Offset(0, 1) = V1
        End If

Understanding "V1" is just an example, it would reference any one of the above identifiers.
Some values are formulas, ie
Code:
        T2 = T4 + V1 + V2 - S - LCP

So the predetermined "A" value is from the list above. In the example below I'm looking for the value of K based on the case select code and the value of A that has already been determined.



I suppose I'm looking for something like this
Code:
Sub MyCoolTest()
Set TheRange = Range("A1:B55").SpecialCells(xlCellTypeConstants, xlTextValues)
    For Each xCell In TheRange
        If xCell.Value = "K" Then
            xCell.Offset(0, 1) = K
                With ActiveCell
                    Select Case .Offset(-3).Value
                        Case 0 To 41543.99:         .Value = 0
                        Case 41544 To 83087.99:     .Value = 2908
                        Case 83088 To 128799.99:    .Value = 6232
                        Case Is >= 128800:          .Value = 10096
                    End Select
                End With
        End If
    Next xCell
    
End Sub


but this doesn't seem to work.
 

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
625
I suppose I'm looking for something like this
Code:
Sub MyCoolTest()
Set TheRange = Range("A1:B55").SpecialCells(xlCellTypeConstants, xlTextValues)
    For Each xCell In TheRange
        If xCell.Value = "K" Then
            xCell.Offset(0, 1) = K
                With ActiveCell
                    Select Case .Offset(-3).Value
                        Case 0 To 41543.99:         .Value = 0
                        Case 41544 To 83087.99:     .Value = 2908
                        Case 83088 To 128799.99:    .Value = 6232
                        Case Is >= 128800:          .Value = 10096
                    End Select
                End With
        End If
    Next xCell
 
End Sub


but this doesn't seem to work.[/QUOTE]
Actually I tested this a bit more. It does appear to be working with what I have.
Thank you for your help

-- g
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I think you should be using xCell instead of ActiveCell. But the relationship between xCell and the cells you want to look up/update is unclear.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,325
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top