Solved User defined format by VBA

November 15, 2016 at 00:15:51
Specs: Windows 64
Hey,

I am trying to transform numbers from column A (cells(i,1)) which are looked like 055778-1-012 into new numbers format: 5.57.78.012.

This task is new for me as I do not Applied user defined formation in VBA before so threfore I need some help for writing the Wright code.

I make effort to Express that is supposed to be my code but I am definetely experiencing problems with it. It simply does not Work. I am not aware of what I am missing. Could you help please?

Sub formatnumbers()

Dim i As Long, Finalcell As Variant

Range("A2").Select
Selection.End(xlDown).Select
Set Finalcell = Range("A2:" & Selection.Address)


For i = 3 To Finalcell
Cells(i, 1).Value = Format(55778 - 1 - 12, "##.##.##.000###")
Next i

End sub

I ned this since each month I receive a data with numbers in one format but I have to transform those into other to use them in another manipulations.


See More: User defined format by VBA

Report •

#1
November 16, 2016 at 05:14:18
Looks like excel does not like converting those types of strings, i suggest remove
all the "-" from your strings with ctrl+h, and run your macro again

Report •

#2
November 16, 2016 at 06:55:24
✔ Best Answer
Work this formula into your macro or use it directly in the worksheet:

=MID(A1,2,1)&"."&MID(A1,3,2)&"."&MID(A1,5,2)&"."&RIGHT(A1,3)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
November 16, 2016 at 12:28:57
Dear Derby,

Thank you for your response.

I have just tried the functions that you suggested from the worksheet, and Excel states that I might check out for symbols. In short, it doesn't work from there.

Then I inserted the equation into my macro, I obtained a bizarre result, yet which has some repeating sequence for cells numbers (or my i-s variable numbers). Such as A3 gets "...3"; A4 - "...4"; A5 - "...5" and from A10 the cells shows "1...10". Anyway it is not the desired result.

Supposing that I'd like to transform only cells (1,1), whould the code look like below?

Sub formatchange()

Range("A1").Value = Mid(i, 2, 1) & "." & Mid(i, 3, 2) & "." & Mid(i, 5, 2) & "." & Right(i, 3)

End Sub

Finally, I also tested the suggested ctrl+h and then run my original code, which resulted in distribution like "5578112,...0000000". So it is not way to solve the issue.

Any other suggestions?


Report •

Related Solutions

#4
November 17, 2016 at 05:47:19
Please keep in mind that we can't see your worksheet from where we are sitting, nor do we know how your data got into the spreadsheet or how Excel is treating it. With that said:

re: "I have just tried the functions that you suggested from the worksheet, and Excel states that I might check out for symbols. In short, it doesn't work from there."

I don't know what "Excel states that I might check out for symbols" means. All I can tell you is that with this exact string in A1...

055778-1-012

...the formula that I suggested returns this:

5.57.78.012

If there are leading or trailing spaces in your string or perhaps hidden characters, then my formula may not work. Spaces and hidden characters can sometimes occur when data is downloaded from an external application, such as a web page, etc.

re: "Supposing that I'd like to transform only cells (1,1), whould the code look like below?"

Again, the following will only work if you have the exact format shown below, with no leading spaces, trailing spaces or hidden characters.

055778-1-012

If you want to perform an operation on a variable, e.g. i, you need to assign a value to the variable.

Sub formatchange_v1()
'Assign cell value to variable
 i = Cells(1, 1)
'Build String and place in cell
  Cells(1, 1) = Mid(i, 2, 1) & "." & _
                Mid(i, 3, 2) & "." & _
                Mid(i, 5, 2) & "." & _
                Right(i, 3)
End Sub

You don't really need to use a variable, but it makes the code a little easier to read. You can work directly on the value in the cell. This will work, but it looks a little messy:

Sub formatchange_v2()
'Build String and place in cell
  Cells(1, 1) = Mid(Cells(1, 1), 2, 1) & "." & _
                Mid(Cells(1, 1), 3, 2) & "." & _
                Mid(Cells(1, 1), 5, 2) & "." & _
                Right(Cells(1, 1), 3)
End Sub


To loop this through a range of cells in Column A, try this:

Sub formatchange_v3()
'Determine last Row with data in Column A
 lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through range
  For rw = 3 To lastRw
'Assign cell value to variable
   i = Cells(rw, 1)
'Build String and place in cell
     Cells(rw, 1) = Mid(i, 2, 1) & "." & _
                    Mid(i, 3, 2) & "." & _
                    Mid(i, 5, 2) & "." & _
                    Right(i, 3)
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
November 17, 2016 at 11:59:10
Thank you Derby it helped me.

The code for the cell (1,1) works perfectly - it transforms the value from 055778-1-012 into the desired 5.57.78.012.

The code with looping throuth the rows - doesn't work. But as you provided a comprehensive explanations I slightly corrected your code and amazingly it returns the required values. I couldn't really have come up with this code, thank you again.

I modified your code since my vba marked the area with i = Cells(rw, 1) and stated that I should start debugging this part. Don't know why.

Sub formatchange_v1()

Dim i As Long
'Assign cell value to variable
For i = 1 To 138
'Here I know the number of the given cells but it is usefull to apply count.rows as you had shown

Cells(i, 2) = Mid(Cells(i, 1), 2, 1) & "." & _
Mid(Cells(i, 1), 3, 2) & "." & _
Mid(Cells(i, 1), 5, 2) & "." & _
Right(Cells(i, 1), 3)
Next i

End Sub

Or as you suggested before:

Sub formatchange_v3()

Dim lastRw As Integer, i As Integer
Dim rw As Integer

'Determine last Row with data in Column A
lastRw = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through range
For rw = 1 To lastRw
'Assign cell value to variable
'i = Cells(rw, 1)
'Build String and place in cell
Cells(rw, 4) = Mid(Cells(rw, 1), 2, 1) & "." & _
Mid(Cells(rw, 1), 3, 2) & "." & _
Mid(Cells(rw, 1), 5, 2) & "." & _
Right(Cells(rw, 1), 3)
Next
End Sub


Report •

#6
November 17, 2016 at 15:08:26
I'll go along with the fact that you "modified" my code, but "corrected" is not the right term. It worked just fine as I described.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question