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

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

Re: Rearrange numbers in a range

From Claus Busch <claus_busch@t-online.de>
Newsgroups microsoft.public.excel.programming
Subject Re: Rearrange numbers in a range
Date 2015-10-24 08:20 +0200
Organization A noiseless patient Spider
Message-ID <n0f7r5$o2p$1@dont-email.me> (permalink)
References <eb95429f-193d-4d49-bf4c-0fc49cab1b69@googlegroups.com> <n0cjg4$58d$1@dont-email.me> <n0ckkt$8e2$1@dont-email.me> <b30fa42d-6c8d-4f2e-812b-054aa15dbd31@googlegroups.com>

Show all headers | View raw

Hi Derick,

Am Fri, 23 Oct 2015 21:10:59 -0700 (PDT) schrieb
derick.gasimperial@gmail.com:

> Thank you. Its not quite doing what is expected.
> For example:
> If the numbers in the source range are;
> 1,2,3,4,12,13
> 
> Then the result should be:
> 2,3,4,12,13,1
> 3,4,12,13,1,2
> 4,12,13,1,2,3
> 12,13,1,2,3,4

I thought you want only rearrange numbers <=10

Try this:

Sub Rearrange()
Dim myCnt As Long, i As Long, n As Long
Dim varData() As Variant

myCnt = Application.CountIf(Range("B4:G4"), "<11")
If myCnt = 0 Then Exit Sub

Application.ScreenUpdating = False

For i = 5 To 9
    Range(Cells(i, 2), Cells(i, 6)).Value _
        = Range(Cells(i - 1, 3), Cells(i - 1, 7)).Value
    Cells(i, 7) = Cells(i - 1, 2)
Next

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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