[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

perform action then move and repeat specified number of times

Posted on 2014-04-17
14
Medium Priority
?
173 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 2000 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
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.

 

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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

650 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