TheRedCardinal
Board Regular
- Joined
- Jul 11, 2019
- Messages
- 243
- Office Version
- 365
- 2021
- Platform
- Windows
Hi all,
I have a column of data in a table that contains numbers. For various reasons relating to lookups, I need to ensure that the "number" is in fact a text field that contains exactly 6 digits, with leading zeroes for smaller values.
I have identified the column and for the sake of a quick fix, I am writing directly to the sheet (I will in turn move to an array but this fix is needed quickly and there are only 300 odd rows).
My code looks like this:
It finds everything "correctly" but the loop action (adding the 0) doesn't seem to work, so the loop just continues around the first row forever (i.e. until I stop it).
I have missed something obvious I know - can anybody spot it?
I have a column of data in a table that contains numbers. For various reasons relating to lookups, I need to ensure that the "number" is in fact a text field that contains exactly 6 digits, with leading zeroes for smaller values.
I have identified the column and for the sake of a quick fix, I am writing directly to the sheet (I will in turn move to an array but this fix is needed quickly and there are only 300 odd rows).
My code looks like this:
VBA Code:
Set CellA = WS1.Range("A1:Z1").Find("Item number")
LCol = WS1.ListObjects(1).Range.Rows.Count
Set WorkingRange = WS1.Range(Cells(2, CellA.Column), Cells(LCol, CellA.Column))
For Each ItemCell In WorkingRange
Do Until Len(ItemCell) = 6
ItemCell.Value = "0" & ItemCell.Value
Loop
Next ItemCell
It finds everything "correctly" but the loop action (adding the 0) doesn't seem to work, so the loop just continues around the first row forever (i.e. until I stop it).
I have missed something obvious I know - can anybody spot it?