How to get the array like this?

I want to use VBA to make an array like the following.
Can anyone give me any advice?
Thanks!


57 58 59 60 61 62 63 64 65
56 31 32 33 34 35 36 37 66
55 30 13 14 15 16 17 38 67
54 29 12 03 04 05 18 39 68
53 28 11 02 01 06 19 40 69
52 27 10 09 08 07 20 41 70
51 26 25 24 23 22 21 42 71
50 49 48 47 46 45 44 43 72
81 80 79 78 77 76 75 74 73 


0
rover3171 (3)
8/15/2009 3:10:43 AM
excel 39879 articles. 2 followers. Follow

6 Replies
484 Views

Similar Articles

[PageSpeed] 40

why?
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"���˵�˫��" <rover317@hotmail.com> wrote in message 
news:uoQj1XVHKHA.4316@TK2MSFTNGP04.phx.gbl...
>I want to use VBA to make an array like the following.
> Can anyone give me any advice?
> Thanks!
>
>
> 57 58 59 60 61 62 63 64 65
> 56 31 32 33 34 35 36 37 66
> 55 30 13 14 15 16 17 38 67
> 54 29 12 03 04 05 18 39 68
> 53 28 11 02 01 06 19 40 69
> 52 27 10 09 08 07 20 41 70
> 51 26 25 24 23 22 21 42 71
> 50 49 48 47 46 45 44 43 72
> 81 80 79 78 77 76 75 74 73
> 

0
dguillett1 (2487)
8/15/2009 12:39:40 PM
It is certainly possible. Code like the following may get you started.

Sub AAA()
    Dim NumRows As Long
    Dim NumCols As Long
    Dim RNdx As Long
    Dim CNdx As Long
    
    NumRows = 3
    NumCols = 6
    For RNdx = 1 To NumRows
        For CNdx = 1 To NumCols
            Cells(RNdx, CNdx).Value = "????"
        Next CNdx
    Next RNdx
End Sub


The big question, of course, is how are the individual values
calculated? What would you have go in place of the ???? in the code
above.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
    Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 15 Aug 2009 11:10:43 +0800, "���˵�˫��"
<rover317@hotmail.com> wrote:

>I want to use VBA to make an array like the following.
>Can anyone give me any advice?
>Thanks!
>
>
>57 58 59 60 61 62 63 64 65
>56 31 32 33 34 35 36 37 66
>55 30 13 14 15 16 17 38 67
>54 29 12 03 04 05 18 39 68
>53 28 11 02 01 06 19 40 69
>52 27 10 09 08 07 20 41 70
>51 26 25 24 23 22 21 42 71
>50 49 48 47 46 45 44 43 72
>81 80 79 78 77 76 75 74 73 
>
0
chip1 (1821)
8/16/2009 1:24:47 AM
"=C1=F7=C0=CB=B5=C4=CB=AB=D3=E3" <rover...@hotmail.com> wrote...
>I want to use VBA to make an array like the following.
>Can anyone give me any advice?
>
>57 58 59 60 61 62 63 64 65
>56 31 32 33 34 35 36 37 66
>55 30 13 14 15 16 17 38 67
>54 29 12 03 04 05 18 39 68
>53 28 11 02 01 06 19 40 69
>52 27 10 09 08 07 20 41 70
>51 26 25 24 23 22 21 42 71
>50 49 48 47 46 45 44 43 72
>81 80 79 78 77 76 75 74 73

You have a spiral. Not immediately obvious to see, but you have a 9 by
9 array with 1 in the center at coordinates (5,5), increment left,
then up, then right 2 cells, then down 2 cells, then right 3 cells,
then up 3 cells, then right 4 cells, then down 4 cells, etc.

So if you want to move clockwise moving left first from the center, so
left - up - right - down, you could try the following udf.


Function spiral_lurd(ByVal n As Long) As Variant
  Dim i As Long, j As Long, k As Long, m As Long, s As Long
  Dim rv() As Long

  If n < 1 Then
    spiral_lurd =3D CVErr(xlErrNum)
    Exit Function
  End If

  ReDim rv(1 To n, 1 To n)

  i =3D 1 + n \ 2
  j =3D i
  k =3D 1
  rv(i, j) =3D k

  For m =3D 1 To n
    s =3D IIf(m Mod 2 =3D 1, -1, 1)

    Do While s * j < s * i + m
      j =3D j + s
      If j < 1 Or j > n Then Exit For
      k =3D k + 1
      rv(i, j) =3D k
    Loop

    Do While s * i < s * j
      i =3D i + s
      k =3D k + 1
      rv(i, j) =3D k
    Loop

  Next m

  spiral_lurd =3D rv

End Function


Select, say, F21:N29, type the formula  =3Dspiral_lurd(9), hold down
[Ctrl] and [Shift] keys and press [Enter] to enter the formula in
these cells as a single array formula.
0
hrlngrv1 (375)
8/16/2009 9:27:10 AM
"Don Guillett" <dguille...@austin.rr.com> wrote...
>why?

Why respond?
0
hrlngrv1 (375)
8/16/2009 9:27:54 AM
Try this one. this code will put the number as you showed into cells,
but not making an array as you said. if you want to make an array having
a number like this, you need to modify the code below a little.

Sub PutNumtest()
Dim J As Long, I As Long, M As Long
Dim n As Long, DL As Long, k As Long, R As Long
Dim sngJ As Long, sngI As Long

J = 5   '<<==change here if you want
I = 5   '<<==cahnge here if you want
n = 1
Cells(I, J) = n
sngJ = -1
sngI = -1
M = 1

On Error GoTo ex
Do
For k = 0 To 1
If DL = 0 Then
    For R = 1 To M
        n = n + 1
        J = J + sngJ
        Cells(I, J) = n
    Next
    sngJ = -sngJ
    DL = 1
Else
    For R = 1 To M
        n = n + 1
        I = I + sngI
        Cells(I, J) = n
    Next
    sngI = -sngI
    DL = 0
End If
Next
M = M + 1
Loop
ex:
End Sub

Keiji

"����������������������������������" wrote:
> I want to use VBA to make an array like the following.
> Can anyone give me any advice?
> Thanks!
> 
> 
> 57 58 59 60 61 62 63 64 65
> 56 31 32 33 34 35 36 37 66
> 55 30 13 14 15 16 17 38 67
> 54 29 12 03 04 05 18 39 68
> 53 28 11 02 01 06 19 40 69
> 52 27 10 09 08 07 20 41 70
> 51 26 25 24 23 22 21 42 71
> 50 49 48 47 46 45 44 43 72
> 81 80 79 78 77 76 75 74 73 
> 
> 
0
keiji
8/16/2009 9:40:09 AM
Excel 2007
Animated spiral.
Just for the fun of it.
http://www.mediafire.com/file/njzjyezt32j/08_15_09.xlsm
0
8/17/2009 6:29:27 PM
Reply:

Similar Artilces:

Serializing Array with array "name" in the XML
I need help serializing an array without including the array "name". I am writing in C# and using the XmlSerializer to serial classes. I am trying to serialize a class with an array in it like the one below. public class myclass { public imagecontext; public myimage[] images; } public myimage { public string name; public string path; } It will serialize something like this... <myclass> <imagecontext>my context</imagecontext> <images> <myimage> <name>Image1</name> ...

How do I get colour separated printouts red and black?
Trying to prepare and print colour separated printouts of a publisher document. I want printouts of red and also in black. The printer I'm using is a Hp Deskjet 720C Could this be the problem? Make a back-up of the file Delete all the red items on the page and print that page, Do not save the file and close it Then re-open the file and delete all the black items on the page and print that. >-----Original Message----- >Trying to prepare and print colour separated printouts of a publisher >document. I want printouts of red and also in black. The printer I'm using &g...

User removed Anonymous permission from Calendar...how to get it back
A user has deleted the Anonymous permissions from their calendar and I am unable to find a way to get it back. Does anyone know what can be done to add this back to their calendar. TIA In news:Xns9861A9314BAB8bobarcabca@207.46.248.16, Fuzzy Logic <bob@arc.ab.caREMOVETHIS> typed: > A user has deleted the Anonymous permissions from their calendar and > I am unable to find a way to get it back. Does anyone know what can > be done to add this back to their calendar. TIA In that user's Outlook, right-click on Calendar, properties, permissions tab. Add Anonymous, and do whate...

Scheduled tasks dont work but look like they are
Hi All, We are having intermittent problems with Windows Task Scheduler. Two scenarios: 1. Everything will work fine for a few weeks, then suddenly tasks that used to work ok will complete as normal, but task scheduler does not know they are finished, and so the tasks status sits as running.. even though the program and executable have closed and gone away.. this stops future instances of the scheduled task from running. 2. Tasks that are scheduled to run, and have run for weeks without issue, suddenly just stop running. They sit waiting for their scheduled start time, the ...

Please help with getting the right structure from dataset using GetXML()
Hi everyone, I need your help getting my getXML method to function the way I need it to. I have a stored procedure that returns data into a dataset (I am using VB.NET). I use getXML with dataset and I get the following result: <NewDataSet> <Table> <StudentFirstName>John</StudentFirstName> <StudentMiddleName>J</StudentMiddleName> <StudentLastName>Smith</StudentLastName> <StudentAddressType>Permanent</StudentAddressType> <StudentAddressLine1>123 Some Rd</StudentAddressLine1> <StudentAddressLine...

Quest
does csvde has limitied attributes to export ? For quest cmdlets, i can use more properties/attributes, but i wish to pipe to csvde for export. Can it be done ? Why not just pipe to a csv file -- Richard Siddaway All scripts are supplied "as is" and with no warranty PowerShell MVP Blog and PowerShell User Group: http://msmvps.com/blogs/RichardSiddaway/Default.aspx "IT Staff" wrote: > does csvde has limitied attributes to export ? > > For quest cmdlets, i can use more properties/attributes, but i wish to pipe > to csvde for e...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

Get External Data
I have a 2 column table which is likely to change length whenever the Refresh Data menu option is selected. The table links to another software package (SAGE). I don't have formulae in adjacent columns so my problem can not be solved by checking the "Fill Down formulas in columns adjacent to data" box. I do, however, have text in adjacent columns (which the user selects from an in-cell drop down list) which I need to remain aligned with the relevant row. The "insert entire rows for new data ..." option does not appear to do what it says! It only inserts rows in the...

Importing text using get external data
I have created a query spreadsheet that gets certain data from another spreadsheet (call it data spreadsheet). The data spreadsheet has a column where text is stored, which in some cases exceeds more than 255 characters. The query that imports such data truncates the text to the first 255 characters. Is there a way around it ? Any input will be appreciated. --- Message posted from http://www.ExcelForum.com/ ...

the number "3" displays like an exposant
je ne comprend pas pourquoi le chiffre 3 apparait comme un exposant dans mon classeur ...

problem getting my hotmail contacts into outlook
I am new to outlook and want my hotmail contacts to be in outlook it says that you can do it and believe me I have tried, using all the help features available but no luck it says you need an MSN connector which I have tried to find. Hi , have a look on this site : "Download Microsoft Office Outlook Connector for MSN" http://www.microsoft.com/downloads/details.aspx?FamilyID=aa0ad3b0-e1a4-4990-a2be-dde78fed0709&displaylang=en Maybe it helps. -- Oliver Vukovics Share your Outlook PST files without Exchange: Public ShareFolder Synchronisation for Outlook and Exchange: Public...

Socket relared problem (getting free port number dynamically) #2
Hi all, i have one problem related to thread. i am having my main thread running which will on some event create a thread for validation purpose and then will start working. and now this validator thread will create a socket and it will bind to port 20111. and then will wait for client to connect. but before waiting for client to connect it will send a message to the main thread that validation is done. but in this way i need to stick with port 20111.which i really don't want. Is it possible for me to get a free port from operating system or from winsock library and then i should bind t...

How do I get Front Page 2003 to work with Windows7
I have been using FrontPage 2003 to update our website. Last month I updated my computer to Windows7 and Office 2007. Several days later I try to log on to Front page and receive the following message : " The operating system is not presently configured to run this application. Load failed Cannont load MSO.DLL" Has anyone had a similar experience? Or any advice? Change the compatibility to Windows XP. (BTW, if you search this newsgroup, you'll see this issue has been addressed many times._ -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 -----...

Getting the longest lengh in range of cells
I am trying to get the length of the longest cell in a range and use the array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. Is there a way in a formula to determine the longest cell length in a range, or through VBA. Thanks in advance Hi Try this formula array (please amend with last row number): {=MAX(LEN(A1:A7))} HTH Cordially Pascal "Geoff" <gh@bob.com> a �crit dans le message de news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl... > I am trying to get the length of the longest cell in a range and use the > array {=len(a1:a1)}. What happen...

Possible to get rid of without rebuilding everything?
Hi all, Back to years ago, the consultant set up our forest with empty root domain and one child domain. Right now, the forest functional level is windows 2003. and all DCs are windows 2003 domain controller. All users and servers are on the child domain. Currently, we are thinking to move to Windows 2008 DCs. If we need to get rid of the empty root domain. is this possible without affecting our applications (exchange etc..)? BTW, why is the reason to keep this empty root domain? Thank you! You are stuck with the root domain forever. Root domain is no longer consi...

URGENT: Creating a chart (Getting the correct input and output) unable to figure out
Hi, I have been trying to create this chart since a long time now and go no clue what am donig wrong. I have some machines in my company and all of them have a end of life for example: Machine 1, at location A with End of Life in 2004 Machine 2, at location B with End of Life in 2008 I want the years on x-axis and the machine locations on y-axis. Afte that I want horizantal lines representing machines which also show th end of life. For a better understanding, see the attached excel file. I have als created an image of the chart I'm looking to draw. Please advise. Thanks. Ker ...

earn extra money and get paid weekly
Earn and get weekly .Minimum payout is Rs.50 only. Just follow 5 steps and start earnings To start earnings click here- http://All4Webs.com/2/m/wassu2007/home.htm ...

single user email gets lost or arrives hours late
I have a user that is using office xp. we run excg5.5. he can send out an email to a user and sometimes the user gets it and sometimes he dosen't. we don't get bounce back message. i am trying to figure out what the problem is. The users address is saved in the users contacts list. ...

How do you get rid of the "[SPAM]" in the subject of an email?
Everytime I send or receive email, no matter what computer I'm on, it automatically inserts "[SPAM]" into the subject of an email. How do I get rid of this? Outlook isn't doing that. Check with your mail administrator or ISP and see if they're running a spam filter that is. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr/Computers/OneNoteFAQ.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. ...

can you get read reciepts from clients other than Outlook?
I have used read receipts for inter office mail where everyone uses Outlook 2000 and Exchange 2000. Works nice. However, what if I want a read receipt from an email I sent to: 1) a web based account like hotmail or yahoo? 2) other email clients like Outlook Express or Eudora? can I? How does that work? any info is appreciated. Thanks! djc <noone@nowhere.com> wrote: > However, what if I want a > read receipt from an email I sent to: > 1) a web based account like hotmail or yahoo? I don't recall any settings in Yahoo! or Hotmail that honor receipts. > 2) other email ...

Brithdays & Anniversaries get recreated from time to time
Apparently Outlook occasionally (I guess at the begin of a year) recreates recurring appointments from the birthdays and anniversaries that it finds in my Contacts-entries. What's annying about this is, that for lots of birthdays I now have duplicates, i.e. TWO appointments. And what's desastrous is that all these new appointments have a reminder set at 12pm of that day. Thus, in the last couple of days repeatedly the alarm of my PDA has gone off to remind me of some friends birthday and woke up me and my wife at midnight (which each time caused some major quarrel and my PDA a...

How do I create a poster consisting of 4 x A4 pages which get glue
I know this can be done as I have done it some yaers before. It may have been in Power Point however. As I remember I created the poster content on the word document page and then using some option/s one is able to segment the page into 4 quarters, print these and then glue them together. I am using Microsoft Office Student and Teacher 2003 Word cannot create posters - try Publisher. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP...

Can anyone get the front and rear pages of a booklet to display?
I can merrily paste a pic onto facing INTERNAL pages of a booklet. But I can't display "facing pages" for the covers (I can print preview)... I can't get a picture to go onto the front and rear covers, the picture gets cropped to the spine. I want one picture that wraps around the covers of the booklet (4" x 8 1/2") to fit into a #10 envelope. This is kind of tricky. The best way to achieve this would be doing the procedure on an inside facing page so you can readily see what you are doing. Insert the picture, copy/paste the picture, stack the two images b...

In Excell how do you get to calculate after clicking off of Calc
I copied and pasted a calculation from the Help display and when I clicked off of the calculation. It should have calculated but didn't. I am using Excel 2007. =SUM(A2,-B2) was put in C2 =SUM(C2,A3,-B3) was put in C3 How do you get it to calculate? I clicked on a different cell in a different row and column. I put numbers in A2,B2 and A3and B3 that the example gave. The help had the title "Calculate a running balance". I copied and pasted the example like helped told me to do. When that didn't work I typed it in and it still didn't work. flyingrace One ...

need example code to get the contactID from SFA/conts/edit.aspx...
Hi!! My question is: How can I get the right ContactID from the current/opened Contact in Microsoft CRM? Can I read the ContactID out from the URL? Is there an example Code available? Microsoft�s SDK for CRM is not very helpful for this Problem. Has anybody already an example code? Thank you for your help! Greetings Daniel You should be able to get it from the query string, as long as you make sure that the entry in the isv.config file with the link to your app has the variable "PassParams" equal to 1 For example: <Button Title="Test" ToolTip="Info on Test&qu...