Solved

perform action then move and repeat specified number of times

Posted on 2014-04-17
14
164 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now