Thread: EXCEL 2007 Run-time error "11"

hello everyone , i got a problem with using macro. i don't know where problem is. so please help me. and the macro codes are below

Option Explicit

Sub Inforecord_List()
Dim CycleQty, i, j, StartRow, ScaleQty, Scale1Col As Integer
Dim DataSht, TmSht, NewSht, VendorSht As Worksheet
Dim TempArr() As Variant

Set DataSht = ThisWorkbook.Sheets("Ô*ʼÊý¾Ý")
Set TmSht = ThisWorkbook.Sheets("Template")
Set VendorSht = ThisWorkbook.Sheets("Vendor")
CycleQty = Application.WorksheetFunction.CountA(DataSht.Columns(1))
ScaleQty = Application.WorksheetFunction.Count(DataSht.Rows(1))
Scale1Col = Application.WorksheetFunction.Match(1, DataSht.Rows(1), 0)
TmSht.Copy after:=DataSht
Set NewSht = ThisWorkbook.Sheets(2)
NewSht.Visible = True

For i = 2 To CycleQty
StartRow = NewSht.UsedRange.Rows.Count + 1
    With NewSht
        If Application.WorksheetFunction.CountIf(VendorSht.Columns(2), DataSht.Cells(i, 2)) > 0 Then _
            .Cells(StartRow, 1).Resize(ScaleQty, 1) = Application.WorksheetFunction.VLookup(Trim(DataSht.Cells(i, 2)), VendorSht.Range("VendorlistRange"), 2, 0)
        .Cells(StartRow, 2).Resize(ScaleQty, 1) = DataSht.Cells(i, 1) 'ÁϺÅ
        .Cells(StartRow, 3).Resize(ScaleQty, 1) = "T010"
        .Cells(StartRow, 4).Resize(ScaleQty, 1) = "3480"
        .Cells(StartRow, 5).Resize(ScaleQty, 1) = "Standard"
        .Cells(StartRow, 12).Resize(ScaleQty, 1) = "=today()-1"
        Columns("L:L").Select
         Selection.Copy
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        .Cells(StartRow, 13).Resize(ScaleQty, 1) = "9999-12-31"
        .Cells(StartRow, 14).Resize(ScaleQty, 1) = "XX"
        .Cells(StartRow, 15).Resize(ScaleQty, 1) = "1000"
        .Cells(StartRow, 16).Resize(ScaleQty, 1) = "ZOWN"
        .Cells(StartRow, 17).Resize(ScaleQty, 1) = "Checked"
        .Cells(StartRow, 18).Resize(ScaleQty, 1).NumberFormatLocal = "@"
        .Cells(StartRow, 18).Resize(ScaleQty, 1) = "0001"
        .Cells(StartRow, 19).Resize(ScaleQty, 1) = "3"
        .Cells(StartRow, 20).Resize(ScaleQty, 1) = "1000"
        .Cells(StartRow, 21).Resize(ScaleQty, 1) = "J0"
        
        .Cells(StartRow, 23).Resize(ScaleQty, 1) = "100"
        .Cells(StartRow, 24).Resize(ScaleQty, 1) = "=today()-1"
        Columns("X:X").Select
         Selection.Copy
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("A2").Select
        .Cells(StartRow, 25).Resize(ScaleQty, 1) = "9999-12-31"
        .Cells(StartRow, 26).Resize(ScaleQty, 1) = "USD"
        .Cells(StartRow, 27).Resize(ScaleQty, 1) = Application.WorksheetFunction.Transpose(DataSht.Cells(1, Scale1Col).Resize(1, ScaleQty))
        .Cells(StartRow, 28).Resize(ScaleQty, 1) = "EA"
        
        TempArr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(DataSht.Cells(i, Scale1Col).Resize(1, ScaleQty).Value))
        For j = 1 To UBound(TempArr)
        TempArr(j) = TempArr(j) * 100 / DataSht.Cells(i, 3)
        Next
        
        .Cells(StartRow, 29).Resize(ScaleQty, 1) = Application.WorksheetFunction.Transpose(TempArr)
        .Cells(StartRow, 29).Resize(ScaleQty, 1).NumberFormatLocal = "0.00_ "
        .Cells(StartRow, 22).Resize(ScaleQty, 1) = .Cells(StartRow, 29)
        .Cells(StartRow, 22).Resize(ScaleQty, 1).NumberFormatLocal = "0.00_ "
    
    
    End With

Next

End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here