PDA

View Full Version : Solved: Loop in Column P to Convert value



slamet Harto
09-22-2008, 12:51 AM
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
09-22-2008, 01:04 AM
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

slamet Harto
09-22-2008, 03:25 AM
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
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

Bob Phillips
09-22-2008, 03:41 AM
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

slamet Harto
09-22-2008, 10:14 PM
Hi Bob,

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

Have a nice day.
Best, Harto