asked on

How to Loop through column, identify cells with data, and set string length

I've pasted a portion of the code I've created to set the string length on specific cells, what I was hoping to find is a more efficient way to loop through the data. The problem I have is that the cells that need to have the string length set are separated by a variable amount of blank cells.

The existing workbook has 7 fields that need the set length of characters, but future ones will require many more. The cells that require a set length also have data occupying the F column. I'm unsure how that could be used to identify which cells need the set string length.

Sub SetStringLength()

Dim x As Integer

Worksheets("Sheet1").Activate

q = Worksheets("Sheet1").Range("D7").Text
x = Len(Worksheets("Sheet1").Range("D7").Text)
q = q & String(24 - x, " ")
Worksheets("Sheet1").Range("D7") = q

q = Worksheets("Sheet1").Range("D18").Text
x = Len(Worksheets("Sheet1").Range("D18").Text)
q = q & String(8 - x, " ")
Worksheets("Sheet1").Range("D18") = q

End Sub

Open in new window

Thanks!

ASKER CERTIFIED SOLUTION

Emenizer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eoin Ryan

You'll have to get more specific. From the code you've posted to seem to be making D7, 24 characters long, while D18, 8 characters long.
What are the "rules" you want to specify?

as for looing through your sheet I generally use "usedrange.rows.count" and "usedrange.columns.count" to find how long and wide the used space is on the sheet, then use a for loop to go through each cell.

lastly, if this is moderately complicated scenario 125 points may not get your many responses, as evidenced so far.

K_R_M

ASKER

Emenizer,
Your code worked out very well, I hadn't realized I included the 24 char. long set length; that was the outlier for the work, all the remainders are 8

EoDawg,
In retrospect, the point value I assigned was definitely low. I see now that I can increase the value only through commenting, and have done such.

Thank you both for your help!

Eoin Ryan

Hi, Didn't mean to come across cranky ;) Just from your code there seemed there may be more to what you needed, and it had the potential to get more complex.

glad you got a solution.