Does not work if there is only 1 entry in column, works for more than one enteries

[afzalw]

This Macro copy path written in Q column to new location path written in R column.

The problem is that it works when I have more than one entries in these Q and R column, but when there is only one entry than it does not work. It gives following error.

Run Time error Type 13
Type mismatched

Thanks


[VBA]
Sub M20CopyExcelFiles()
Set fs = CreateObject("Scripting.FileSystemObject")
With ThisWorkbook.Sheets(2)
LR = .Cells(.Rows.Count, "Q").End(xlUp).Row
oldpath = .Range("Q2:Q" & LR)
newpath = .Range("R2:R" & LR)

End With
For i = LBound(oldpath) To UBound(oldpath)
FileCopy oldpath(i, 1), newpath(i, 1)
Next i

End Sub


[/VBA]

[shrivallabha]

One workaround would be:
[VBA]Sub M20CopyExcelFiles()

With ThisWorkbook.Sheets(2)
LR = .Cells(.Rows.Count, "Q").End(xlUp).Row
If LR = 2 Then FileCopy .Range("Q2").Value, .Range("R2").Value: Exit Sub
oldpath = .Range("Q2:Q" & LR)
newpath = .Range("R2:R" & LR)
End With

For i = LBound(oldpath) To UBound(oldpath)
FileCopy oldpath(i, 1), newpath(i, 1)
Next i

End Sub[/VBA]

[Teeroy]

If your Range size is only one cell then oldpath and newpath are strings, not arrays, and LBound and Ubound of a string doesn't make sense.
Shrivallabha's way is shorter but the following might help you understand what went wrong.

[VBA]
Sub M20CopyExcelFiles()
Set fs = CreateObject("Scripting.FileSystemObject")
With ThisWorkbook.Sheets(2)
LR = .Cells(.Rows.Count, "Q").End(xlUp).Row
oldpath = .Range("Q2:Q" & LR)
newpath = .Range("R2:R" & LR)
End With
If VarType(oldpath) = vbString Then
FileCopy oldpath, newpath
Else
For i = LBound(oldpath) To UBound(oldpath)
FileCopy oldpath(i, 1), newpath(i, 1)
Next i
End If
End Sub[/VBA]

[shrivallabha]

If your Range size is only one cell then oldpath and newpath are strings, not arrays, and LBound and Ubound of a string doesn't make sense.
Shrivallabha's way is shorter but the following might help you understand what went wrong.

[vba]
Sub M20CopyExcelFiles()
Set fs = CreateObject("Scripting.FileSystemObject")
With ThisWorkbook.Sheets(2)
LR = .Cells(.Rows.Count, "Q").End(xlUp).Row
oldpath = .Range("Q2:Q" & LR)
newpath = .Range("R2:R" & LR)
End With
If VarType(oldpath) = vbString Then
FileCopy oldpath, newpath
Else
For i = LBound(oldpath) To UBound(oldpath)
FileCopy oldpath(i, 1), newpath(i, 1)
Next i
End If
End Sub[/vba]

Even your code can surely be one line shorter...
Nicely explained