Shift characters in a value based on a variable

[Ricker090]

I've been trying to write a macro (unsuccessfully) to shift a series of numeric characters based on a value that I specify.

I always have a series of 7 numbers and the first number can be a 1 or a 0. The second number can be a 2 or a 0. The third number can be a 3 or a 0, etc. So, I get a report that will give me these numbers and I'm trying to shift the numbers down the sequence.

For example, my report generates: 1204067
If I add one to it, I would like: 1230507
Or if I add two, I would get: 1234060
Four: 0234507

So basically, I'm moving the zeros around the sequence. Is there a way to do this with VBA?

Please note that at least one of the seven characters will be greater than zero, but it's entirely possible to get something like this: 0000060

I have attached a sample workbook.

[rylo]

Hi

Try this UDF

Option Base 1
Function myfunc(startno, mover)
  Dim outarr(7)
  
  For i = 1 To 7
    If Mid(startno, i, 1) = "0" Then
      If i + mover > 7 Then
        posn = i + mover - 7
      Else
        posn = i + mover
      End If
      outarr(posn) = "0"
    End If
      
  Next i
  For i = 1 To 7
    If outarr(i) <> "0" Then
      outarr(i) = i
    End If
  Next i
  For i = 1 To 7
    myfunc = myfunc & outarr(i)
  Next i
  
End Function

Using your example file,
F1: =myfunc($B$1,D1)
Copy down to F4

HTH

rylo

[Marcol]

Another possible UDF

Function ShiftZeros(Base As Range, ShiftBy As Long) As String
    Dim n As Long, DigitCount As Long
    Dim strTmp As String
    
    If ShiftBy = 0 Then
        ShiftZeros = ""
        Exit Function
    End If
    
    DigitCount = Len(Base)
    If DigitCount > 9 Then
        ShiftZeros = "Digit Limit = 9"
        Exit Function
    End If
    
    ReDim BaseArray(DigitCount)
    For n = 1 To DigitCount
        BaseArray(n) = n
    Next
    
    ReDim tmpArray(DigitCount)
    For n = 1 To DigitCount
        If Mid(Base, n, 1) > 0 Then
            tmpArray(n) = 0
        Else
            If (n + ShiftBy) Mod DigitCount = 0 Then
                tmpArray(n) = DigitCount
            Else
                tmpArray(n) = (n + ShiftBy) Mod DigitCount
            End If
        End If
    Next
    
    For n = 1 To DigitCount
        If tmpArray(n) > 0 Then BaseArray(tmpArray(n)) = 0
    Next
    
    For n = 1 To DigitCount
        ShiftZeros = ShiftZeros & BaseArray(n)
    Next
    
End Function

A bit longer than rylos' but this will handle text based numbers with 1 to 9 digits.

[Ricker090]

Thanks guys. Both of these solutions work great and I will definitely use them.