excelvbaforeachtype-mismatchoutofrangeexception

Type-Mismatch on For Each Range

i'm getting a type mismatch while trying to copy a range of cells. This macro needs to open 2 different workbooks, and then proceeds to copy a predefined set of cells, so the empty cell in the WorkBook(Filename1) D19, becomes the value of the already filled D19 cell in WorkBook(Filename2). This is the complete code im working on:

Sub OpenWorkbooks()
Application.ScreenUpdating = False

Dim fd As FileDialog
Dim FileName1, FileName2 As String
Dim Rng, ArCell As Range

Set fd = Application.FileDialog(msoFileDialogOpen)
Dim FileChosen As Integer

FileChosen = fd.Show
fd.InitialFileName = "C:\"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = True

fd.Filters.Clear
fd.Filters.Add "Excel1", "*.xlsx"
fd.Filters.Add "Excel2", "*.xlsm"

fd.FilterIndex = 1
fd.ButtonName = "Select &2Files .xlsm/.xlsx"

If FileChosen <> -1 Then
Else

FileName1 = fd.SelectedItems(1)
Workbooks.Open (FileName1)
FileName2 = fd.SelectedItems(2)
Workbooks.Open (FileName2)

End If

And here comes the problematic part, the debug highlights the third line (added .address

    Set Rng = Range("D19,D20,I19,I20,C30,C32,C35,C36,D40,D41,D42,D43,D44,D45")
    For Each ArCell In Rng.Cells
    Workbooks(FileName1).Sheets(1).Range(ArCell.**address**) = Workbooks(FileName2).Sheets(1).Range(ArCell.**address**)
    Next ArCell

Application.ScreenUpdating = True
End Sub

edited: @PaichengWu I took your suggestion and it got rid of the type-mismatch problem, but the subscription out of range still persists. I used this kind of Range = Range definition before and I recall it bypassing the need for an "activate" so why doesn't it work under this circumstances

Even when trying stuff like "Workbooks(FileName1).Activate" before the operation, the debug still point out "Out of range". Sr if I'm being this dense, this is the second time in my life I've worked on vba...

Solution

  • Try rewrite below

    Workbooks(FileName1).Sheets(1).Range(ArCell) = Workbooks(FileName2).Sheets(1).Range(ArCell)

    as

    Workbooks(FileName1).Sheets(1).Range(ArCell.address) = Workbooks(FileName2).Sheets(1).Range(ArCell.address)