Problem with Len() within UDF element

G'day,

The following function is to be used to change values from a trial balance prior to entry into another sheet. The trial balance displays all credit items as negative, and all debit items as positive. The sheet this is going into works differenty. Income, for example, should only have a negative number if the number is a debit balance - such as cost of goods sold. An expenditure item should be positive, unless is is something like a reimbursement. Etc., etc.

The function below takes two arguments - CellRef and TBAmount. The cell containing the CellRef is made up of either a 5 or 6 digit number (always). The first number in the five-digit number contains a column reference (therefore, the first two numbers in the six-digit number contains a column reference for column references greater than 9). The last four digits in both of these numbers contain the row reference.

Now, before going any further, I have no control over the CellRef number or the sheet to which the TBAmount is going into. All I can do is manipulate the data between the trial balance and the financial statement input.

Also, I have successfully written code that uses the CellRef to export the TBAmount into the financial statement input. All I need now is to change the trial balance figure to a negative or positive in order for it to correctly display in the financial statement spreadsheet.

Now for my problem. I am attempting to write a function that will give me the row number, and based on that row number, change the TBAmount to either a positive or negative. The problem I am having is that the following line of code returns two digits for CellRef instead of either five or six:If Len(CellRef) = 6 ThenTo prove this, I used the following line in the function:MsgBox "Length of CellRef = " & Len(CellRef)The message box comes up as "Length of CellRef = 2". Is this telling me the cell reference = 2? (i.e. if the cell for the function input is, say, "L4" - i.e. giving me the length of the cell reference rather than the cell value?) If so, how do I get the length of the value IN cell "L4"?

OK, so here is my entire function:Public Function ADJTB(CellRef As Integer, TBAmount As Double)
Dim RowNum As Integer
Dim FRAMME_No As Double
If Len(CellRef) = 5 Then
RowNum = Mid(CellRef, 2, 4)
End If
If Len(CellRef) = 6 Then
RowNum = Mid(CellRef, 3, 4)
End If
'1455 to 2077
If RowNum > 1454 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount * -1
Else
FRAMME_No = TBAmount * -1
End If
End If
'1284 to 1454
If RowNum > 1283 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount
Else
FRAMME_No = TBAmount
End If
End If
'1200 to 1283
If RowNum > 1999 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount * -1
Else
FRAMME_No = TBAmount * -1
End If
End If
'571 to 1200
If RowNum > 570 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount
Else
FRAMME_No = TBAmount
End If
End If
'493 to 570
If RowNum > 492 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount
Else
FRAMME_No = TBAmount
End If
End If
'456 to 492
If RowNum > 455 Then
FRAMME_No = "Check Individually"
End If
'337 to 455
If RowNum > 336 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount
Else
FRAMME_No = TBAmount
End If
End If
'12 to 336
If RowNum > 11 Then
If TBAmount < 0 Then
FRAMME_No = TBAmount * -1
Else
FRAMME_No = TBAmount * -1
End If
End If
ADJTB = FRAMME_No
End FunctionAny assistance would be greatly appreciated.

Thanks,

Mitchys

I would need to see how the CellRef variable is filled.

Also:
1) Use: .Value to make sure the value of a cell is used, and in the long bit code above your Else's have no function since the FRAMME+No variable is being filled with the same value.
2) All those If's could be replaced with a single Case Select.
3) You can also use the Right function to return the rightmost 4 digits instead of the Mid Function.
4) The ADJTB variable can be filled directly rather than use the interum FRAMME_No variable.

I would need to see how the CellRef variable is filled.G'day Kluz, and thanks for your ideas. I haven't done much with Case Select, but will pursue that idea.

As far as how CellRef is filled, it is input like a normal Excel function (i.e. by using the function in a cell formula). For example, I might have 10023 in cell C5, and -1,258,659.25 in cell D5. (The number in cell D5 is a revenue amount, and should therefore have the "-" removed.) The target worksheet is to receive this number into column L, row 23 (column numbers are relative in that L is the first column to receive data input - it is Programme 1).

Cell C33 might have 101253, and D33 have 895,654 in it. As this is an asset revaluation, the figure in D33 is a downard adjustment, and so needs to be input as a negative number. This is to go into the target sheet in column V (i.e. Programme 9, therefore ten columns to the right of column L). The data is to go into row 1253.

The reason for this is that the trial balance figures are not straight out of our accounting package, but is a consolidated trial balance in the form of a pivot table. The reason for this is that our accounting package is a number-based system which feeds heavily from an annual budget. In other words, a budget is set up at the start of each year, and all major purchases/works/services are given a Ledger Number. Within those ledger numbers, each job is to have a different number. As there can be thousands of jobs per year, the trial balance is put into a pivot table to simplify the data for input into the financial statement worksheet.

If this sounds confusing, it is. I have recently taken up this position (Management Accountant) and am still learning how they do things. I suspect that the process from the trial balance to the pivot table could be streamlined as well using vba or vb, but I haven't worked on that side of the data process yet.

Again, thanks very much for your input. While I have been messing around (mostly messing up!) vba for a while now, many things are still new to me. Your patience is appreciated.

Regards,

Mitchys

G'day Kluz,

Here is my code now, but it still has problems (and please note I haven't used the Case Select method before)!Public Function ADJTB(CellRef As Integer, TBAmount As Double)
Dim RowNum As Integer
RowNum = Right(CellRef, 4)
Select Case RowNum
Case Is >= 1454
TBAmount = TBAmount * -1
Case Is >= 1283
TBAmount = TBAmount
Case Is >= 1199
TBAmount = TBAmount * -1
Case Is >= 492
TBAmount = TBAmount
Case Is >= 455
TBAmount = TBAmount * -1
Case Is >=336
TBAmount = TBAmount
Case Is >= 12
TBAmount = TBAmount * -1
End Select
End FunctionAlso, regarding your comment below, I don't know how to use .value to get the cell value from CellRef:1) Use: .Value to make sure the value of a cell is usedI have tried the following without success:
Dim intCellRf As Integer
intCellRf = Range(CellRef).ValueRowNum = Right(CellRef.value, 4)Thank you,

Mitchys

Ok, Ok, I found my problem - when I realised that the function was halting on all values greater than around 32,000, I realised I had to change all my number declarations from integer to double. :whoops:

Now the function appears to work as it is meant to - just need to confirm that the positive and negative numbers are being properly changed/left alone.

Here is my code:Public Function ADJTB(CellRef As Double, TBAmount As Double)
Dim RowNum As Double
RowNum = Right(CellRef, 4)
RowNum = RowNum
Select Case RowNum
Case Is >= 1455
TBAmount = TBAmount * -1
Case Is >= 1284
TBAmount = TBAmount
Case Is >= 1200
TBAmount = TBAmount * -1
Case Is >= 493
TBAmount = TBAmount
Case Is >= 455
TBAmount = TBAmount * -1
Case Is >= 337
TBAmount = TBAmount
Case Is >= 12
TBAmount = TBAmount * -1
End Select
ADJTB = TBAmount
End FunctionRegards,

Mitchys

Glad you were able to get things figured out. I mentioned the .Value thing since I didn't know how the CellRef variable was being initially filled.
I seem to remember a: FRAMME_No = "Check Individually" line from your first post, did that go away?

I seem to remember a: FRAMME_No = "Check Individually" line from your first post, did that go away?Yes, I re-looked at the financial statement input sheet and made an assumption as to whether these amounts had a native debit or credit value.

Again, thanks for your help.

Regards,

Mitchys