determine how many elements an array will have

[mc136355]

Hi

newbie needing to know how to declare an array where we don't know how many elements due to always increasing
I have a list in A1 to A10 and need to use lastrow to declare array

also would like to display msgbox after the loop to give a list of all items (don't know if this would be join or split function)

thanks for any help

MC

Dim lastrow As Long
lastrow = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Count
Dim i As Long
Dim fileArray(1 To lastrow) As String
For i = 1 To lastrow
MsgBox fileArray(i)
Next i

 

[dmt32]

Hi,
If you want to assign your range to an array then try this

      Dim fileArray As Variant, Element As Variant
    
    fileArray = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value2
    
    For Each Element In fileArray
        MsgBox Element
    Next

Dave

 

[mc136355]

Hi thanks for reply

code works great and puts the range into the array

can I instead of msgbox for each individual element just have one with list of elements separated by comma

do I need to use redim preserve and then output after loop

thanks
MC

 

[dmt32]

something like following maybe?

    For Each Element In fileArray
        msg = msg & Element & " ,"
    Next
    
        MsgBox Mid(msg, 1, Len(msg) - 1)

Dave

 

[mc136355]

Thanks for replies

Both bits of code work great and will be using in my code

MC

 

[mc136355]

Hi again bit stuck on getting the following code to work

first part works and goes to function but I then get subscript out of range

cant understand the problem


Private Sub CommandButton1_Click()
Worksheets("INVENTORY").Activate
If WorksheetFunction.CountIf(Range("C5:C15"), Range("Q17")) = 0 Then
MsgBox "Item not in list, choose from list"
Else
MsgBox "Valid Choice"
myArr = Worksheets("INVENTORY").Range("Q17")

MyVar = Find_Sheet(myArr)


End If
End Sub


Function Find_Sheet(myArr As String) As Boolean
Dim Element As Variant

fileArray = Worksheets("test3").Range("A1:A" & cells(Rows.Count, "A").End(xlUp).Row).Value2

For Each Element In fileArray
msg = msg & Element & " ,"
Next

MsgBox Mid(msg, 1, Len(msg) - 1)
End Function

 

[dmt32]

does Worksheets("test3") exist and if so, is the workbook the active workbook or do you have another workbook open?

Dave

 

[mc136355]

Thanks for reply

Hi test3 is a sheet in the same workbook from which I pull my data from

Rgds
MC

 

[dmt32]

try this update to function resolves the error.

Function Find_Sheet(ByVal myArr As String) As Boolean
    Dim Element As Variant
    Dim msg as string
    
    With ThisWorkbook.Worksheets("test3")
        fileArray = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value2
    End With
    
    For Each Element In fileArray
    msg = msg & Element & " ,"
    Next
    
    MsgBox Mid(msg, 1, Len(msg) - 1)
End Function

whats your intention with argument myArr - you pass a value from range to it but are not using it in function?

Dave

 

[mc136355]

Hi Dave

Thanks for your help as a newbie I get quite confused with what is an array variable etc. and how to manipulate.
I will endeavour to keep learning and improve my knowledge.

code is perfect for my needs

thanks
MC