| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining VBA code for columns
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
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. -- HTH... Jim Thomlinson " wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
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 -- HTH... Jim Thomlinson " wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 I have a question on this one. What is "i"? Since it's not defined anywhere, does XL default to some value? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
i should have been 8. Thought I had corrected that.
-- Regards, Tom Ogilvy "davegb" wrote in message ups.com... Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 I have a question on this one. What is "i"? Since it's not defined anywhere, does XL default to some value? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Combining VBA code for columns
Tom Ogilvy wrote: i should have been 8. Thought I had corrected that. Thanks, Tom! -- Regards, Tom Ogilvy "davegb" wrote in message ups.com... Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 I have a question on this one. What is "i"? Since it's not defined anywhere, does XL default to some value? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combining two columns together | Excel Discussion (Misc queries) | |||
| combining columns | Excel Discussion (Misc queries) | |||
| Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
| combining columns all the way down | Excel Discussion (Misc queries) | |||
| combining 2+ wkbks into 1. Code needs tweaking please | Excel Programming | |||
Hybrid Mode
