Slightly offthread.
Is VBA free in Office 2003 Prof.?
Is it possible to learn VBA from the Help as I did so for Excel.
Thanks,
Jamshed.

Dave Peterson wrote in message ...
Subscript 9 usually means you're refering to something that doesn't exist.

worksheets("sheet99") when you don't have a sheet99.

You may want to indicate which line of code is causing the problem.

I think that your code is tightly tied to your data--so it would be difficult to
guess anything specific--but you did get a question about this portion in your
other thread:

With Sheets("TB")
Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
End With
If TBCodeRng(1).Address = "$A$1" Then


If TBCodeRng is in column E, how can TBCodeRng(1).address ever point to column
A?

And just a general comment:

In your "select case" portion of code, you have this:

Select Case i.Value
Case "Investments":
FirstRow = 2
FirstCol = 5
SecondCol = 7
ThirdCol = 10
Case "Bank Balances":
FirstRow = 2
FirstCol = 3
SecondCol = 4
ThirdCol = 7
Case "Share Capital & Reserves":
FirstRow = 2
FirstCol = 6
SecondCol = 10
ThirdCol = 12
End Select

I'd add "option compare text" to the top of my module--so I wouldn't have to
worry about investments or INVESTMENTS or even InVeStMeNtS.

My real question:

What happens when a cell doesn't contain one of those strings. All your Long
variables are initialized to 0. You may want to add a check:

Select Case i.Value
Case "Investments":
FirstRow = 2
FirstCol = 5
SecondCol = 7
ThirdCol = 10
Case "Bank Balances":
FirstRow = 2
FirstCol = 3
SecondCol = 4
ThirdCol = 7
Case "Share Capital & Reserves":
FirstRow = 2
FirstCol = 6
SecondCol = 10
ThirdCol = 12
Case Else
'same kind of needs to be added to the calling routine
'as you used with SetRanges
CancelA = true
exit sub
End Select

Speedy wrote:

Please see the enclosed VBA code whose purpose is to populate details sheets
from an input sheet. There is a subscript error 9 when run. The detail
sheets are called "Investments" , "Bank" , etc. If you need the excel file
please advise and I will send it. Any advise on how to shorten the code or
improve the error trapping also welcomed. The code used to work OK until I
added the Select Case logic which is aimed at determining the write to
location in the detail sheets named Investments, Bank etc.

Any ideas?

Stéphane

Option Explicit
Dim UtilityCodeRng As Range
Dim TBCodeRng As Range
Dim CancelA As Boolean

Sub ShuffleData()
CancelA = False
SetRanges
If CancelA = True Then Exit Sub
ShuffleAllData
MsgBox "Copying of data complete.", , "Done"
End Sub

Sub SetRanges()
With Sheets("TB")
Set TBCodeRng = .Range("E2", .Range("E" & Rows.Count).End(xlUp))
End With
If TBCodeRng(1).Address = "$A$1" Then
MsgBox "There are no sort codes in the TB sheet." & Chr(13) & _
"This program will terminate.", , "No TB Sheet Data"
CancelA = True
Exit Sub
End If
End Sub

Sub ShuffleAllData()
Dim i As Range
Dim FirstRow As Long 'The first data row
Dim DestRow As Long 'The actual destination row
Dim FirstCol As Long 'The actual destination Column
Dim SecondCol As Long 'The actual destination Column number 2
Dim ThirdCol As Long 'The actual destination Column number 3

Sheets("TB").Activate
For Each i In TBCodeRng

'Note that i.Value is the destination sheet name.
Select Case i.Value
Case "Investments":
FirstRow = 2
FirstCol = 5
SecondCol = 7
ThirdCol = 10
Case "Bank Balances":
FirstRow = 2
FirstCol = 3
SecondCol = 4
ThirdCol = 7
Case "Share Capital & Reserves":
FirstRow = 2
FirstCol = 6
SecondCol = 10
ThirdCol = 12
End Select

'Let's say the destination range is defined as 30 rows max.
With Sheets(i.Value)
DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row
.Cells(DestRow, FirstCol) = i.Offset(, -5)

'Test for Dr or Cr amount current year, Looks at colum C
If i.Offset(, -4) = "" Then
.Cells(DestRow, SecondCol) = i.Offset(, -3)
'Writes at Destination content of column D
Else
.Cells(DestRow, SecondCol) = i.Offset(, -4) * (-1)
'Writes content at destination of column C
End If
'Test for Dr or Cr amount for previous year (n-1), Looks at column F

If i.Offset(, -2) = "" Then
.Cells(DestRow, ThirdCol) = i.Offset(, -1)
'Writes content at destination of column F
Else
.Cells(DestRow, ThirdCol) = i.Offset(, -2) * (-1)
'Writes content at destination of column D

End If

End With
Next i
End Sub