Populating a grid

[downtown_david]

Hi everyone

Im almost a newbie

On Sheet1, I have a list of names in column A1 : A100. I would like to put these names into an array and on Sheet2, populate a grid at A1. I also need a message box to pop up to query the dimensions before populating the grid (5x5, 8x10, 8x12, etc)

Im trying to teach myself and improve my use of VBA, and I need to get over this hurdle to help me progress. I sincerely appreciate any help.

Thanks
downtown_david

 

[pvr928]

Hi downtown_david

Great that you want learn VBA. If this is the first thing you are trying to accomplish, the learning curve will be pretty steep.

The best way to learn VBA is trial & error. I won't provide you with the code, but will break down the steps for you. Have a go yourself in trying to achieve each step (Hint: if you've found MrExcel and created an account, I'm sure you are able to google possible solutions).

Step 1
Dimension all of your variables (Hint: use Option Explicit)
Identify both Sheet1 and Sheet2 and set ranges for them

Step 2
Determine length of list (number of rows - Hint: last row)

Step 3
Show message box asking for dimensions of array - are the possible arrays limited in number, ie only the three you gave - if so, you will have to lock down the options for the user (Hint: a populated combo box, or userform with option buttons may be suitable).

Step 4
Dimension the Excel array to the dimension of the array chosen by the user

Step 5
'Divide' the list into the array dimensions chosen and then populate the Excel array. This will probably require IF - THEN - ELSE logic encapsulated in a FOR loop, being the length of the depth of the list in Sheet1

Step 6
Print the Excel array to Sheet2

Each of these steps is eminently achievable, but will require some digging and trial and error on your part.

Have a go at some of these parts, post your attempts, and I'm sure there will be many willing volunteers to assist you.

Cheers

pvr928

 

[downtown_david]

Wow, great, I was looking for for some quick code that I would try to program into a much larger solution and I got a patronizing lecture. Yes, it possible useful, but not helpful.

 

[MickG]

Try this:-
Place this code in your data sheet "Code Module".
After loading code:-
Double click "C1"
Drop down appears for "Matrix "Size selection
Select from drop down,
On selection, Sheet 2 starting "A1" will update with required "Matric" based on data sheet column "A".

Private [COLOR=navy]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR=navy]As[/COLOR] Range, Cancel [COLOR=navy]As[/COLOR] Boolean)
[COLOR=navy]Dim[/COLOR] Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Txt = "5x5, 8x10, 8x12"
[COLOR=navy]With[/COLOR] Range("C1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Txt
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] nRay [COLOR=navy]As[/COLOR] Variant, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
   nRay = Range("A1", Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]If[/COLOR] Target.Address(0, 0) = "C1" [COLOR=navy]Then[/COLOR]
        [COLOR=navy]Select[/COLOR] [COLOR=navy]Case[/COLOR] Target
            [COLOR=navy]Case[/COLOR] "5x5": ReDim ray(1 To 5, 1 To 5)
            [COLOR=navy]Case[/COLOR] "8x10": ReDim ray(1 To 8, 1 To 10)
            [COLOR=navy]Case[/COLOR] "8x12": ReDim ray(1 To 8, 1 To 12)
    [COLOR=navy]End[/COLOR] Select
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]For[/COLOR] n = 1 To UBound(ray, 1)
  [COLOR=navy]For[/COLOR] Ac = 1 To UBound(ray, 2)
    c = c + 1
    ray(n, Ac) = nRay(c, 1)
    [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]With[/COLOR] Sheets("sheet2").Range("A1").Resize(UBound(ray, 1), UBound(ray, 2))
    .Parent.Range("A1:M12").Clear
    .Value = ray
    .Borders.Weight = 2
   .HorizontalAlignment = xlCenter
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick