Problem with Bob Umlas sub total code. Coming up with user define
 

I'm hoping someone might help with this. Bob kindly gave me some code but I
keep coming up with a user define error. I have spent a lot of time trying to
figure it out but I haven't worked with "Chunk" before and my trusty manual
isn't helping. My logic is letting me down somewhat too!

Here's what I want to do. Search columns S and T and if both cells are empty
at the same time, then subtotal down in both columns until the next entry in
either column. I also want them to be bold font. Here's the script thus far..

Sub SubTots()
Dim ans As Range
Set ans = Nothing
Set rg = Range("S:T").SpecialCells(xlCellTypeBlanks)
For Each chunk In rg
If chunk.Column = 19 And IsEmpty(chunk.Offset(0, 1)) Or _
chunk.Column = 20 And IsEmpty(chunk.Offset(0, -1)) Then
If ans Is Nothing Then
Set ans = chunk
Else
Set ans = Union(ans, chunk)
End If
End If
Next
ans.Select
Application.SendKeys "%="
End Sub

Can anyone help? Any help appreciated at this stage. This little bit is
holding up a much larger project.
Cheers
Gai

Problem with Bob Umlas sub total code. Coming up with user define
 

At first glance, it appears you are using both ans and rg to reference a
range. chunk is either a cell or a range (or row), but isn't defined in the
code you provided.

At the top of your code module, do you have the following statement?
Option Explicit

"GaiGauci" wrote:

I'm hoping someone might help with this. Bob kindly gave me some code but I
keep coming up with a user define error. I have spent a lot of time trying to
figure it out but I haven't worked with "Chunk" before and my trusty manual
isn't helping. My logic is letting me down somewhat too!

Here's what I want to do. Search columns S and T and if both cells are empty
at the same time, then subtotal down in both columns until the next entry in
either column. I also want them to be bold font. Here's the script thus far..

Sub SubTots()
Dim ans As Range
Set ans = Nothing
Set rg = Range("S:T").SpecialCells(xlCellTypeBlanks)
For Each chunk In rg
If chunk.Column = 19 And IsEmpty(chunk.Offset(0, 1)) Or _
chunk.Column = 20 And IsEmpty(chunk.Offset(0, -1)) Then
If ans Is Nothing Then
Set ans = chunk
Else
Set ans = Union(ans, chunk)
End If
End If
Next
ans.Select
Application.SendKeys "%="
End Sub

Can anyone help? Any help appreciated at this stage. This little bit is
holding up a much larger project.
Cheers
Gai

Problem with Bob Umlas sub total code. Coming up with user define
 

Hi Gai

This should do what you need. It takes a slightly different view,
looks at the last used cell in Col A (making the assumption that
descriptive data is in this Column. It then moved backwards from the
bottom of Column S & T checking for empty cells . It sums downwards
not to the next entry in either column S or T but to the last sum (as
such it gathers all the figures inbetween the two sum ranges and
therefore all the figures in either S or T. Oh and of course it makes
the sum range bold. Let the group know how you get on.

Take Care

Marcus

Option Explicit

Sub SubtotalBlanks()
Dim RowCnt As Integer
Dim ColCount As Integer
Dim start As Integer
Dim i As Integer

RowCnt = Range("A" & Rows.Count).End(xlUp).Row
start = RowCnt
'Do Until RowCnt = 25
For i = RowCnt To 2 Step -1
If Range("S" & RowCnt).Value = "" And Range("T" & (RowCnt)).Value =
"" Then

For ColCount = 19 To 20 'The Sum Bit
Cells(RowCnt, ColCount).FormulaR1C1 = _
"=SUM(R" & start & "C:R" & RowCnt + 1 & "C)"
Cells(RowCnt, ColCount).Font.Bold = True 'The Bold Bit
Next ColCount

start = RowCnt - 1
Else
RowCnt = i - 1

End If
RowCnt = i
Next i
End Sub

Problem with Bob Umlas sub total code. Coming up with user def
 

No I don't. I thought reading the code I had to specify chunk but that's as
far as my thinking took me. I just tried putting in "Option Explicit" but it
now comes up with "compile error. Invalid inside procedure". Am I doing it
wrong?
Gai


"ker_01" wrote:

At first glance, it appears you are using both ans and rg to reference a
range. chunk is either a cell or a range (or row), but isn't defined in the
code you provided.

At the top of your code module, do you have the following statement?
Option Explicit

"GaiGauci" wrote:

I'm hoping someone might help with this. Bob kindly gave me some code but I
keep coming up with a user define error. I have spent a lot of time trying to
figure it out but I haven't worked with "Chunk" before and my trusty manual
isn't helping. My logic is letting me down somewhat too!

Here's what I want to do. Search columns S and T and if both cells are empty
at the same time, then subtotal down in both columns until the next entry in
either column. I also want them to be bold font. Here's the script thus far..

Sub SubTots()
Dim ans As Range
Set ans = Nothing
Set rg = Range("S:T").SpecialCells(xlCellTypeBlanks)
For Each chunk In rg
If chunk.Column = 19 And IsEmpty(chunk.Offset(0, 1)) Or _
chunk.Column = 20 And IsEmpty(chunk.Offset(0, -1)) Then
If ans Is Nothing Then
Set ans = chunk
Else
Set ans = Union(ans, chunk)
End If
End If
Next
ans.Select
Application.SendKeys "%="
End Sub

Can anyone help? Any help appreciated at this stage. This little bit is
holding up a much larger project.
Cheers
Gai

Problem with Bob Umlas sub total code. Coming up with user def
 

The Option Explicit directive must be outside of and before any
procedure (Sub or Function) and before any module-scoped variable.
Just make it the first line of code in the module and it will work.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Tue, 8 Dec 2009 20:59:01 -0800, GaiGauci
wrote:

No I don't. I thought reading the code I had to specify chunk but that's as
far as my thinking took me. I just tried putting in "Option Explicit" but it
now comes up with "compile error. Invalid inside procedure". Am I doing it
wrong?
Gai


"ker_01" wrote:

At first glance, it appears you are using both ans and rg to reference a
range. chunk is either a cell or a range (or row), but isn't defined in the
code you provided.

At the top of your code module, do you have the following statement?
Option Explicit

"GaiGauci" wrote:

I'm hoping someone might help with this. Bob kindly gave me some code but I
keep coming up with a user define error. I have spent a lot of time trying to
figure it out but I haven't worked with "Chunk" before and my trusty manual
isn't helping. My logic is letting me down somewhat too!

Here's what I want to do. Search columns S and T and if both cells are empty
at the same time, then subtotal down in both columns until the next entry in
either column. I also want them to be bold font. Here's the script thus far..

Sub SubTots()
Dim ans As Range
Set ans = Nothing
Set rg = Range("S:T").SpecialCells(xlCellTypeBlanks)
For Each chunk In rg
If chunk.Column = 19 And IsEmpty(chunk.Offset(0, 1)) Or _
chunk.Column = 20 And IsEmpty(chunk.Offset(0, -1)) Then
If ans Is Nothing Then
Set ans = chunk
Else
Set ans = Union(ans, chunk)
End If
End If
Next
ans.Select
Application.SendKeys "%="
End Sub

Can anyone help? Any help appreciated at this stage. This little bit is
holding up a much larger project.
Cheers
Gai

Problem with Bob Umlas sub total code. Coming up with user def
 

Thanks Marcus- this worked a treat!

Cheers
Gai

"marcus" wrote:

Hi Gai

This should do what you need. It takes a slightly different view,
looks at the last used cell in Col A (making the assumption that
descriptive data is in this Column. It then moved backwards from the
bottom of Column S & T checking for empty cells . It sums downwards
not to the next entry in either column S or T but to the last sum (as
such it gathers all the figures inbetween the two sum ranges and
therefore all the figures in either S or T. Oh and of course it makes
the sum range bold. Let the group know how you get on.

Take Care

Marcus

Option Explicit

Sub SubtotalBlanks()
Dim RowCnt As Integer
Dim ColCount As Integer
Dim start As Integer
Dim i As Integer

RowCnt = Range("A" & Rows.Count).End(xlUp).Row
start = RowCnt
'Do Until RowCnt = 25
For i = RowCnt To 2 Step -1
If Range("S" & RowCnt).Value = "" And Range("T" & (RowCnt)).Value =
"" Then

For ColCount = 19 To 20 'The Sum Bit
Cells(RowCnt, ColCount).FormulaR1C1 = _
"=SUM(R" & start & "C:R" & RowCnt + 1 & "C)"
Cells(RowCnt, ColCount).Font.Bold = True 'The Bold Bit
Next ColCount

start = RowCnt - 1
Else
RowCnt = i - 1

End If
RowCnt = i
Next i
End Sub
.