If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
VBA Code problem error 9
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
code to correct leap yr problem in February 2004 | ron | General Discussion | 4 | October 2nd, 2004 05:03 PM |
Mailmerge zip code problem | SamFortMyers | Mailmerge | 10 | September 8th, 2004 12:08 AM |
Problem with my TOC Field Code | Jed Sheckler | New Users | 1 | August 31st, 2004 10:59 PM |
Strange annoying problem - long chunk of code | John | Using Forms | 1 | June 10th, 2004 03:37 AM |