CODEWITHSUNDEEP
excelvba
christian
christian

Reputation: 69

Move Two characters from beginning to end of string VBA

I need to create a VBA script in excel that chanages an order number from having "CD" at the front to "CD" at the end so from "CD00001" to "00001CD"

Any help would be awesome. all of the order numbers are in Column B and start at row 5. please help.

What i have so far:

Private Sub OrderNumber_Click()
Dim Val As String
Dim EndC As Integer

EndC = Worksheets("Raw Data Upload").Range("A1048576").End(xlUp).Row

For i = 5 To EndC
    Val = Right("B" & i, Len("B" & i) - 2) & Left("B" & i, 2)
    Range("B" & i).Value = Val
Next

End Sub

This replaces the order numbers with B5, B6 and so on but if i put this function into Excel itself it works fine.

Upvotes: 2

Views: 1377

Answers (4)

BruceWayne
BruceWayne

Reputation: 23283

Simply use:

Right(Range("B" & i), Len(Range("B" & i)) - 2) & Left(Range("B" & i), 2)

An alternative is to set up the cell as a Range():

Sub t()
Dim cel As Range
Dim endC As Long
endC = Worksheets("Raw Data Upload").Range("A1048576").End(xlUp).Row

For i = 5 To endC
    Set cel = Range("B" & i)
    myVal = Right(cel, Len(cel) - 2) & Left(cel, 2)
    Range("B" & i).Value = myVal
Next
End Sub

Currently, when you do Right("B" & i, Len("B" & i) - 2) & Left("B" & i, 2), for row 5, this becomes Right("B5", Len("B5") - 2) & Left("B5", 2) then this evaluates to simply:

Right("B5",0) & Left("B5",2), which is [nothing] & B5, finally becoming B5

Note the lack of using B5as a range. Instead it's being treated as a string.

(Also, I'm assuming this is to be run on the ActiveSheet. If not, please add the worksheet before the range, i.e. Worksheets("Raw Data Upload").Range("B" & i)...)

Upvotes: 0

DisplayName
DisplayName

Reputation: 13386

Try this

Private Sub OrderNumber_Click()
    Dim cell As Range
    With Worksheets("Raw Data Upload")
        For Each cell in .Range("B5", .Cells(.Rows.Count, 2).End(xlUp))
            cell.Value = Right(cell.Value, Len(cell.Value) - 2) & Left(cell.Value, 2)
        Next
    End With
End Sub

Upvotes: 0

Dave Cook
Dave Cook

Reputation: 717

dim beginStr, endStr, originalStr, outputStr as string
dim rng as range

'put the below into a loop, assigning a rng to the desired cell each time

originalStr = rng.value ' Change to chosen range
beginStr = left(originalStr,2)
endStr = right(originalStr, len(originalStr) - 2)
outputStr = endStr + beginStr
Range("B" & i).Value = outputStr

I haven't got a copy of Excel to test this on but it should work.

Upvotes: 1

QHarr
QHarr

Reputation: 84465

Like this? DO you want it in column B?

Option Explicit

Private Sub OrderNumber_Click()

Dim i As Long
Dim val As String
Dim EndC As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Raw Data Upload")
EndC = ws.Range("A1048576").End(xlUp).Row

For i = 5 To EndC

     val = ws.Cells(i, "A")

     Range("B" & i).Value = Mid$(val, 3, Len(val) - 2) & Left$(val, 2)

Next i

End Sub

Upvotes: 1

Related Questions