Help with vb code and custom function

G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
640
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Andrew Poulsom

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
 
G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
640
Office Version
  1. 365
Platform
  1. Windows
Andrew Poulsom said:
Try:

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

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

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
 
G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
640
Office Version
  1. 365
Platform
  1. Windows

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
 
G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
640
Office Version
  1. 365
Platform
  1. Windows

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.
 
G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
640
Office Version
  1. 365
Platform
  1. Windows
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

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.
 
You must log in or register to reply here.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,422
Messages
5,837,149
Members
430,478
Latest member
masoodb1358

Share this page

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