0
Sub ImportXMLtoList()

    Dim strTargetFile As String
    Dim wb As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    strTargetFile = "C:\DELL\1c\CriticalTestCases_UB_S02028A_MB500_50_50.xml"
    Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadImportToList)
    Application.DisplayAlerts = True

    wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("Sheet1").Range("A10")
    wb.Close False
    Application.ScreenUpdating = True
    Range("F11:F" & lastRow).Formula = "=(MID(C2,SEARCH('(',C2)+1,SEARCH(')',C2)-SEARCH('(',C2)-1)"

End Sub

On running this, Run time error 1004

Method 'Range' of '_Object' failed. error is shown

VBasic2008
  • 44,888
  • 5
  • 17
  • 28
Sridhar Manoharan
  • 11

1 Answers1

0

Just before your last line of executable code (the one that uses lastrow), put this line of code:

lastrow = Cells(Rows.Count, "F").End(xlUp).row

This is if you mean to refer to the last cell in column F that has data. If you want the last first blank cell AFTER the last cell with data, then do this:

lastrow = Cells(Rows.Count, "F").End(xlUp).row + 1
Gove
  • 1,745
  • 10
  • 11