Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count cells with fill color and retain code - Mr. Umlas Can you he | Excel Discussion (Misc queries) | |||
Excel 2007 user name not coming up | Excel Discussion (Misc queries) | |||
Need VBA Code to Let User Define Active Sheet | Excel Discussion (Misc queries) | |||
Excel User Conference - Early Registration Period Coming to an End | Excel Discussion (Misc queries) | |||
New user needing help with coming up with a formula | New Users to Excel |