PDA

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



afzalw
04-18-2013, 07:08 AM
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



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

shrivallabha
04-18-2013, 09:01 AM
One workaround would be:
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

Teeroy
04-18-2013, 11:35 PM
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.


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

shrivallabha
04-20-2013, 06:29 AM
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.


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
Even your code can surely be one line shorter...:rotlaugh:
Nicely explained:clap: