Solved

perform action then move and repeat specified number of times

Posted on 2014-04-17
14
169 Views
Last Modified: 2014-04-26
I need to resize columns 36 times. Basically, I start at column "F", set width to 10.43, move position 3 columns to right and repeat action 35 more times and then stop.
0
Comment
Question by:Patmurf
[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
  • 8
  • 6
14 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40007332
Try

Sub set3rdcolwidth()
Dim i as long
For i = 6 To 42 Step 3
    Cells(1, i).EntireColumn.ColumnWidth = 10.43
Next i
End Sub
0
 

Author Comment

by:Patmurf
ID: 40007424
It appears to set the column width for the target column, but it doesn't move 3 columns to the right and repeat. It does stop after a number of iterations.

I observed the action in na step-through view.
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 40007466
Sub set3rdcolwidth()
Dim i as long
For i = 6 To 42 Step 3
    Cells(1, i).EntireColumn.select
    Cells(1, i).EntireColumn.ColumnWidth = 10.43
Next i
End Sub
0
Independent Software Vendors: 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!

 

Author Comment

by:Patmurf
ID: 40008346
It sort of works. It does march through the columns and makes the width proper, however, the first action is to go to cell (1,1) when I would hope it would stay where I want it to start - in cell (1,8) - and progress from there on for the 120 iterations.

The code slightly changed is as follows:

Option Explicit
Sub set3rdcolwidth()
Dim i As Long
For i = 1 To 120 Step 3
    Cells(1, i).EntireColumn.Select
    Cells(1, i).EntireColumn.ColumnWidth = 10.43
Next i
End Sub
0
 

Author Comment

by:Patmurf
ID: 40010017
If you are still there, any idea how to hold the cursor at a particular position (column H) and have movement relative to that position and not return to cell A:1?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40010022
Sorry I missed your previous post.

I do not understand what you are trying to achieve in your last post.

hold the cursor....have movement....not return
0
 

Author Comment

by:Patmurf
ID: 40011608
Oh, sorry.
I have the cursor at the top of column "G" and want to set the column width to 10.43.  My wish is then to move 3 columns to the right (column "J") and set that column width to 10.43.

I then need to continue this movement and setting column width until I get to column "DH" at which point the code should stop. That is 57 moves in which every 3rd column starting with column "G" has it's width set. All intervening cells have nothing done to them.

I hope this is clearer. If you wish to make this easier, I can send the SS since there is nothing confidential.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40011755
The difference between this and my last code is that it will select the first cell of every third column instead of every third entire column. Is this what you want?

Sub set3rdcolwidth()
Dim i As Long
For i = 1 To 120 Step 3
    Cells(1, i).Select
    Cells(1, i).EntireColumn.ColumnWidth = 10.43
Next i
End Sub
0
 

Assisted Solution

by:Patmurf
Patmurf earned 0 total points
ID: 40012350
The code below works perfectly.

Option Explicit
Sub set3rdcolwidth()
Dim i As Long
Columns("G:G").Select
For i = 7 To 118 Step 3
    Cells(1, i).EntireColumn.Select
    Cells(1, i).EntireColumn.ColumnWidth = 10.73
Next i
End Sub


The ONLY difference is that I make sure I go to the first column I want to change with the code _ "Columns("G:G").Select".

Then I changed your code to read - "For i = 7 To 118 Step 3".
You can see the "for i = 7", I do not go to column ("A") but remain at my start point of column ("G").

The end result of this is that you have lead me in the correct direction and I accept your answer.
0
 

Author Comment

by:Patmurf
ID: 40012365
I've requested that this question be closed as follows:

Accepted answer: 0 points for Patmurf's comment #a40012350

for the following reason:

Ultimately, I needed to understand the coding provided by the expert, discover where the code was not doing what I wanted and made the fix myself.

However, without the expert, I was clueless as to how to start. The expert DID provide the framework for me to understand the code that was needed and that I had to understand a little more than I previously had to be able to make my own changes to make the code work as I wanted. I view this experience as a plus.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40012366
Since the proposed solution helped you it would be appropriate to accept it as an assisted solution and your own solution as the accepted solution.
0
 

Author Comment

by:Patmurf
ID: 40012369
I'm sorry to be such a dunce about this, but does your answer mean that I should check "accepted multiple solutions" or is there another place I should be to answer as you suggest?
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40012408
You should accept multiple solutions, select yours as the accepted and mine as the assisted.
0
 

Author Closing Comment

by:Patmurf
ID: 40024353
With both efforts, the problem was solved.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 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