Help with vb code and custom function

G

greegan

Well-known Member
Joined
Nov 18, 2009
Messages
643
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Try:

Code: 
Sub Enter2011CaCon()
    Range("B18").Select
    With ActiveCell
        .Value = CaTaxConstant(.Offset(-3).Value)
    End With
End Sub
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Combining VBA code together...need help!
Replies
3
Views
148
Fluff
Fluff
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
145
mikefisher
M
G
  • Question
Macro Code Help for multiple worksheets.
Replies
1
Views
164
Georgiboy
Georgiboy
E
  • Question
VB Help with code to copy selected rows to specified cells in another sheet.
2
Replies
12
Views
384
Excel_Assis
E
A
  • Question
Help Debugging Custom Data Label Macro
Replies
0
Views
94
arndtea
A

Forum statistics

Threads
1,214,520
Messages
6,120,008
Members
448,935
Latest member
ijat

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
Back
Top