Sheldon Livingston
asked on
Excel VBA code assist
I have a procedure that "closes" a section...
I call this procedure via the line below:
zzEndSection wksReport1.Range("A5")...
The procedure starts as follows:
Private Sub zzEndSection(theRange As Range, theSection As String, theOffset As Integer, theStartRow As Integer)
theRange.Offset(theOffset, 0).EntireRow.Insert <<<<<<<<<<<<<<< Line 1
theRange.Offset(theOffset, 1) = theSection & " Total" <<<<<<<<<<<<<<< Line 2
If I check the value of "theRange" in line 1 and in line 2 "theRange" is "A5".
I have a starting procedure that is called via:
zzStartSection wksReport1.Range("A5")...
The procedure starts as follows:
Private Sub zzStartSection(theRange As Range, theOffset As Integer, theVerbiage As String)
theRange.Offset(theOffset, 0).EntireRow.Insert <<<<<<<<<<<<<<< Line 1a
theRange.Offset(theOffset, 0).EntireRow.Select <<<<<<<<<<<<<<< Line 2a
When checking values, "theRange" in Line 1a is "A5" yet in Line 2a it is "A6"
Any idea why the starting and ending procedures don't treat the range the same?
I call this procedure via the line below:
zzEndSection wksReport1.Range("A5")...
The procedure starts as follows:
Private Sub zzEndSection(theRange As Range, theSection As String, theOffset As Integer, theStartRow As Integer)
theRange.Offset(theOffset,
theRange.Offset(theOffset,
If I check the value of "theRange" in line 1 and in line 2 "theRange" is "A5".
I have a starting procedure that is called via:
zzStartSection wksReport1.Range("A5")...
The procedure starts as follows:
Private Sub zzStartSection(theRange As Range, theOffset As Integer, theVerbiage As String)
theRange.Offset(theOffset,
theRange.Offset(theOffset,
When checking values, "theRange" in Line 1a is "A5" yet in Line 2a it is "A6"
Any idea why the starting and ending procedures don't treat the range the same?
What is the theOffset when you are calling each sub?
ASKER
I'm going to post both calling statements and procedures:
Calling segment:
End session
Start session:
Am I overlooking something?
Calling segment:
'End the Status
zzEndSection wksReport1.Range("A5"), currentStatus, currentRowOffset, startSubTotalRowCategory
currentRowOffset = currentRowOffset + 1
currentStatus = wksReport1.Range("A5").Offset(currentRowOffset, statusColumn - 1)
End If
currentStatus = wksReport1.Range("A5").Offset(currentRowOffset, statusColumn - 1)
startSubTotalRowStatus = wksReport1.Range("A5").Offset(currentRowOffset, 0).Row
zzStartSection wksReport1.Range("A5"), currentRowOffset, "Status: " & currentStatus
End session
Private Sub zzEndSection(theRange As Range, theSection As String, theOffset As Integer, theStartRow As Integer)
theRange.Offset(theOffset, 0).EntireRow.Insert
theRange.Offset(theOffset, 1) = theSection & " Total"
theRange.Offset(theOffset, 1).HorizontalAlignment = xlRight
theRange.Offset(theOffset, 1).Font.Bold = True
theRange.Offset(theOffset, 2) = "=SUBTOTAL(9,C" & theStartRow & ":C" & theRange.Offset(theOffset, 1).Row - 1 & ")"
Start session:
Private Sub zzStartSection(theRange As Range, theOffset As Integer, theVerbiage As String)
theRange.Offset(theOffset, 0).EntireRow.Insert
theRange.Offset(theOffset, 0).EntireRow.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Am I overlooking something?
since the insert creates a new line before the current line, Excel moves everything down. If your parameter was on A5 (row 5), the data in the cells on that row are now located on row 5
ASKER
aikimark... why doesn't the range change in both procedures?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It appears that "theRange" does NOT change unless the offset = 0... I'll put some code in place to catch this...
ASKER
Thanks for the catch...
ASKER
I simply changed all occurrences of A5 to A4 and started the offset at 1... all is well.
Open in new window