Insert string in rage error 1004 vba

Question

I try to define an array as a range of a combination of cells and strings. I get error 1004 : The method range of _Worksheet failed

My code :

Sub report()
'
' Macro

' Déclaration des variables
Dim myPath As String
Dim myFile As String
Dim Datereport As String
Dim myArray() As Integer
Dim LastRow As Long
Dim LastColumn As Long
Dim wb As Workbook
Dim sht As Worksheet
Dim Letter As String

'Optimisation de la Macro Speed
Application.ScreenUpdating = True
Application.EnableEvents = True

'Définition de la date de rapport
Datereport = Workbooks("Rapports").Worksheets("Sommaire").Range("B6")


'Trouve les fichiers qui on la date associée
myPath = "Z:\7. Personnel\Florian\Projet_BDC\Test\Consolidations\"
myFile = Dir(myPath & "*.xlsx")


 While myFile <> ""
    If InStr(myFile, Datereport) > 0 Then 
    Set wb = Workbooks.Open(Filename:=myPath & myFile)
    Set sht = wb.Worksheets(1)
    LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 
    LastColumn = sht.Cells(1, sht.Columns.Count).End(xlToLeft).Column 
    ReDim myArray(1 To LastRow, 1 To LastColumn)
    Letter = Col_Letter(LastColumn)
    myArray = sht.Range("A1:Letter & LastRow")
    End If
    myFile = Dir()
Wend

End Sub

The sub end at this line :

myArray = sht.Range("A1:Letter & LastRow")

I dont understand why the range is wrong. Letter is O and Last row is 15 So it should refer to Range("A1:O15"). I want to do it dynamically so i cant just put the cells.

Thank you

Scott Craner Scott Craner · Accepted Answer · 2016-01-26T16:21:15

You are very close. Change:

sht.Range("A1:Letter & LastRow")

to:

sht.Range("A1:" & Letter & LastRow)

Variables need to be removed from the string and then concatenated in individually

Or you could skip trying to find the column letter and use .Cells:

myArray = sht.Range(sht.Cells(1,1),sht.Cells(LastRow,LastColumn)).Value

You will need to declare myarray as variant change the declaration at the top to:

Dim MyArray as Variant

Insert string in rage error 1004 vba

1 Answers