Processing
 

Trying to get an array to contain information from a column on another worksheet in VBA

06/09/2016 14:12#1

Joined at: 6 months ago

Post: 1

Thank: 0

Thanked: 0

I'm very new to VBA and I have been using the below code to reorder columns to a specified order in the nams array.

What I would like to do is instead of having the list of columns written into the code is take the list from a range on another worksheet to make the code more flexible going forward but I'm not sure where to start. Any help would be greatly appreciated!

Dim rng As Range Dim i As Integer Dim J As Integer Dim Temp Dim nams As Variant Dim F Dim Dex As Integer nams = Array("RequestCode", "Contact", "OpenDate", "ClosedDate") Set rng = Range("A1").CurrentRegion For i = 1 To rng.Columns.Count     For J = i To rng.Columns.Count         For F = 0 To UBound(nams)             If nams(F) = rng(J) Then Dex = F: Exit For        Next F         If F < i Then             Temp = rng.Columns(i).Value             rng(i).Resize(rng.Rows.Count) = rng.Columns(J).Value             rng(J).Resize(rng.Rows.Count) = Temp         End If     Next J  Next I 
06/09/2016 14:31Top#2

SandPiper

Member

Joined at: 6 months ago

Post: 1

Thank: 0

Thanked: 0

Dim nams() As Variant nams = Sheets("Sheet1").Range("A1:A5") 

This will read the values of the specified range into your nams() array which indexes at 1, not 0. Keep in mind, doing this method always results in a two dimensional array, so to get your third value for example you will have to do something like nams(3,1).

Similar articles

Excel More conditions in one formula?

6 months ago - Reply: 2 - Views: 172

Get Workbook reference from Open File dialog

6 months ago - Reply: 2 - Views: 131

Add Calculated Field To Pivot Table

6 months ago - Reply: 0 - Views: 7

Nested IF Functions in Excel not working

6 months ago - Reply: 3 - Views: 164

Formulating Date in Excel 2013

7 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

7 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

7 months ago - Reply: 1 - Views: 23

excel search and copy in/from another file

7 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

7 months ago - Reply: 0 - Views: 6

Excel static conditional formatting area

7 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re