application.selection into an array

[jimrward]

having a senior moment here
I have a worksheet with loads of columns but I only want to use a couple in a bit of VBA code to generate a text file for another application
one of these is names, so i can easily select a number of names and run my VBA, 2 columns away is an ID marker that I also want to use so my portion of code thus far is for testing

Dim mynames() As Variant
mynames = Application.Selection.Value
um = UBound(mynames)

n1=mynames(1) FAILS

For Each MyName In mynames
fred = MyName
Next

myids = Application.Selection.Offset(0, 2)
For Each Myid In myids
fred = Myid
Next

i want to to create a loop for each name but also keep things in sync with the IDS

every time i try and reference the array I get a subscript error, if there are 5 names for example, i want to loop from 1 to 5, and be able to reference the elements of the arrays in my output

 

[Akuini]

Maybe this:

Dim mynames() As Variant
mynames = Application.Selection.Value
myids = Application.Selection.Offset(0, 2)

For i = 1 To UBound(mynames, 1)
    Debug.Print mynames(i, 1) & " : " & myids(i, 1)
Next

 

[jimrward]

many thanks I was working with the array being a single dimension doh

 

[Akuini]

many thanks I was working with the array being a single dimension doh

When you load an array from multiple cells then it always be a 2D array, even if it is from 1 column.

 

[jimrward]

if i want to include a further few columns can I also create a horizontal array, instead of
myid1 = Application.Selection.Offset(0, 1)
myid2 = Application.Selection.Offset(0, 2)
myid3 = Application.Selection.Offset(0, 3)
also keep the above for names selection

 

[Akuini]

can I also create a horizontal array

I don't understand what you mean? and what is the purpose?

 

[jimrward]

I am trying to produce a CSV file to drive a dymo label production

it is so i can include other columns within the loop, the other columns are effectivly a matrix of 1's and blanks signifying options
I can then use an index to reference the contents of the other columns that match my name selections to change the label columns

The name and ID work fine I want to try now to pick up the matrix options and say if its a 1 then print one type of label else skip, names are to the left of the matrix, creating 13 variables makes it clumsy and loads of case/if/then/else

so in short for each name in the selection pick up the horizontal matrix for that selection row to an array for easier processing

1	2	3	4	5	6	7	8	9	10	11	12	13
41	37	36	37	10	20	6	14	8	2	2	2	11
1	1	1	1	1	1	1	1					1
1	1	1	1		1
1	1	1	1		1

 

[jimrward]

Sub MakeLabelsFile2()
Dim CompNames As Variant
CompNames = Application.Transpose(Application.Transpose(Worksheets("MasterScores").Range("E1:AF1")))

Dim CompDetail As Variant
CompDetail = Application.Transpose(Application.Transpose(Worksheets("MasterScores").Range("E2:AF2")))

u = UBound(CompNames)

Dim Path As String
Dim FileNumber As Integer

Path = "R:\MakeLabels.txt"
       'Change the path as per your requirement

FileNumber = FreeFile

Open Path For Output As FileNumber
Print #FileNumber, "Barcode,CompNo,Name,DistanceID,CompID,CompName,StageID,ShooterID"

MyNames = Application.Selection.Value
MyIDS = Application.Selection.Offset(0, 22)

Mybase = 5
MyC1 = Application.Selection.Offset(0, Mybase + 1)
For i = 1 To UBound(MyNames, 1)
    MyName = MyNames(i, 1)
    Myid = MyIDS(i, 1)

For l = 1 To u
    Dim compsplit() As String
    compsplit = Split(CompDetail(l), "~")
    test0 = compsplit(0)
    test1 = compsplit(1)
    test2 = compsplit(2)

    Dim CompIdStuff() As String
    CompIdStuff = Split(test0, ".")
    Stageid = CompIdStuff(1)
    compid = Format(Right(CompIdStuff(0), Len(CompIdStuff(0)) - 1), "00")


    Print #FileNumber, Myid & test2 & "," & Myid & "," & MyName & "," & Left(compsplit(2), 3) & "," & compid & "," & CompNames(l) & "," & Stageid & ",1"
Next l
Next
 
Close FileNumber
End Sub

 

[Akuini]

Sorry, I have to go now, it's after midnight in my country.
We can continue this tomorrow.
And hopefully somebody will be able to help.

 

[Akuini]

Sorry, I don't quite understand your explanation. And actually it is different from the original problem in post #1, so you need to start a new thread.