首页 文章

Excel VBA将公式粘贴到可变范围

提问于
浏览 次
1

我正在尝试创建一个VBA代码，将公式粘贴到列和单元格的可变范围 . 我有一个我认为可以修改的代码的开头，但我没有成功 .

我有一张表（见图），其可变范围介于A2和？之间 . 我需要将区域C3粘贴到行和列的末尾，这个公式将采用B中的值并将其除以列数 . 我以为我有一个开始，但我失败了 .

请协助 . “开始”代码遵循

Sub QtyByWks()
    Dim M As Long, N As Long, i As Long, x As Long, j As Long
    M = Sheet10.Cells(1, Columns.count).End(xlToLeft).Column
    N = Sheet10.Cells(Rows.count, "A").End(xlUp).Row
    j = 3
    For x = 1 To M
        For i = 1 To N
            If Cells(i, "B").Value > 0 Then
                Cells(j, "C").Value = Cells(i, "B").Value
                j = j + 2
            End If
        Next i
    Next x
End Sub

另请注意，“行”和“列”都可通过其他VBA进行变量[工作表捕获]

在此先感谢您的帮助

1: https://i.stack.imgur.com/xG6YN.png

excel vba excel-vba

1 回答

  • 0

    很难说你的代码有什么样的错误/问题，因为你没有提供太多的信息 . 无论哪种方式，我都会尝试调整你提供的内容来做我认为你想要做的事情：

    Sub QtyByWks()
    Dim M As Long, N As Long, i As Long, x As Long, j As Long
' Changed the formula to check row 2 instead of one, as per your screenshot.
    M = Sheet1.Cells(2, Columns.count).End(xlToLeft).Column
    N = Sheet1.Cells(Rows.count, "A").End(xlUp).Row
    j = 3
'Replaced the x loop with a j loop that increments by 2.  
    For j = 3 To N Step 2
'Had the i loop start from 3 instead of 1
        For i = 3 To M
            If Cells(j, "B").Value > 0 Then
'Divided the "B" value by the number of columns M, which is what it sounds like you were going for in your description.
                Cells(j, i).Value = Cells(j, "B").Value / (M - 2)
            End If
        Next i
    Next j
End Sub

    显然，代码正在假设Columns和Rows变量返回预期值 .

    回复于

相关问题