PDA

View Full Version : [SOLVED] Issue with getting last member from an array



quetzal
06-19-2016, 09:41 AM
Hi, I cannot get the last item from my selection. Coding in VBA drives my crazy. There might by severar errors in the following short code. Thank you for your help.


Option Base 1
Sub test()
Dim Ranko As Integer
LastItem As Date ' not sure if this is a correct declaration

Sheets("SCHEDULE").Range("E5:E12").Select' my selection of some dates


Ranko = Selection.Count ' counts the cells in selection
Dim AllTimeArr(Ranko) As Variant
AllTimeArr = Selection 'should assign the selection to the array
LastItem = AllTimeArr(Ranko) 'should get the last member from array
End Sub

Paul_Hossler
06-19-2016, 10:14 AM
Selection is a 2 dim array


Option Explicit
Option Base 1
Sub test()
Dim Ranko As Integer
Dim LastItem As Date ' not sure if this is a correct declaration
Dim AllTimeArr As Variant

Sheets("SCHEDULE").Range("E5:E12").Select ' my selection of some dates
AllTimeArr = Selection '2 dim array (1-8,1-1)

Ranko = UBound(AllTimeArr, 1)

LastItem = AllTimeArr(Ranko, 1) 'last row, first col
End Sub

quetzal
06-19-2016, 11:34 AM
I did not expect that. Thank you!
And how would you extract only the one dimmension? Because this doesn't work:


Ranko = UBound(AllTimeArr, 1)
Dim ExtrArray(Ranko) As Date
For i = 1 To Ranko
ExtrArray(i) = AllTimeArr(i, 1)
Next

snb
06-19-2016, 11:35 AM
Keep it ....


Sub M__snb()
sn=Sheets("SCHEDULE").Range("E5:E12")
msgbox sn(ubound(sn),1)
end sub

1-dimensional:

Sub M__snb()
sn=application.transpose(Sheets("SCHEDULE").Range("E5:E12"))
msgbox sn(ubound(sn))
end sub

or


Sub M__snb()
sn=[transpose(E5:E12)]
msgbox sn(ubound(sn))
end sub

quetzal
06-19-2016, 12:36 PM
...found solution for that one:

Dim ExtrArray() As Variant
Ranko = UBound(AllTimeArr, 1)
ReDim ExtrArray(Ranko)
For i = 1 To Ranko
ExtrArray(i) = AllTimeArr(i, 1)
Next

Paul_Hossler
06-19-2016, 07:14 PM
snb's #4 for a 1 dim array using .Transpose is better and faster