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?
Sheldon LivingstonConsultantAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
I can only replicate the behaviour you describe if theOffset is 0, and it happens in both subs.
0
 
Martin LissOlder than dirtCommented:
Are you sure? Because when I do this and run Sub x, where A5 on my Sheet1 is "test", 'theRange' in both statements in the sub shows "test"

Sub x()
zzStartSection Sheets("Sheet1").Range("A5")
End Sub
Private Sub zzStartSection(theRange As Range)
Const theOffset = 1
theRange.Offset(theOffset, 0).EntireRow.Insert '  <<<<<<<<<<<<<<< Line 1a
theRange.Offset(theOffset, 0).EntireRow.Select ' <<<<<<<<<<<<<<<  Line 2a
End Sub

Open in new window

0
 
NorieVBA ExpertCommented:
What is the theOffset when you are calling each sub?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Sheldon LivingstonConsultantAuthor Commented:
I'm going to post both calling statements and procedures:

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

Open in new window


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 & ")"

Open in new window


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

Open in new window


Am I overlooking something?
0
 
aikimarkCommented:
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
0
 
Sheldon LivingstonConsultantAuthor Commented:
aikimark... why doesn't the range change in both procedures?
0
 
Sheldon LivingstonConsultantAuthor Commented:
It appears that "theRange" does NOT change unless the offset = 0...  I'll put some code in place to catch this...
0
 
Sheldon LivingstonConsultantAuthor Commented:
Thanks for the catch...
0
 
Sheldon LivingstonConsultantAuthor Commented:
I simply changed all occurrences of A5 to A4 and started the offset at 1... all is well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.