determine how many elements an array will have

mc136355

New Member
Joined
Mar 20, 2018
Messages
36
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,333
Office Version
2013
Platform
Windows
Hi,
If you want to assign your range to an array then try this


Code:
      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
 
Last edited:

mc136355

New Member
Joined
Mar 20, 2018
Messages
36
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,333
Office Version
2013
Platform
Windows
something like following maybe?


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

mc136355

New Member
Joined
Mar 20, 2018
Messages
36
Thanks for replies

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

MC
 

mc136355

New Member
Joined
Mar 20, 2018
Messages
36
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

Well-known Member
Joined
Jul 3, 2012
Messages
5,333
Office Version
2013
Platform
Windows
does Worksheets("test3") exist and if so, is the workbook the active workbook or do you have another workbook open?

Dave
 

mc136355

New Member
Joined
Mar 20, 2018
Messages
36
Thanks for reply

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

Rgds
MC
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,333
Office Version
2013
Platform
Windows
try this update to function resolves the error.


Code:
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

New Member
Joined
Mar 20, 2018
Messages
36
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
 

Forum statistics

Threads
1,084,884
Messages
5,380,436
Members
401,678
Latest member
saffar

Some videos you may like

This Week's Hot Topics

Top