Snabelhund
New Member
- Joined
- Nov 11, 2021
- Messages
- 20
- Office Version
- 2016
- Platform
- Windows
Hi im Trying to write a simple part in a bigger macro that will convert a set of numbers and add a hyphen between number 8 and 9 see below for examples
The original numbers are fomatted as 000000000000
What i want to achieve i a format that looks like this: 000000-0000
I have a solution that works partly, but since some of the orignal numbers in the data set are formatted/contains both numbers and text it does seem to skip adding hyphen to theese cells.
As an example the original cell formatting can also look like 00000000AA00 instead of 000000000000 as described before . The outcome i´m looking for i that all cells are formatted as 000000-0000 or 000000-AA00 which im having trouble to achieve with my code.
Ill post the part that does the formatting below, (as mentioned it is part of a bigger module/ process)
Cheers and sorry for any spelling errors (from a non english speaking country)
The original numbers are fomatted as 000000000000
What i want to achieve i a format that looks like this: 000000-0000
I have a solution that works partly, but since some of the orignal numbers in the data set are formatted/contains both numbers and text it does seem to skip adding hyphen to theese cells.
As an example the original cell formatting can also look like 00000000AA00 instead of 000000000000 as described before . The outcome i´m looking for i that all cells are formatted as 000000-0000 or 000000-AA00 which im having trouble to achieve with my code.
Ill post the part that does the formatting below, (as mentioned it is part of a bigger module/ process)
VBA Code:
Sub Workbook_Open()
Dim rngh As Range, cellh As Range
Dim myString As String
Dim Cell As Range
Dim wb As Workbook
Set rngh = Range("B2:B5000")
For Each cellh In rngh
If Not IsEmpty(cellh) Then
myString = cellh.Text
myString = Right(myString, Len(myString) - 2)
cellh.Value = myString
End If
Next cellh
On Error GoTo NoFilledCells
For Each Cell In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeConstants)
Cell.Value = Format(Replace(Cell.Value, "-", ""), "000000-0000")
Next
NoFilledCells:
Cheers and sorry for any spelling errors (from a non english speaking country)