Type-Mismatch on For Each Range

0
votes

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...

2
excelvbaforeachtype-mismatchoutofrangeexception
Dim Rng, ArCell As Range dimensions Rng as a Variant (undefined by your syntax) and only ArCell as range. Try Dim Rng As Range, ArCell As Range - Variatus

2 Answers

0
votes

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)
0
votes

Dim Rng, ArCell As Range dimensions Rng as a Variant (undefined by your syntax) and only ArCell as range. Try Dim Rng As Range, ArCell As Range

The default property of a range is the Value property. Therefore, if you write Workbooks(FileName1).Sheets(1).Range(arCell) = 1 you are assigning the value of 1 to the defined range. In order to not confuse yourself write Workbooks(FileName1).Sheets(1).Range(arCell).Value = 1

Of course, Workbooks(FileName1).Sheets(1).Range(arCell) makes no sense because arCell is a range whose exact location (workbook and worksheet) is already defined. Therefore your whole long line burns down to arCell.Value = 1. Same for the value to be assigned except that my reduction would burn down to arCell.Value = arCell.Value which you don't have in mind. You might want to play with arCell.Address but Address isn't the default property, Value is. So, write what you mean and mean what your write.