A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VBA Code problem error 9



 
 
Thread Tools Display Modes
  #1  
Old October 8th, 2004, 07:45 AM
Speedy
external usenet poster
 
Posts: n/a
Default 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

  #2  
Old October 8th, 2004, 09:47 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

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


--

Dave Peterson

  #3  
Old October 9th, 2004, 08:05 AM
Speedy
external usenet poster
 
Posts: n/a
Default

Hello Dave, thanks for your interest.

I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in
the previuous tread) but it only means that that part of the code will not
run and should make no difference. what it is meant to do is to trap the
error where there is no data in the sort code colum which is really E.

THe module ran OK with tha code as it was though. The problem came latter
when the Select Case was introduced to depending on the detail sheet name to
copy the data from the input sheet to a specifc location on the detail sheet
as these are not alway starting at the same co-ordinates for data input.

The model gets stuck at run time with the "With Sheets(i.Value)" line.

I think the option compare text is a good idea as there are in the real live
example many more detail sheets and the way it is written it requires a
perfect match which can be problematic.

Just a question if the variables are set to zero, would it not be better to
set up these as strings?

If you want I can send you the spreadsheet as I can appreciate it is better
to have the actual data and model together.

Thanks for your help.

Stéphane


"Dave Peterson" wrote:

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


--

Dave Peterson


  #4  
Old October 9th, 2004, 12:27 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

First, I think keeping the discussion in the newsgroups is better. You get the
help of lots of people. (and my wrong answers can be corrected.)

if you get a "subscript out of range" error on a line like:

With Sheets(i.Value)

That means that the workbook that you're looking at doesn't contain a worksheet
that has the same name as what's in i (i.value).

So what's in i?
I'd put:
msgbox "****" & i.value & "****" & vblf & i.address
right before this line to see if I could see any extra spaces/spelling
differences.
(Everything between the asterisks is the .value)

I've found that when I have errors like this, it's usually a mistyped name in
the cell or I'm looking at the wrong workbook.

===
And using strings for those variables won't be helpful. Those are initialized
as empty strings ("").

And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will
cause trouble.




Speedy wrote:

Hello Dave, thanks for your interest.

I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in
the previuous tread) but it only means that that part of the code will not
run and should make no difference. what it is meant to do is to trap the
error where there is no data in the sort code colum which is really E.

THe module ran OK with tha code as it was though. The problem came latter
when the Select Case was introduced to depending on the detail sheet name to
copy the data from the input sheet to a specifc location on the detail sheet
as these are not alway starting at the same co-ordinates for data input.

The model gets stuck at run time with the "With Sheets(i.Value)" line.

I think the option compare text is a good idea as there are in the real live
example many more detail sheets and the way it is written it requires a
perfect match which can be problematic.

Just a question if the variables are set to zero, would it not be better to
set up these as strings?

If you want I can send you the spreadsheet as I can appreciate it is better
to have the actual data and model together.

Thanks for your help.

Stéphane

"Dave Peterson" wrote:

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


--

Dave Peterson



--

Dave Peterson

  #5  
Old October 10th, 2004, 05:07 AM
Jamshed F. Mehta
external usenet poster
 
Posts: n/a
Default

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

  #6  
Old October 10th, 2004, 12:41 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

It's included with office--so I'm not sure I'd classify it as free bg.

I would think that a book or two would help immensely. I would liken learning
how to write code from Help to learning grammar from the dictionary.

If you've got a specific question, you'll probably be able to find an example in
help--if you're lucky enough to know where to look.

Debra Dalgleish has a big list of books for excel at:
http://www.contextures.com/xlbooks.html




"Jamshed F. Mehta" wrote:

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


--

Dave Peterson

  #7  
Old October 11th, 2004, 07:53 AM
Speedy
external usenet poster
 
Posts: n/a
Default

Hello Dave,

Good news, the adding of the message box has help me find out that the error
was in fact that I was looking up the sort code in column E where in fact the
sort data is in colum G. Dumb heah?

Logic itself was working. I have added the Case else error trapping as it
is a good idea and will probaly keep the message box as it provides some sort
of confort that things are working as one might expect.

By the way do you know off anybody who is in the accouting / audit field who
is doing similar things? Similar things being automated audit workpapers in
our lingo. I have found auditnet etc but that is more oriented to CAAT ie
auditing computer data with the help of a computer which is something else.

Thanks for your help.

"Dave Peterson" wrote:

First, I think keeping the discussion in the newsgroups is better. You get the
help of lots of people. (and my wrong answers can be corrected.)

if you get a "subscript out of range" error on a line like:

With Sheets(i.Value)

That means that the workbook that you're looking at doesn't contain a worksheet
that has the same name as what's in i (i.value).

So what's in i?
I'd put:
msgbox "****" & i.value & "****" & vblf & i.address
right before this line to see if I could see any extra spaces/spelling
differences.
(Everything between the asterisks is the .value)

I've found that when I have errors like this, it's usually a mistyped name in
the cell or I'm looking at the wrong workbook.

===
And using strings for those variables won't be helpful. Those are initialized
as empty strings ("").

And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will
cause trouble.




Speedy wrote:

Hello Dave, thanks for your interest.

I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in
the previuous tread) but it only means that that part of the code will not
run and should make no difference. what it is meant to do is to trap the
error where there is no data in the sort code colum which is really E.

THe module ran OK with tha code as it was though. The problem came latter
when the Select Case was introduced to depending on the detail sheet name to
copy the data from the input sheet to a specifc location on the detail sheet
as these are not alway starting at the same co-ordinates for data input.

The model gets stuck at run time with the "With Sheets(i.Value)" line.

I think the option compare text is a good idea as there are in the real live
example many more detail sheets and the way it is written it requires a
perfect match which can be problematic.

Just a question if the variables are set to zero, would it not be better to
set up these as strings?

If you want I can send you the spreadsheet as I can appreciate it is better
to have the actual data and model together.

Thanks for your help.

Stéphane

"Dave Peterson" wrote:

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

--

Dave Peterson



--

Dave Peterson


  #8  
Old October 11th, 2004, 08:03 AM
Speedy
external usenet poster
 
Posts: n/a
Default

Dear Dave (number two)

I have found one more issue when experimenting with the code. The first row
logic does not work as expected. I don't totally follow the logic used in
the line DestRow = .Cells(FirstRow + 29,
FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5).

The aim was to have been able to start writing at whatever the DestRow was
set to in the logic above that. What happens it always starts at row 2 ?

Can you explain that to me please?

Stephane


"Dave Peterson" wrote:

First, I think keeping the discussion in the newsgroups is better. You get the
help of lots of people. (and my wrong answers can be corrected.)

if you get a "subscript out of range" error on a line like:

With Sheets(i.Value)

That means that the workbook that you're looking at doesn't contain a worksheet
that has the same name as what's in i (i.value).

So what's in i?
I'd put:
msgbox "****" & i.value & "****" & vblf & i.address
right before this line to see if I could see any extra spaces/spelling
differences.
(Everything between the asterisks is the .value)

I've found that when I have errors like this, it's usually a mistyped name in
the cell or I'm looking at the wrong workbook.

===
And using strings for those variables won't be helpful. Those are initialized
as empty strings ("").

And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will
cause trouble.




Speedy wrote:

Hello Dave, thanks for your interest.

I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in
the previuous tread) but it only means that that part of the code will not
run and should make no difference. what it is meant to do is to trap the
error where there is no data in the sort code colum which is really E.

THe module ran OK with tha code as it was though. The problem came latter
when the Select Case was introduced to depending on the detail sheet name to
copy the data from the input sheet to a specifc location on the detail sheet
as these are not alway starting at the same co-ordinates for data input.

The model gets stuck at run time with the "With Sheets(i.Value)" line.

I think the option compare text is a good idea as there are in the real live
example many more detail sheets and the way it is written it requires a
perfect match which can be problematic.

Just a question if the variables are set to zero, would it not be better to
set up these as strings?

If you want I can send you the spreadsheet as I can appreciate it is better
to have the actual data and model together.

Thanks for your help.

Stéphane

"Dave Peterson" wrote:

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

--

Dave Peterson



--

Dave Peterson


  #9  
Old October 11th, 2004, 10:04 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

Glad you got it working (well, until I read the second message).

But no, I don't know anyone in accounting that's doing anything like this.



Speedy wrote:

Hello Dave,

Good news, the adding of the message box has help me find out that the error
was in fact that I was looking up the sort code in column E where in fact the
sort data is in colum G. Dumb heah?

Logic itself was working. I have added the Case else error trapping as it
is a good idea and will probaly keep the message box as it provides some sort
of confort that things are working as one might expect.

By the way do you know off anybody who is in the accouting / audit field who
is doing similar things? Similar things being automated audit workpapers in
our lingo. I have found auditnet etc but that is more oriented to CAAT ie
auditing computer data with the help of a computer which is something else.

Thanks for your help.

"Dave Peterson" wrote:

First, I think keeping the discussion in the newsgroups is better. You get the
help of lots of people. (and my wrong answers can be corrected.)

if you get a "subscript out of range" error on a line like:

With Sheets(i.Value)

That means that the workbook that you're looking at doesn't contain a worksheet
that has the same name as what's in i (i.value).

So what's in i?
I'd put:
msgbox "****" & i.value & "****" & vblf & i.address
right before this line to see if I could see any extra spaces/spelling
differences.
(Everything between the asterisks is the .value)

I've found that when I have errors like this, it's usually a mistyped name in
the cell or I'm looking at the wrong workbook.

===
And using strings for those variables won't be helpful. Those are initialized
as empty strings ("").

And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will
cause trouble.




Speedy wrote:

Hello Dave, thanks for your interest.

I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in
the previuous tread) but it only means that that part of the code will not
run and should make no difference. what it is meant to do is to trap the
error where there is no data in the sort code colum which is really E.

THe module ran OK with tha code as it was though. The problem came latter
when the Select Case was introduced to depending on the detail sheet name to
copy the data from the input sheet to a specifc location on the detail sheet
as these are not alway starting at the same co-ordinates for data input.

The model gets stuck at run time with the "With Sheets(i.Value)" line.

I think the option compare text is a good idea as there are in the real live
example many more detail sheets and the way it is written it requires a
perfect match which can be problematic.

Just a question if the variables are set to zero, would it not be better to
set up these as strings?

If you want I can send you the spreadsheet as I can appreciate it is better
to have the actual data and model together.

Thanks for your help.

Stéphane

"Dave Peterson" wrote:

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

--

Dave Peterson



--

Dave Peterson



--

Dave Peterson

  #10  
Old October 11th, 2004, 10:14 PM
Dave Peterson
external usenet poster
 
Posts: n/a
Default

There are multiple lines of code he

With Sheets(i.Value)
DestRow = .Cells(FirstRow + 29, FirstCol).End(xlUp).Offset(1).Row
.Cells(DestRow, FirstCol) = i.Offset(, -5)

While you're looping through your range, you're changing firstrow,
firstcol--depending on the worksheet name found in the cell on the TB worksheet.

So I put Investments in E2 (I used your original code).

Your code says to set these variables like:
Case "Investments":
FirstRow = 2
FirstCol = 5
SecondCol = 7
ThirdCol = 10

so
destrow = .cells(2+29,5).end(xlup).offset(1).row

says to start at range E31.
Then manually hit the end key and the upArrow (.end(xlup))
then drop down one row.

So it really depends on what you have in E31 and above.

Maybe it's as simple as looking at column G (=7) here, too????








Speedy wrote:

Dear Dave (number two)

I have found one more issue when experimenting with the code. The first row
logic does not work as expected. I don't totally follow the logic used in
the line DestRow = .Cells(FirstRow + 29,
FirstCol).End(xlUp).Offset(1).Row .Cells(DestRow, FirstCol) = i.Offset(, -5).

The aim was to have been able to start writing at whatever the DestRow was
set to in the logic above that. What happens it always starts at row 2 ?

Can you explain that to me please?

Stephane

"Dave Peterson" wrote:

First, I think keeping the discussion in the newsgroups is better. You get the
help of lots of people. (and my wrong answers can be corrected.)

if you get a "subscript out of range" error on a line like:

With Sheets(i.Value)

That means that the workbook that you're looking at doesn't contain a worksheet
that has the same name as what's in i (i.value).

So what's in i?
I'd put:
msgbox "****" & i.value & "****" & vblf & i.address
right before this line to see if I could see any extra spaces/spelling
differences.
(Everything between the asterisks is the .value)

I've found that when I have errors like this, it's usually a mistyped name in
the cell or I'm looking at the wrong workbook.

===
And using strings for those variables won't be helpful. Those are initialized
as empty strings ("").

And then .Cells(DestRow, ThirdCol) (with destrow and thirdcol both = "") will
cause trouble.




Speedy wrote:

Hello Dave, thanks for your interest.

I agree with your comment about the $A$1 thing. (as I'd mention to Myrna in
the previuous tread) but it only means that that part of the code will not
run and should make no difference. what it is meant to do is to trap the
error where there is no data in the sort code colum which is really E.

THe module ran OK with tha code as it was though. The problem came latter
when the Select Case was introduced to depending on the detail sheet name to
copy the data from the input sheet to a specifc location on the detail sheet
as these are not alway starting at the same co-ordinates for data input.

The model gets stuck at run time with the "With Sheets(i.Value)" line.

I think the option compare text is a good idea as there are in the real live
example many more detail sheets and the way it is written it requires a
perfect match which can be problematic.

Just a question if the variables are set to zero, would it not be better to
set up these as strings?

If you want I can send you the spreadsheet as I can appreciate it is better
to have the actual data and model together.

Thanks for your help.

Stéphane

"Dave Peterson" wrote:

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

--

Dave Peterson



--

Dave Peterson



--

Dave Peterson

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 06:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.