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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.