RE: How to compare Multiple Rows and Columns to get data

[Lee Li [MSFT]]

Dear Tom,

Thank you for your posting.

According to your descritpion, you want to compare the multiple rows and
columns to get the data of withhold.
In fact I would like to recommend you use VBA to write a function to get
the data by yourself.
For exmaple, you create a fuction call calwithhold, in this fuction you
read the cell data of "Marriage Status" and "Amount subject to withhold"
and then calculate the withhold. Just copy the code and paste it to a
module. Then, you can enter a formula calwithhold(data Range) to get the
result. For more informaion of the usage of VBA, you can take a look at the
following URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/htm
l/deovrworkingwithmicrosoftexcelobjects.asp

Hope it helps. Have a nice day!

Sincerely,

Lee Li
MCSE2000, MCDBA2000
Microsoft Partner Online Support


Get Secure! - www.microsoft.com/security

====================================================
When responding to posts, please "Reply to Group" via
your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and
confers no rights.

 

[Tom Hagen Jr]

I've been trying to figure out how to do this, I did look
at the link and printed out the pages but I'm still not
getting any of the code how to work. The Help in the VBA
lacks in giving good examples or directions on how to do
anything like this.

Tom

 

[Lee Li [MSFT]]

Dear Tom,

Thank you for your update.

Here is am short sample of using VBA in Excel with Using the Cells property
We can substitute the loop counter (or other variables or expressions) for
the cell index numbers. In the following example, the variable counter is
substituted for the row index. The procedure loops through the range
C1:C20, setting to 0 (zero) any number whose absolute value is less than
0.01.

Sub RoundToZero1()
For Counter = 1 To 20
Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
Next Counter
End Sub

You can refer to the help file installed with Excel on your computer for
more usage of VBA in Excel. The file will be located in the following
folder:
C:\Program Files\Microsoft Office\Office10\1033 or C:\Program
Files\Microsoft Office\Office\1033
The file name is VBAXL10.CHM or VBAXL9.CHM (It will be different from the
Office version that you ha dinstalled).
You can find a lot of sample in this file.

If you have any other questions or concerns, please do not hesitate to let
me know.

Sincerely,

Lee Li
MCSE2000, MCDBA2000
Microsoft Partner Online Support


Get Secure! - www.microsoft.com/security

====================================================
When responding to posts, please "Reply to Group" via
your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and
confers no rights.

 

[Tom Hagen]

Sorry it took me so long to reply but I've been trying to
figure out how to do this in either Access or Excel 2000
but to much has changed and the coding I'm use to in
Access 95/97 is not working. Can someone please look at
the code and tell me whats wrong? I can also send both
the excel and access files to see what I'm trying to do
and compare.

Here is the code:


Public Function Find_Fed_Tax(MS As String, PP As String,
TI As Currency) As Currency
'MS is Marriage Status, PP is Payroll Period, TI is
Taxable Income which is figured out from the W2 form.

'Create a connection to the current Database
Dim db As ADODB.Connection
Set db = CurrentProject.Connection

'Create a Recordset from the table Federal Tax
Dim rstFT As New ADODB.Recordset
rstFT.Open "Federal Tax", db, adOpenKeyset,
adLockOptimistic, adCmdTableDirect

'Create a temp storage location for the info needed to do
the math
Dim rstMS As New ADODB.Recordset

'Using the old method of recordset to try and create the
temp numbers needed
Set rstFFT = db.OpenRecordset("Select * FROM '" & rstFFT
& "' WHERE [Marriage Status]= '" & MS & "'And [Payroll
Period]= '" & PP & "' And [Over]<='" & TI & "' And [Not
Over]>'" & TI & "'")

'Do the math for the amount to be taxed
Find_Fed_Tax = rstFFT![Income tax to withhold] + (TI -
rstFFT![Over]) * rstFFT![% to withhold]

End Function


Thanks for the help.

Tom Hagen

 

[Lee Li [MSFT]]

Dear Tom,

Thank you for your update. I am glad to hear from you again.

Unfortunately without the Excel file, I am afraid that we are able to
understand the function of your code clearly. Please provide me the
following information to clarify the issue in more detail:
1. The Excel file that you are using. (You can send the file to the
(e-mail address removed) directly)
2. Please describe the compare condition that you want to perform in more
detail based on the Excel file that you had provided.

Thank you for your cooperation. Have a nice day!

Sincerely,

Lee Li
MCSE2000, MCDBA2000
Microsoft Partner Online Support


Get Secure! - www.microsoft.com/security

====================================================
When responding to posts, please "Reply to Group" via
your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and
confers no rights.