• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

perform action then move and repeat specified number of times

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
Patmurf
Asked:
Patmurf
  • 8
  • 6
3 Solutions
 
Saqib Husain, SyedEngineerCommented:
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
 
PatmurfAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PatmurfAuthor Commented:
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
 
PatmurfAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
PatmurfAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
PatmurfAuthor Commented:
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
 
PatmurfAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
PatmurfAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
You should accept multiple solutions, select yours as the accepted and mine as the assisted.
0
 
PatmurfAuthor Commented:
With both efforts, the problem was solved.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now