Solved: Loop in Column P to Convert value

[slamet Harto]

Dear Master,
To the point : I need your help on how to loop in column P to convert a value to Character.
Let say, 1 = H, 2=I, 3=J ? till 0 =Q.
Please have a loon into the attached file.

Many thanks in advance.
Rgds, Harto

[Bob Phillips]

[vba]

Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 5 To LastRow

.Cells(i, "P").Value = ""
For j = 1 To Len(.Cells(i, "F").Value)

If Mid$(.Cells(i, "F").Value, j, 1) = "0" Then

.Cells(i, "P").Value = .Cells(i, "P").Value & "Q"
Else

.Cells(i, "P").Value = .Cells(i, "P").Value & _
Chr(Mid$(.Cells(i, "F").Value, j, 1) + 71)
End If
Next j
Next i
End With
End Sub
[/vba]

[slamet Harto]

Hi Bob,

Remember, we need to take the number and convert it starting the 5th character then leave it the last 5th character (do not convert it, keep in number format). And, try to modified the code but give me a stuck.
Please help me again.

Thanks & Rgds, harto
[VBA] For j = 5 To 10 'Len(.Cells(i, "F").Value)


If Mid$(.Cells(i, "F").Value, j, 1) = "0" Then

.Cells(i, "P").Value = .Cells(i, "P").Value & "Q"
Else

.Cells(i, "P").Value = .Cells(i, "P").Value & _
Chr(Mid$(.Cells(i, "F").Value, j, 1) + 71)
' leave it the last 5th value and combine it.
' so the value in range P5 will be JHIJKL67890
' P6 should be IHJKLM78900
' please advice[/VBA]

[Bob Phillips]

[vba]

Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = 5 To LastRow

.Cells(i, "P").Value = ""
For j = 5 To 9

If Mid$(.Cells(i, "F").Value, j, 1) = "0" Then

.Cells(i, "P").Value = .Cells(i, "P").Value & "Q"
Else

.Cells(i, "P").Value = .Cells(i, "P").Value & _
Chr(Mid$(.Cells(i, "F").Value, j, 1) + 71)
End If
Next j
.Cells(i, "P").Value = .Cells(i, "P").Value & Mid$(.Cells(i, "F").Value, 10)
Next i
End With
End Sub
[/vba]

[slamet Harto]

Hi Bob,

Work well. Thank you so much.
You are great.

Have a nice day.
Best, Harto