none
Run-time error '13': Type mismatch RRS feed

  • Question

  • Hi,I am a newer in VBA. And there are always the error:typr mismatch when running the following code,which is just the code segment:

    Dim j As Integer
    Dim m As Variant
    Dim n As Variant

    m = 0

     

    j = 1

    For j = 1 To 4

    Cells("&(NumRow + 2+18j)&", 2).Select    'the error occurs: "run-time error: Type mismatch
       ActiveCell.FormulaR1C1 = "Actin-P1(25uM)"
      
       Cells("&(NumRow + 3+18j)&", 2).Select
       ActiveCell.FormulaR1C1 = "Actin-P2(25uM)"
       
       Cells("&(NumRow + 4+18j)&", 2).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P1(25uM)"
      
       Cells("&(NumRow + 5+18j)&", 2).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P2(25uM)"
      

    Range("C" & (38 + "18j&") & ": F" & (54 + "18j&")).Select
       
        Selection.Cut
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.SmallScroll Down:=-6
        Range("P" & (3 + "18j&")).Select
        ActiveSheet.Paste
       
       
        Range("C" & (38 + "18j&") & ": F" & (53 + "18j&")).Select
        Selection.EntireRow.Delete
       
        
       
     NumRow = 1
      
       Cells(NumRow + 2 + "18j&", 15).Select
       ActiveCell.FormulaR1C1 = "Actin-P1(25uM)"
      
       Cells(NumRow + 3 + "18j&", 15).Select
       ActiveCell.FormulaR1C1 = "Actin-P2(25uM) "
      
       Cells(NumRow + 4 + "18j&", 15).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P1(25uM)"

           Next j

    Looking forward to your help. thanks!

    Wednesday, November 28, 2012 7:21 AM

Answers

  • Try removing the space before and after :B

        Range("B" & (NumRow + 2 + 18 * j) & ":B" & (NumRow + 18 + 18 * j)).Copy


    Regards, Hans Vogelaar

    • Marked as answer by Crystal WANG Thursday, November 29, 2012 7:48 AM
    Thursday, November 29, 2012 7:15 AM

All replies

  • Change

        Cells("&(NumRow + 2+18j)&", 2).Select
        ActiveCell.FormulaR1C1 = "Actin-P1(25uM)"

    to

        Cells(NumRow + 2 + 18 * j, 2).Value = "Actin-P1(25uM)"

    Similarly, change

        Range("C" & (38 + "18j&") & ": F" & (54 + "18j&")).Select
        Selection.Cut

    to

        Range("C" & (38 + 18 * j) & ":F" & (54 + 18 * j)).Cut

    etc. etc.

    Regards, Hans Vogelaar

    Wednesday, November 28, 2012 7:55 AM
  • Hi Crystal,

    Make that:

    Cells((NumRow + 2 + 18 * j),2).Value = "Actin-P1(25uM)"

    Note that the select part isn't needed. Also, you are just putting in a value in the cell so I used Value instead of FormulaR1C1.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Wednesday, November 28, 2012 8:07 AM
  • Hi, the solution did work and thanks so much.

    Another error occurs,however. My code is :

    Function get_File_Path(title_name As String) As Variant

    Dim fn As Variant


        fn = Application.GetOpenFilename("txt-files,*.txt", _
            1, "Select One Or More Files To Open", , True)
           
        If TypeName(fn) = "Boolean" Then Exit Function

        get_File_Path = fn

    FileFormat = xlOpenXMLWorkbookMacroEnabled

    End Function

     


    Sub AutoProc()


    Dim fileList As Variant
    Dim filename As String
    Dim NumRow As Integer
    Dim f As Integer

     
     
      NumRow = 1

       
      fileList = get_File_Path("raw data")
     
      For f = 2 To UBound(fileList)

     filename = fileList(f)

     

        Workbooks.OpenText filename:= _
           filename _
            , Origin:=xlWindows, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, Comma:=False, _
            Space:=True, Other:=True, OtherChar:="=", FieldInfo:= _
            Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
            , 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1)), _
            TrailingMinusNumbers:=True
           
           
        Range("L171").Select
        ActiveWindow.SmallScroll Down:=159
        Range("C320:C474").Select
        ActiveWindow.SmallScroll Down:=-144
        Range("C320:C474,E320:E474").Select
        Range("E320").Activate
        Selection.Copy
        ActiveWindow.SmallScroll Down:=-141
        Range("J316").Select
        ActiveSheet.Paste
        Columns("K:K").Select
        Range("K309").Activate
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("J316:M468").Select
        Selection.Copy
         'Windows("Book2").Activate
       
        Windows("E_B5_121010_C_autoextract&plot.xlsx").Activate
        Sheets("Data").Activate
       
        
        Cells(1, 3).Select
     
        'NumRow = Selection.End(xlDown).Row
           
        Cells(NumRow + 2, 3).Select
        ActiveSheet.Paste

     

     


        Dim filesource As String
        Dim p As Integer
        Dim s As Integer
        Dim a As Long
       
        p = 1
        a = 1
       
        Do Until p = 0
          
           p = InStr(a, filename, "\", 1)
           If p = 0 Then Exit Do
           s = p
           a = p + 1
       
        Loop
       
        filesource = Mid(filename, s + 1)
       
        p = InStr(1, filesource, ".", 1)
       
        filesource = Left(filesource, p - 1)
       
       
        Cells(NumRow + 2, 1).Select
        ActiveCell.FormulaR1C1 = filesource
                                                                               'extract file name from the file path which included

      
       Cells(NumRow + 2, 2).Select
       ActiveCell.FormulaR1C1 = "Actin-P1(25uM)"
      
       Cells(NumRow + 3, 2).Select
       ActiveCell.FormulaR1C1 = "Actin-P2(25uM) "
      
       Cells(NumRow + 4, 2).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P1(25uM)"
      
       Cells(NumRow + 5, 2).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P2(25uM)"
      
       Cells(NumRow + 6, 2).Select
       ActiveCell.FormulaR1C1 = "RNaseP3mRNA(25uM)"
      
       Cells(NumRow + 7, 2).Select
       ActiveCell.FormulaR1C1 = "InfA-M001(25uM)"
      
       Cells(NumRow + 8, 2).Select
       ActiveCell.FormulaR1C1 = "InfA-M002(25uM)"
      
       Cells(NumRow + 9, 2).Select
       ActiveCell.FormulaR1C1 = "InfA-M003(25uM)"
      
      
       Cells(NumRow + 10, 2).Select
       ActiveCell.FormulaR1C1 = "InfA-P1(25uM)_no-S"
     
       Cells(NumRow + 11, 2).Select
       ActiveCell.FormulaR1C1 = "RSV-P3(25uM)"
     
       Cells(NumRow + 12, 2).Select
       ActiveCell.FormulaR1C1 = "InfA-H504(25uM)"
      
       Cells(NumRow + 13, 2).Select
       ActiveCell.FormulaR1C1 = "PolyA(10uM)"
      
       Cells(NumRow + 14, 2).Select
       ActiveCell.FormulaR1C1 = "PolyA(25uM)"
      
      
       Cells(NumRow + 15, 2).Select
       ActiveCell.FormulaR1C1 = "NDV(25uM)"
      
       Cells(NumRow + 16, 2).Select
       ActiveCell.FormulaR1C1 = "PolyC(10uM)"
      
       Cells(NumRow + 17, 2).Select
       ActiveCell.FormulaR1C1 = "PolyC(25uM)"
      
       Cells(NumRow + 18, 2).Select
       ActiveCell.FormulaR1C1 = "Buffer"
      
      
      
      
     
        Range("B" & (NumRow + 2) & ":B" & (NumRow + 18)).Select
       Selection.Copy
       Range("D" & (NumRow + 2)).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
          
          
       Cells(NumRow + 1, 3).Select
       ActiveCell.FormulaR1C1 = "Raw Data"
      
       Cells(NumRow + 1, 4).Select
       ActiveCell.FormulaR1C1 = "Target probes"
      
       Cells(NumRow + 1, 5).Select
       ActiveCell.FormulaR1C1 = "Group1_Is"
      
       Cells(NumRow + 1, 6).Select
       ActiveCell.FormulaR1C1 = "SD"
      
       Cells(NumRow + 1, 7).Select
       ActiveCell.FormulaR1C1 = "Is-NDV_25"
      
       Cells(NumRow + 1, 8).Select
       ActiveCell.FormulaR1C1 = "Is-PolyC_10"
      
       Cells(NumRow + 1, 9).Select
       ActiveCell.FormulaR1C1 = "Is-PolyC_25"
      
       Cells(NumRow + 1, 10).Select
       ActiveCell.FormulaR1C1 = "Is-Buffer"
      
       Cells(NumRow + 1, 11).Select
       ActiveCell.FormulaR1C1 = "Is/(NDV_25)"
      
       Cells(NumRow + 1, 12).Select
       ActiveCell.FormulaR1C1 = "Is/(PolyC_10"
      
       Cells(NumRow + 1, 13).Select
       ActiveCell.FormulaR1C1 = "Is/(PolyC_25)"
      
       Cells(NumRow + 1, 14).Select
       ActiveCell.FormulaR1C1 = "Is/Buffer"


      Range("C" & (NumRow + 1) & ":N" & (NumRow + 1)).Select
          
        With Selection.Font
            .Name = "Verdana"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
        End With
        Selection.Font.Bold = False
        Selection.Font.Bold = True

        Range("E" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 10
        Range("F" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 13
        Range("G" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("H" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("I" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("J" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("K" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7
        Range("L" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7
        Range("M" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7
        Range("N" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7

     

     


        Dim i As Integer
       
        i = 0
           
        For i = NumRow + 2 To NumRow + 18
       
       
        Cells(i, 5).Select
        ActiveCell.FormulaR1C1 = "=255-RC[-2]"
       
        Next i
       
        'calculate Is - Inc
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 7).Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-R[" & (15 - i + NumRow) & "]C[-2]"
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 8).Select
        ActiveCell.FormulaR1C1 = "=RC[-3]-R[" & (16 - i + NumRow) & "]C[-3]"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 9).Select
        ActiveCell.FormulaR1C1 = "=RC[-4]-R[" & (17 - i + NumRow) & "]C[-4]"
       
           
       
        Next i
       
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 10).Select
        ActiveCell.FormulaR1C1 = "=RC[-5]-R[" & (18 - i + NumRow) & "]C[-5]"
       
           
       
        Next i
        
        
       
        
        'calculate Is/CO
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 11).Select
        ActiveCell.FormulaR1C1 = "=RC[-6]/(R[" & (15 - i + NumRow) & "]C[-6]+R[" & (15 - i + NumRow) & "]C[-5]*3)"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 12).Select
        ActiveCell.FormulaR1C1 = "=RC[-7]/(R[" & (16 - i + NumRow) & "]C[-7]+R[" & (16 - i + NumRow) & "]C[-6]*3)"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 13).Select
        ActiveCell.FormulaR1C1 = "=RC[-8]/(R[" & (17 - i + NumRow) & "]C[-8]+R[" & (17 - i + NumRow) & "]C[-7]*3)"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 14).Select
        ActiveCell.FormulaR1C1 = "=RC[-9]/(R[" & (18 - i + NumRow) & "]C[-9]+R[" & (18 - i + NumRow) & "]C[-8]*3)"
       
           
       
        Next i

     
       
       
        For i = NumRow + 2 To NumRow + 14

       
       
        Range("K" & (i)).Select
        Selection.NumberFormatLocal = "0.0000_ "
        Range("L" & (i)).Select
        Selection.NumberFormatLocal = "0.0000_ "
        Range("M" & (i)).Select
        Selection.NumberFormatLocal = "0.0000_ "
        Range("N" & (i)).Select
        Selection.NumberFormatLocal = "0.0000_ "
       
        Next i
       
         
        Dim t As Long
        Dim u As Long
        Dim c As String
       
        t = InStr(1, filesource, "_", 1)
       
        u = InStr(t + 1, filesource, "_", 1)

        c = Mid(filesource, t + 1, u - t - 1)

       
       
        Cells(NumRow + 2, 14).Select
        ActiveCell.FormulaR1C1 = c
        
       

       
        Cells(NumRow + 18, 7).Select
        ActiveCell.FormulaR1C1 = "=AVERAGE(R[-16]C:R[-4]C)"
       
        Range("G" & (NumRow + 18) & "").Select
        Selection.AutoFill Destination:=Range("G" & (NumRow + 18) & ":N" & (NumRow + 18) & ""), Type:=xlFillDefault
       
        Range("G" & (NumRow + 18) & ":J" & (NumRow + 18) & "").Select
        Selection.NumberFormatLocal = "0.00_ "

        Range("K" & (NumRow + 18) & ":N" & (NumRow + 18) & "").Select
        Selection.NumberFormatLocal = "0.0000_ "
        
        
        
       Next f


     Range("C20:F36").Select
       
        Selection.Cut
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.SmallScroll Down:=-6
        Range("P3").Select
        ActiveSheet.Paste

    Range("C20:F35").Select

    Selection.EntireRow.Delete

     


        
        
                                                                        
       NumRow = 1
      
       Cells(NumRow + 2, 15).Select
       ActiveCell.FormulaR1C1 = "Actin-P1(25uM)"    'for groupII
      
       Cells(NumRow + 3, 15).Select
       ActiveCell.FormulaR1C1 = "Actin-P2(25uM) "
      
       Cells(NumRow + 4, 15).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P1(25uM)"
      
       Cells(NumRow + 5, 15).Select
       ActiveCell.FormulaR1C1 = "GAPDH-P2(25uM)"
      
       Cells(NumRow + 6, 15).Select
       ActiveCell.FormulaR1C1 = "RNaseP3mRNA(25uM)"
      
       Cells(NumRow + 7, 15).Select
       ActiveCell.FormulaR1C1 = "InfA-M001(25uM)"
      
       Cells(NumRow + 8, 15).Select
       ActiveCell.FormulaR1C1 = "InfA-M002(25uM)"
      
       Cells(NumRow + 9, 15).Select
       ActiveCell.FormulaR1C1 = "InfA-M003(25uM)"
      
      
       Cells(NumRow + 10, 15).Select
       ActiveCell.FormulaR1C1 = "InfA-P1(25uM)_no-S"
     
       Cells(NumRow + 11, 15).Select
       ActiveCell.FormulaR1C1 = "RSV-P3(25uM)"
     
       Cells(NumRow + 12, 15).Select
       ActiveCell.FormulaR1C1 = "InfA-H504(25uM)"
      
       Cells(NumRow + 13, 15).Select
       ActiveCell.FormulaR1C1 = "PolyA(10uM)"
      
       Cells(NumRow + 14, 15).Select
       ActiveCell.FormulaR1C1 = "PolyA(25uM)"
      
      
       Cells(NumRow + 15, 15).Select
       ActiveCell.FormulaR1C1 = "NDV(25uM)"
      
       Cells(NumRow + 16, 15).Select
       ActiveCell.FormulaR1C1 = "PolyC(10uM)"
      
       Cells(NumRow + 17, 15).Select
       ActiveCell.FormulaR1C1 = "PolyC(25uM)"
      
       Cells(NumRow + 18, 15).Select
       ActiveCell.FormulaR1C1 = "Buffer"

     

    Range("O" & (NumRow + 2) & ":O" & (NumRow + 18)).Select
       Selection.Copy
       Range("Q" & (NumRow + 2)).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
          
           NumRow = 1
       Cells(NumRow + 1, 16).Select
       ActiveCell.FormulaR1C1 = "Raw Data"
      
       Cells(NumRow + 1, 17).Select
       ActiveCell.FormulaR1C1 = "Target probes"
      
       Cells(NumRow + 1, 18).Select
       ActiveCell.FormulaR1C1 = "Group2_Is"
      
       Cells(NumRow + 1, 19).Select
       ActiveCell.FormulaR1C1 = "SD"
      
       Cells(NumRow + 1, 20).Select
       ActiveCell.FormulaR1C1 = "Is-NDV_25"
      
       Cells(NumRow + 1, 21).Select
       ActiveCell.FormulaR1C1 = "Is-PolyC_10"
      
       Cells(NumRow + 1, 22).Select
       ActiveCell.FormulaR1C1 = "Is-PolyC_25"
      
       Cells(NumRow + 1, 23).Select
       ActiveCell.FormulaR1C1 = "Is-Buffer"
      
       Cells(NumRow + 1, 24).Select
       ActiveCell.FormulaR1C1 = "Is/(NDV_25)"
      
       Cells(NumRow + 1, 25).Select
       ActiveCell.FormulaR1C1 = "Is/(PolyC_10"
      
       Cells(NumRow + 1, 26).Select
       ActiveCell.FormulaR1C1 = "Is/(PolyC_25)"
      
       Cells(NumRow + 1, 27).Select
       ActiveCell.FormulaR1C1 = "Is/Buffer"


      Range("P" & (NumRow + 1) & ":AA" & (NumRow + 1)).Select
          
        With Selection.Font
            .Name = "Verdana"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
        End With
        Selection.Font.Bold = False
        Selection.Font.Bold = True

        Range("R" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 10
        Range("S" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 13
        Range("T" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("U" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("V" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("W" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 5
        Range("X" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7
        Range("Y" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7
        Range("Z" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7
        Range("AA" & (NumRow + 1)).Select
        Selection.Font.ColorIndex = 7

     

       
        i = 0
       
        NomRow = 1
           
        For i = NumRow + 2 To NumRow + 18
       
       
        Cells(i, 18).Select
        ActiveCell.FormulaR1C1 = "=255-RC[-2]"
       
        Next i

     

     


       
        i = 1
       
        NomRow = 1
       
       
        'calculate Is - Inc
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 20).Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-R[" & (15 - i + NumRow) & "]C[-2]"
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 21).Select
        ActiveCell.FormulaR1C1 = "=RC[-3]-R[" & (16 - i + NumRow) & "]C[-3]"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 22).Select
        ActiveCell.FormulaR1C1 = "=RC[-4]-R[" & (17 - i + NumRow) & "]C[-4]"
       
           
       
        Next i
       
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 23).Select
        ActiveCell.FormulaR1C1 = "=RC[-5]-R[" & (18 - i + NumRow) & "]C[-5]"
       
           
       
        Next i
       
       
       
       
        'calculate Is/CO
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 24).Select
        ActiveCell.FormulaR1C1 = "=RC[-6]/(R[" & (15 - i + NumRow) & "]C[-6]+R[" & (15 - i + NumRow) & "]C[-5]*3)"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 25).Select
        ActiveCell.FormulaR1C1 = "=RC[-7]/(R[" & (16 - i + NumRow) & "]C[-7]+R[" & (16 - i + NumRow) & "]C[-6]*3)"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 26).Select
        ActiveCell.FormulaR1C1 = "=RC[-8]/(R[" & (17 - i + NumRow) & "]C[-8]+R[" & (17 - i + NumRow) & "]C[-7]*3)"
       
           
       
        Next i
       
       
        For i = NumRow + 2 To NumRow + 14
       
       
        Cells(i, 27).Select
        ActiveCell.FormulaR1C1 = "=RC[-9]/(R[" & (18 - i + NumRow) & "]C[-9]+R[" & (18 - i + NumRow) & "]C[-8]*3)"
       
           
       
        Next i

     


     

    NumRow = 1
    Cells(NumRow + 18, 20).Select
        ActiveCell.FormulaR1C1 = "=AVERAGE(R[-16]C:R[-4]C)"
       
       
        Cells(NumRow + 18, 24).Select
        ActiveCell.FormulaR1C1 = "=AVERAGE(R[-16]C:R[-4]C)"
        Cells(NumRow + 18, 25).Select
        ActiveCell.FormulaR1C1 = "=AVERAGE(R[-16]C:R[-4]C)"
       
       Cells(NumRow + 18, 26).Select
        ActiveCell.FormulaR1C1 = "=AVERAGE(R[-16]C:R[-4]C)"
        Cells(NumRow + 18, 27).Select
        ActiveCell.FormulaR1C1 = "=AVERAGE(R[-16]C:R[-4]C)"
        Range("T" & (NumRow + 18) & "").Select
        Selection.AutoFill Destination:=Range("T" & (NumRow + 18) & ":W" & (NumRow + 18) & ""), Type:=xlFillDefault
       
        Range("T" & (NumRow + 18) & ":W" & (NumRow + 18) & "").Select
        Selection.NumberFormatLocal = "0.00_ "

        Range("X" & (NumRow + 18) & ":AA" & (NumRow + 18) & "").Select
        Selection.NumberFormatLocal = "0.0000_ "

     

    Dim k As Variant

    Dim j As Integer
    Dim m As Variant
    Dim n As Variant


    m = 0

     

    j = 1
    'If IsNumeric(Cells("&(NumRow + 2+18j)&", 2).Value) = True Then
    For j = 1 To 4

     

     



       Cells((NumRow + 2 + 18 * j), 2).Value = "Actin-P1(25uM)"
       

       Cells((NumRow + 3 + 18 * j), 2).Value = "Actin-P2(25uM) "
       
      
       Cells((NumRow + 4 + 18 * j), 2).Value = "GAPDH-P1(25uM)"
       
     
       Cells((NumRow + 5 + 18 * j), 2).Value = "GAPDH-P2(25uM)"
       
      
       Cells((NumRow + 6 + 18 * j), 2).Value = "RNaseP3mRNA(25uM)"
       
       
       Cells((NumRow + 7 + 18 * j), 2).Value = "InfA-M001(25uM)"
       

       Cells((NumRow + 8 + 18 * j), 2).Value = "InfA-M002(25uM)"
       
       
      Cells((NumRow + 9 + 18 * j), 2).Value = "InfA-M003(25uM)"
       
       
       
       Cells((NumRow + 10 + 18 * j), 2).Value = "InfA-P1(25uM)_no-S"
      
       
       Cells((NumRow + 11 + 18 * j), 2).Value = "RSV-P3(25uM)"
      

       Cells((NumRow + 12 + 18 * j), 2).Value = "InfA-H504(25uM)"
       
     
      Cells((NumRow + 13 + 18 * j), 2).Value = "PolyA(10uM)"
       

       Cells((NumRow + 14 + 18 * j), 2).Value = "PolyA(25uM)"
       
       
      
       Cells((NumRow + 15 + 18 * j), 2).Value = "NDV(25uM)"
       
     
       Cells((NumRow + 16 + 18 * j), 2).Value = "PolyC(10uM)"
       
       
       Cells((NumRow + 17 + 18 * j), 2).Value = "PolyC(25uM)"
       
       
       Cells((NumRow + 18 + 18 * j), 2).Value = "Buffer"

     


       
     
        Range("B" & (NumRow + 2 + 18 * j) & " :B " & (NumRow + 18 + 18 * j)).Copy     //here the Method 'Range'of object '_Global' failed coming
       
       Range("D" & (NumRow + 2 + 18 * j)).Select
       Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
           :=False, Transpose:=False
           
           

       Cells(NumRow + 1 + 18 * j, 3).Value = "Raw Data"
     
       Cells(NumRow + 1 + 18 * j, 4).Value = "Target probes"
       
       
       Cells(NumRow + 1 + 18 * j, 5).Value = "Group(2j+1)_Is"
       

       Cells(NumRow + 1 + 18 * j, 6).Value = "SD"
       
      
       Cells(NumRow + 1 + 18 * j, 7).Value = "Is-NDV_25"
       
       Cells(NumRow + 1 + 18 * j, 8).Value = "Is-PolyC_10"
       

       Cells(NumRow + 1 + 18 * j, 9).Value = "Is-PolyC_25"
       
       
      Cells(NumRow + 1 + 18 * j, 10).Value = "Is-Buffer"
       
       Cells(NumRow + 1 + 18 * j, 11).Value = "Is/(NDV_25)"
       
      
       Cells(NumRow + 1 + 18 * j, 12).Value = "Is/(PolyC_10"
       

       Cells(NumRow + 1 + 18 * j, 13).Value = "Is/(PolyC_25)"
       
       
      Cells(NumRow + 1 + 18 * j, 14).Value = "Is/Buffer"


     

      


        
        
        i = 0
           
        For i = NumRow + 2 + 18 * j To NumRow + 18 + 18 * j
       
       
        Cells(i, 5).Select
        ActiveCell.FormulaR1C1 = "=255-RC[-2]"
       
        Next i
       
        'calculate Is - Inc
       
        For i = NumRow + 2 + 18 * j To NumRow + 14 + 18 * j
       
       
        Cells(i, 7).Select
        ActiveCell.FormulaR1C1 = "=RC[-2]-R[" & (15 - i + NumRow + 18 * j) & "]C[-2]"
           
       
        Next i
       
       
        For i = NumRow + 2 + 18 * j To NumRow + 14 + 18 * j
       
       
        Cells(i, 8).Select
        ActiveCell.FormulaR1C1 = "=RC[-3]-R[" & (16 - i + NumRow + 18 * j) & "]C[-3]"
       
           
       
        Next i
       
       
        For i = NumRow + 2 + 18 * j To NumRow + 14 + 18 * j
       
       
        Cells(i, 9).Select
        ActiveCell.FormulaR1C1 = "=RC[-4]-R[" & (17 - i + NumRow + 18 * j) & "]C[-4]"
       
           
       
        Next i
       
       
       
        For i = NumRow + 2 + 18 * j To NumRow + 14 + 18 * j
       
       
        Cells(i, 10).Select
        ActiveCell.FormulaR1C1 = "=RC[-5]-R[" & (18 - i + NumRow + 18 * j) & "]C[-5]"
       
           
       
        Next i
        
       

     

    ActiveWindow.Visible = False
        Windows("E_B5_121010_C_autoextract&plot.xls").Activate

     

     

    If f = UBound(fileList) Then
            f = 0
           
        ElseIf f = 1 Then
       
            Exit Sub
           
        End If
      
     
       Next j
       
     

      
      
      
      
      
    End Sub

     

     

     

     

    Any reply is welcome! Thanks in advance.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

       

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Thursday, November 29, 2012 2:00 AM
  • Try removing the space before and after :B

        Range("B" & (NumRow + 2 + 18 * j) & ":B" & (NumRow + 18 + 18 * j)).Copy


    Regards, Hans Vogelaar

    • Marked as answer by Crystal WANG Thursday, November 29, 2012 7:48 AM
    Thursday, November 29, 2012 7:15 AM
  • Yes! It does work! Lots of thanks.I am so grateful.....
    Thursday, November 29, 2012 7:51 AM