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

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


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

  • 2
1 Solution
This code should do fine.
-->loop through a range of cells
-->set string length for all non empty cells

The string length is however fixed. You could only make this variable if you build in some logic for that (either pick it up from other cells or have some logic in vba which defines the string length you want)
Sub SetStringLength()

Dim rngRange As Range
Dim rngCell As Range
Dim lngStringLength As Long

lngStringLength = 24

'define range to set string lengths
Set rngRange = Worksheets("Sheet1").Range("D7:D18")

'loop through cell of range and set string length if not empty
For Each rngCell In rngRange
    If rngCell.Value <> Empty Then
        rngCell = SetString(rngCell.Text, lngStringLength)
    End If
Next rngCell

End Sub

Function SetString(ByVal cellValue As String, ByRef preferedCellLength As Long) As String
    Dim cellValueLength As Long
    cellValueLength = Len(cellValue)
    SetString = cellValue & String(preferedCellLength - cellValueLength, " ")
End Function

Open in new window

Eoin RyanI.T. ContractorCommented:
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_MAuthor Commented:
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

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 RyanI.T. ContractorCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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