excel - 我想用与单元格输入链接的变量替换数字，但出现错误

Question

我已经用我的代码的一部分发布了这个问题。我想用我的双变量 z 替换以下公式中的 0.5。

.Cells(21, 6).Formula = "=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">""&0.5*MAX(RC[-4]:R[" & Total & "]C[-4]))"

我的整个代码如下所示：

Sub ImportMultipleTextFile()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim InputTextFile As Variant
Dim SourceDataFolder As String
Dim OutputDataFolder As String
Dim wb As Workbook: Set wb = ThisWorkbook
Dim LastRow As Integer
Dim MyFolder As String
Dim x As Integer
Dim Total As Long
x = 3
Dim Dateiname As String
Dim z As Double
z = Worksheets(1).Range("O1").Value

If MsgBox("Kistler Drehdaten?", vbYesNo) = vbYes Then
    MsgBox "Wähle den Kistler Ordner aus mit den .txt Dateien"


With Application.FileDialog(msoFileDialogFolderPicker)
       .AllowMultiSelect = False
       .Show
       MyFolder = .SelectedItems(1)
       Err.Clear
    End With
                                                        
SourceDataFolder = MyFolder



'Loop through each text file in source folder
InputTextFile = Dir(SourceDataFolder & "\*.txt")
While InputTextFile <> ""
Workbooks.OpenText Filename:=SourceDataFolder & "\" & InputTextFile, DataType:=xlDelimited, Tab:=True


Dateiname = ActiveWorkbook.Name
Range("A:E").Copy
wb.Worksheets(1).Range("A:E").PasteSpecial
Total = wb.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

Dim avgSourceRangePart As String
avgSourceRangePart = "RC[-4]:R[" & Total & "]C[-4]"
Dim maxPart As String
maxPart = "MAX(RC[-4]:R[" & Total & "]C[-4])"
Dim avgConditionPart As String
avgConditionPart = """>"" & " & z & " * " & maxPart & ")"


With wb.Worksheets(1)
.Cells(21, 6).Formula = "=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">""&0.5*MAX(RC[-4]:R[" & Total & "]C[-4]))"
'.Cells(21, 7).Formula = "=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">"" &" & z & "*MAX(RC[-4]:R[" & Total & "]C[-4]))"
'.Cells(21, 7).Formula = "=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">"" &" & z & "*MAX(RC[-4]:R[" & Total & "]C[-4]))"
.Cells(21, 7).FormulaR1C1 = "=AVERAGEIF(" & avgSourceRangePart & ";" & avgConditionPart & ")"
.Cells(21, 8).Formula = "=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">""&0.5*MAX(RC[-4]:R[" & Total & "]C[-4]))"
.Range("F21:H21").Copy
End With

    x = x + 1
Range("A:I").Clear
'Close the opened input file
Workbooks(InputTextFile).Close
InputTextFile = Dir

Wend
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End If
End Sub

....后跟另一个具有相同样式的elseif，用于需要打开的不同文本包。

标签： excelvbaexcel-formula

Answer 1

Continuing from my previous answer - The line you're looking for is:

"=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">"" &" & z & "*MAX(RC[-4]:R[" & Total & "]C[-4]))"

But there are other problems with the code that cause errors.

The main problem was with Range("A:E").Copy. The range was not qualified, meaning it was referring to the wrong book. See the corrected code below:

Sub ImportMultipleTextFile()
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim InputTextFile As Variant
    Dim SourceDataFolder As String
    Dim OutputDataFolder As String
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim LastRow As Integer
    Dim MyFolder As String
    Dim x As Integer:    x = 3
    Dim Total As Long
    Dim TxtFile As Workbook
    Dim z As Double
    
    z = Worksheets(1).Range("O1").Value
    
    If MsgBox("Kistler Drehdaten?", vbYesNo) = vbYes Then
        MsgBox "Wähle den Kistler Ordner aus mit den .txt Dateien"
    
    
        With Application.FileDialog(msoFileDialogFolderPicker)
               .AllowMultiSelect = False
               .Show
               MyFolder = .SelectedItems(1)
               Err.Clear
        End With
                                                                
        SourceDataFolder = MyFolder
        
        
        
        'Loop through each text file in source folder
        InputTextFile = Dir(SourceDataFolder & "\*.txt")
        While InputTextFile <> ""
            Workbooks.OpenText Filename:=SourceDataFolder & "\" & InputTextFile, DataType:=xlDelimited, Tab:=True
            Set TxtFile = Application.Workbooks(InputTextFile)
            
            TxtFile.Sheets(1).Range("A:E").Copy
            wb.Worksheets(1).Range("A:E").PasteSpecial
            Total = wb.Worksheets(1).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
            
            With wb.Worksheets(1)
                .Cells(21, 6).Formula = "=AVERAGEIF(RC[-4]:R[" & Total & "]C[-4],"">"" &" & z & "*MAX(RC[-4]:R[" & Total & "]C[-4]))"
                .Range("F21:H21").Copy
            End With
            
            Range("A:I").Clear
            'Close the opened input file
            TxtFile.Close
            InputTextFile = Dir
        
        Wend
    End If
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub