Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108118

Re: Rearrange numbers in a range

Show all headers | View raw

derick.gasimperial@gmail.com explained on 23-10-15 :
> I have the numbers 1,2,3,4,5,6 in the source range("b4:g4") and would like to 
> produce
>
> 1,2,3,4,5,6
> 2,3,4,5,6,1
> 3,4,5,6,1,2
> 4,5,6,1,2,3
> 5,6,1,2,3,4
> 6,1,2,3,4,5
>
> Basically the code would take each number in the source range and place it in 
> the 6th column if it is between 1 and 10 inclusive. First it would have to 
> the count how many numbers in the source range "b4:g4" are equal to and less 
> than 10.  In this case the 6 numbers are less than 10 so it would produce 6 
> results. if it was 5 numbers that are less than 10 then it would produce 5 
> results. If all the numbers are more than 10 then no action is required. 
>
> The numbers in the source range can be in any order not necessarily in 
> sequence.
>
> Any help would be greatly appreciated.
> Thank you

Try this:
================================
Public Sub ShiftLeft()
Dim A, B, C, R, i, j
Dim Separator As String

R = [F2!B4:G4]
Separator = ","

For Each i In R
    A = A & i & Separator
Next
A = Left(A, Len(A) - 1)

MsgBox A
For j = 1 To 5
    B = Split(A, Separator, -1, 1)
    For i = 1 To 5
        C = C & B(i) & Separator
    Next
    C = C & B(0)
    MsgBox C
    A = C
    C = NullString
Next

End Sub
==============================

Bruno

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-22 21:18 -0700
  Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-23 08:20 +0200
    Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-23 08:40 +0200
      Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-23 21:10 -0700
        Re: Rearrange numbers in a range Claus Busch <claus_busch@t-online.de> - 2015-10-24 08:20 +0200
          Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-25 19:07 -0700
  Re: Rearrange numbers in a range GS <gs@v.invalid> - 2015-10-24 22:29 -0400
  Re: Rearrange numbers in a range Bruno Campanini <brunocam@libero.it> - 2015-10-26 01:32 +0100
    Re: Rearrange numbers in a range derick.gasimperial@gmail.com - 2015-10-25 19:10 -0700

csiph-web