?
Solved

Excel VBA code assist

Posted on 2014-12-06
9
Medium Priority
?
241 Views
Last Modified: 2014-12-06
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?
0
Comment
Question by:classnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40484828
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
 
LVL 34

Expert Comment

by:Norie
ID: 40484830
What is the theOffset when you are calling each sub?
0
 

Author Comment

by:classnet
ID: 40484832
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 46

Expert Comment

by:aikimark
ID: 40484833
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
 

Author Comment

by:classnet
ID: 40484836
aikimark... why doesn't the range change in both procedures?
0
 
LVL 34

Accepted Solution

by:
Norie earned 2000 total points
ID: 40484837
I can only replicate the behaviour you describe if theOffset is 0, and it happens in both subs.
0
 

Author Comment

by:classnet
ID: 40484842
It appears that "theRange" does NOT change unless the offset = 0...  I'll put some code in place to catch this...
0
 

Author Closing Comment

by:classnet
ID: 40484843
Thanks for the catch...
0
 

Author Comment

by:classnet
ID: 40484847
I simply changed all occurrences of A5 to A4 and started the offset at 1... all is well.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question