Create Static Array in VBA Module

[Rudi]

Hi,

I have a VBA project that loops through sheets, copying data blocks that match an area from one WB to another. The areas stay the same and the loop code stays the same. I want to know how to create an array in VBA (to store the areas) to pass to the loop, so I don't have to have one loop for each area.

Hope this is clear... TX

[HansV]

I don't know what you mean by static here.

You can use code like this:

Dim arr As Variant
arr = wsh1.Range("C5:F20").Value
...
wsh2.Range("C5:F20").Value = arr

[Rudi]

Sorry, I meant static as a list of values...not taking it from a sheet. (Sorry for the confusion).
I have come this far:

Code: Select all

Option Explicit
Option Base 1
Public sNames As Variant

Sub TypeNamesArray()
    
    sNames = Array("Knysna", "George", "Claremont", "Corporate", "Tyger Valley", "Durban", _
                "Durban : D1", "Derivatives", "Institutional", "iTrade", "Johannesburg", """Sandton""", _
                "Direct", "Pretoria", "Stellenbosch")
     
End Sub

[Rudi]

How do i exit this loop? When the array values are run, does the array return ""?

Code: Select all

Option Explicit
Option Base 1

Public sNames As Variant

Sub RunTransfer()
    TypeNamesArray
    FolderPicker
    LoopTransfer
End Sub

Sub LoopTransfer()
    Do Until sNames = "" '<<<<<< What does an array return when its elements are finished? TX
        MakeWB
        TransferData
    Loop
End Sub

Sub TypeNamesArray()

    sNames = Array("Knysna", "George", "Claremont", "Corporate", "Tyger Valley", "Durban", _
                   "Durban : D1", "Derivatives", "Institutional", "iTrade", "Johannesburg", _
                   """Sandton""", "Direct", "Pretoria", "Stellenbosch")

End Sub

[rory]

That's not how you loop through an array:

Code: Select all

Dim n as long
for n = lbound(snames) to ubound(snames)
' do something with sNames(n)
Next n

[Rudi]

TX...I'll change it and try again
Cheers

[HansV]

That will work if you want to fill cells in a single row:

Range("A1:O1").Value = sNames

If you want to fill cells in a single column, you must transpose the array:

Range("A1:A15").Value = Application.Transpose(sNames)

If you want to fill a range with multiple rows and columns, you must create a two-dimensional array.

[Rudi]

Hi,

I am now getting an error. The value of 'i' is -1 (where that comes from???) and is causeing a subscript out of range.

Please could you help with this error, and maybe suggest if there is a better way to process this file.
To recap, I need the code to locate and copy each block of area data into a new workbook. All the code is working, but the loop process and array process is a little unrefined?!

TX for any help.

2013-07-29_12h11_05.png

[HansV]

You declare i as Integer. This creates a variable with the default value 0.
You then have

i = i - 1

So i is now 0 - 1 = -1

[Rudi]

I did that earlier in stepping through the code to minus 1...then forgot to change it back to +1
I'll run it again to see if it works now...

[Rudi]

TX for everybody's help. The macro is running well now
Cheers

[Rudi]

Hi,

One more little issue...
If I select the desktop, then the code debugs? I can select desktop if I go through the Mydocs folder (obviously it builds a path then), but if a user just selects desktop, how can I avoid the debug?

In the code you will see I tried to build an IF...but this is not working?

TX

2013-07-29_15h09_31.jpg

2013-07-29_15h10_17.jpg

[HansV]

I'd use the FileDialog object:

Code: Select all

    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show Then
            sFolderPath = .SelectedItems(1)
        Else
            MsgBox "No folder selected", vbExclamation
            Exit Sub
        End If
    End With

When I use this and click directly on Desktop, it returns the correct path (Windows 7, Office 2010).

[Rudi]

Great. I'll swop it out. TX again!