Combining VBA code for columns

[cfatz1]

Hi-

I have the following code that I run for 8 consecutive
columns....columns I through P. As of now I am repeating this code 8
times (8 seperate blocks of code). This is obviously slowing the
process down quite a bit. My question is this: Is there a way to
combine all 8 columns into one block of code? Here is the code I am
using:

Range("I8").Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -7).Range("A1").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 7).Range("A1").Select
If ActiveCell.Value <> "" Then
l = ActiveCell.Value + l
ActiveCell.Offset(1, -7).Select
Else
rng.Activate
ActiveCell.Value = l
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -7).Select
l = 0

End If
Loop

rng.Activate
ActiveCell.Value = l
l = 0

Then I have this code for column J....

Range("J8").Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -8).Range("A1").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 8).Range("A1").Select
If ActiveCell.Value <> "" Then
l = ActiveCell.Value + l
ActiveCell.Offset(1, -8).Select
Else
rng.Activate
ActiveCell.Value = l
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -8).Select
l = 0

End If
Loop

rng.Activate
ActiveCell.Value = l
l = 0

TIA,
Chris

 

[Tom Ogilvy]

Figuring out what you are doing and rewriting it would be the way to improve
your code, but to reduce the repetative blocks:

for jj = 9 to 16
Cells(i,jj).Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -7).Range("A1").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 7).Range("A1").Select
If ActiveCell.Value <> "" Then
l = ActiveCell.Value + l
ActiveCell.Offset(1, -7).Select
Else
rng.Activate
ActiveCell.Value = l
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -7).Select
l = 0

End If
Loop

rng.Activate
ActiveCell.Value = l
l = 0
Next jj

 

[cfatz1]

Thanks Tom.

I am somewhat of a newbie and am trying to learn on the fly. I think
my main problem is the DoUntil Loop. I am going to change that to a
Nested For Loop. Do you think this is the best way to attack this? I
have a variable that stores the number of lines in the spreadsheet.

If you have any other advice, I could use it!

Thanks Again,
Chris

 

[davegb]

Figuring out what you are doing and rewriting it would be the way to improve
your code, but to reduce the repetative blocks:

for jj = 9 to 16
Cells(i,jj).Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -7).Range("A1").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(0, 7).Range("A1").Select
If ActiveCell.Value <> "" Then
l = ActiveCell.Value + l
ActiveCell.Offset(1, -7).Select
Else
rng.Activate
ActiveCell.Value = l
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Set rng = Range(ActiveCell.Address)
ActiveCell.Offset(1, -7).Select
l = 0

End If
Loop

rng.Activate
ActiveCell.Value = l
l = 0
Next jj

I have a question on this one. What is "i"? Since it's not defined
anywhere, does XL default to some value?

 

[Guest]

Changing the do until into a for loop will have almost no effect. Your
biggest issue is that you are using the active cell and selecting. Also you
do not show where you are declaring the variables. If undeclared then the
variables are defaulting to variant which is also extremely slow. What
exactly is your macro supposed to do (in simple english). We can take a stab
at cleaning it up for you by removing the selects and maybe adding some range
objects to avoid using the active cell.

 

[cfatz1]

First off....I changed the code to a For Loop. Here is what I have
now:

Range("I8").Select
Set rng = Range(ActiveCell.Address)

For Count = 0 To i
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value <> "" Then
l = ActiveCell.Value + l
Else
rng.Activate
ActiveCell.Value = l
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Set rng = Range(ActiveCell.Address)
l = 0
End If
Next Count

I have Parent and NonParent Values in a spreadsheet. The Parents sit
on top of the NonParents. What the code does is it stores the values
of the non-parents in a variable and then goes to the parents cell
address and pastes that value. Then it moves on to the next parent
value. (Every parent value is a blank cell to begin with.)

Essentially it is a subtotal in reverse order. I am trying to total
all of the nonparent values in put them in the parent cell above.

Example:

Parent - COSTCO
<This cell is blank>
NonParent - COSTCO 124 A. St. Boston, MA
500
NonParent - COSTCO 45 Fish St. New York, NY
600

What the code does is it totals to 1100 and stores it in the variable
(l in code above) then goes to the parent cell and pastes that value.
It finds the next parent cell by jumping to the next blank cell and
clearing the variable l.

Hope this makes sense. Open to all suggestions.

Also...I select EVERYTHING. It is the only way I can visualize the
code. Is there a quick tutorial on "How to get away from selecting
cells"?

Thanks!!!

Chris

 

[Guest]

This code is not a whole lot different from yours (mostly just translated).
It needs to have i defined...

Sub TotalParents()
On Error GoTo ErrorHandler
Dim rngParent As Range 'Hold the parent cell
Dim rngCurrent As Range 'Move this cell
Dim wks As Worksheet 'Define the worksheet we are on
Dim lngParentTotal As Long
Dim count As Long
Dim i As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set wks = ActiveSheet
Set rngParent = wks.Range("I8")
Set rngCurrent = rngParent

For count = 0 To i
Set rngCurrent = rngCurrent.Offset(1, 0)
If lngParentTotal <> "" Then
lngParentTotal = lngParentTotal + lngParentTotal
Else
rngParent.Value = lngParentTotal
lngParentTotal = 0
Set rngParent = rngParent.End(xlDown).Offset(1, 0)
End If
Next count
ErrorHandler:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Tom Ogilvy]

i should have been 8. Thought I had corrected that.

 

[davegb]

i should have been 8. Thought I had corrected that.

Thanks, Tom!