Debug VBA code

[Guest]

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.

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

 

[Myrna Larson]

Can you tell us on which row the error is occurring? If it's a reference to a
worksheet, that means you've spelled the name incorrectly.

 

[Guest]

Hi Myrna,

The error message simply says subscript error 9 which the debugger shows at
the start of the line saying " For Each i In TBCodeRng"" There is no further
clue ??

Happy hunting.

Cheers,

Stéphane

 

[Myrna Larson]

Try putting in some temporary debugging statements before this, like

MsgBox TBCodeRng.Address, vbokonly
MsgBox TBCodeRng.Cells.Count, vbokonly

Step through the code with F8 and report back on what the messages say.

 

[Myrna Larson]

PS: I think there's an error in your SetRanges procedure. Here's part of the
code:

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

The address will be something like $E$100 or $E$1:$E$2. The address of the
first cell in that range wil NEVER be $A$1. Maybe you intended something like

If TBCodeRng.Cells(1).Row = 1 Then

 

[Myrna Larson]

Typo there. Should have said $E$1:$E$100, not $E$100.

 

[Guest]

Hello Myrna,

I will look into this. There appears to a problem there. However, the
code ran fine with this bit as it was (even if the test logic is wrong). It
is only when I added the SelectCase i.value logic that things started going
wrong.

There might still be something in that logic, particularly with the location
of the End Select that may play a role. What do you think?

Stéphane