带斜率函数的无效限定符错误

Dim n As Long
Dim MyRange As Range
Set MyRange = ActiveSheet.UsedRange
Dim TargetSheet As Worksheet, SourceSheet As Worksheet
Dim TargetBook As Workbook
Set TargetBook = Application.ActiveWorkbook
Set TargetSheet = Application.ActiveSheet
For n = 3 To MyRange.Rows.Count
    TargetSheet.Cells(n, (MyRange.Columns.Count) + 1).Value = Application.WorksheetFunction.Slope(TargetSheet.Range(TargetSheet.Cells(n, 5), TargetSheet.Cells(n, MyRange.Columns.Count)), TargetSheet.Range(TargetSheet.Cells(1, 5), TargetSheet.Cells(1, MyRange.Columns.Count))).Value
Next n
End With
End Sub

Sub main()
Dim n As Long
Dim MyRange As Range
Set MyRange = ActiveSheet.UsedRange
Dim TargetSheet As Worksheet, SourceSheet As Worksheet
Dim TargetBook As Workbook
Set TargetBook = Application.ActiveWorkbook
Set TargetSheet = Application.ActiveSheet
For n = 2 To MyRange.Rows.Count
    TargetSheet.Cells(n, (MyRange.Columns.Count) + 1).Value = _
        Application.WorksheetFunction.Slope(TargetSheet.Range(TargetSheet.Cells(n, 1), TargetSheet.Cells(n, MyRange.Columns.Count)), _
        TargetSheet.Range(TargetSheet.Cells(1, 1), TargetSheet.Cells(1, MyRange.Columns.Count)))
Next n
End Sub

Option Explicit
Sub main()
Dim n As Long
Dim MyRange As Range
Dim nRows As Long, nCols As Long, ColCount As Long
Set MyRange = ActiveSheet.UsedRange
Dim TargetSheet As Worksheet, SourceSheet As Worksheet
Dim TargetBook As Workbook
Set TargetBook = Application.ActiveWorkbook
Set TargetSheet = Application.ActiveSheet
nRows = MyRange.Rows.Count
nCols = MyRange.Rows(1).End(xlToRight)
For n = 2 To nRows
    ColCount = Application.CountIf(TargetSheet.Range(TargetSheet.Cells(n, 1), Sheet1.Cells(n, nCols)), """<>""""""")
    If ColCount > 1 Then
        TargetSheet.Cells(n, (MyRange.Columns.Count) + 1).Value = _
            Application.Slope(TargetSheet.Range(TargetSheet.Cells(n, 1), TargetSheet.Cells(n, MyRange.Columns.Count)), _
            TargetSheet.Range(TargetSheet.Cells(1, 1), TargetSheet.Cells(1, MyRange.Columns.Count)))
    End If
Next n
End Sub

Dim n As Long, o As Range, CurrentRow As Range, NonEmptyCellCountRow As Integer
For n = 3 To MyRange.Rows.Count `Within each row, counting non-empty cells
    Set CurrentRow = TargetSheet.Range(TargetSheet.Cells(n, 5), TargetSheet.Cells(n, MyRange.Columns.Count))
    NonEmptyCellCountRow = 0
    For Each o In CurrentRow
        If o.Value <> "" Then NonEmptyCellCountRow = NonEmptyCellCountRow + 1
    Next o
    If NonEmptyCellCountRow < 2 Then _
        TargetSheet.Cells(n, (MyRange.Columns.Count) + 1) = "Insufficient Data"

带斜率函数的无效限定符错误

2 个回答