I have a User Defined Function that will move values from a summary area to detail rows in another spreadsheet. The application is an income tax calculator tool.

I have one sheet ("W2 Data") that stores information about income and taxes\, which allows for multiple W2 forms for up to 2 taxpayers. (Note for non US readers: Form W2 shows information about income, taxes paid, etc. There are five categories of interest, Federal Income Tax, FICA (Social Security), Medicare, State Tax and Local Tax). Here's what the W2 sheet looks like:

W-2 Data							
Taxpayer	Source	Gross		FIT		FICA		Medicaid	State		Local
TP1	TP1 Source1	654,321.00	53,421.00	12,345.00	0.00		6,543.00	5,432.00
TP2	TP2 Source2	135,790.00	13,579.00	12,345.00	0.00		7,531.00	642.00
TP2	TP2SourceB	24,680.00	8,000.00	2,345.00	345.00		1,123.00	321.00
TP1	TP1SourceX	13,579.00	5,000.00	1,234.00	234.00		2,358.00	654.00



and the Summary area of the W2 sheet looks like this:
Totals						
Taxpayer	Gross		FIT		FICA		Medicaid	State		Local
TP1		667,900.00	58,421.00	13,579.00	234.00		8,901.00	6,086.00
TP2		160,470.00	21,579.00	14,690.00	345.00		8,654.00	963.00
I want to move the summary values to another spreadsheet, called TaxesPaid. The problem (and the reason I need to use a UDF) is that if there is no Local tax, then I do not move the information, and if there is no data for Taxpayer 2, then I do not move data for that taxpayer. This means that the Taxes Paid sheet could have 1or 2 rows of taxes paid coming from the W2 sheet. My UDF, called CalculateDeductibleTaxes takes a taxpayer, and moves the appropriate data to the appropriate row:

Function CalculateDeductibleTaxes(Taxpayer As String) As String

Const TaxPayer1Summary = "J3"
Const TaxPayer2Summary = "J4"
Const StateOffset = 5
Const LocalOffset = 6

Const FirstTaxRow = 3				'	This needs to be a function, reflecting earlier invocations of this function
						'	However, first things first!
Dim TaxpayerID As Long
Dim SourceRow As Long
Dim DestRow As Long
Dim SourceCellAddress As String
Dim DestCellAddress As String
Dim StateTax As Currency
Dim LocalTax As Currency

Dim SourceCell As Range
Dim DestCell As Range
Dim wksResidence As Worksheet
Dim wksTaxesPaid As Worksheet
Dim wksW2 As Worksheet

'   Return value is always an empty string!
CalculateDeductibleTaxes = ""

Set wksResidence = Worksheets("Residence")
Set wksTaxesPaid = Worksheets("Taxes Paid")
Set wksW2 = Worksheets("W2 Data")

'   Taxpayer points to one of the entries in the W2 SUmmary. We transfer the taxes
'   to the TaxesPaid worksheet.
If Taxpayer = wksResidence.Range("B5").Value Then
   TaxpayerID = 1
   SourceCellAddress = TaxPayer1Summary
   DestCellAddress = "A" + Format(FirstTaxRow)
ElseIf Taxpayer = wksResidence.Range("B6") Then
   TaxpayerID = 2
   SourceCellAddress = TaxPayer2Summary
   DestCellAddress = "A" + Format(FirstTaxRow + 1)
Else
   CalculateDeductibleTaxes = "Unknown taxper: " + Taxpayer
   Exit Function
End If

'   Examine the data in the W2 summary area
Set SourceCell = wksW2.Range(SourceCellAddress)
Set DestCell = wksTaxesPaid.Range(DestCellAddress)	'	Does not seem to carry the Worksheet property with it!

StateTax = SourceCell.Offset(0, StateOffset).Value
LocalTax = SourceCell.Offset(0, LocalOffset).Value

wksTaxesPaid.Range(DestCellAddress).Offset(0, 0).Value = Taxpayer		'******	Macro fails when executing this line
wksTaxesPaid.Range(DestCellAddress).Offset(0, 1).Value = "12/31/2012"
wksTaxesPaid.Range(DestCellAddress).Offset(0, 2).Value = StateTax
wksTaxesPaid.Range(DestCellAddress).Offset(0, 3).Value = "State Tax"
wksTaxesPaid.Range(DestCellAddress).Offset(0, 6).Value = "Yes"

wksTaxesPaid.Range(DestCellAddress).Offset(1, 0).Value = Taxpayer
wksTaxesPaid.Range(DestCellAddress).Offset(1, 1).Value = "12/31/2012"
wksTaxesPaid.Range(DestCellAddress).Offset(1, 2).Value = LocalTax
wksTaxesPaid.Range(DestCellAddress).Offset(1, 3).Value = "Local Tax"
wksTaxesPaid.Range(DestCellAddress).Offset(1, 6).Value = "Yes"

End Function
I'm at a loss to figure out what could be wrong. Any ideas, insights creative suggestions would be appreciated,

Thanks,

Tony