excel - 如果满足条件，请选择要复制并粘贴到另一个单元格中的范围

Question

Dim word As Variant
Dim range As Variant


If cells(5, [6]) = "Bulk Density" Then
range = ("A3:K11")


For Each word In range
        If word = "Moisture Content" Then
            [M20] = word.Offset(0, 1)
            Exit For
        End If
        Next
        End If
End Sub

您好我正在尝试基于单元格选择一个范围f5。f5然后，如果在我想选择某个范围（ ）中遇到该词A3:K11，我可以从中复制数据并将其移动到另一个单元格。例如，如果单元格f5包含“体积密度”，那么我想复制单元格范围内的水分含量值（并不总是在同一行中），然后将该值粘贴到M20. 当我自动放入范围时，值的复制和粘贴有效，但是当我希望它选择范围本身时，单词变量始终为空，我不知道为什么。任何帮助将不胜感激，感谢max

标签： excelvba

Answer 1

一种查找

• 第一个 Sub 解决您的直接案例 ( Bulk Density ...)。
• 如果您想将相同的逻辑应用于其他“属性”，请编辑第三个 Sub（调用第二个 Sub）以满足您的需要。

编码

Option Explicit

Sub writeValueConst()

    Const CheckString As String = "Bulk Density"
    Const CheckCellAddress As String = "F6"
    Const Searchstring As String = "Moisture Content"
    Const SearchRangeAddress As String = "F3:F11"
    Const ColumnOffset As Long = 1
    Const WriteCellAddress As String = "M20"
    
    Dim cel As Range
    If Range(CheckCellAddress).Value = CheckString Then
        For Each cel In Range(SearchRangeAddress).Cells
            If cel.Value = Searchstring Then
                Range(WriteCellAddress).Value _
                  = cel.Offset(0, ColumnOffset).Value
                Exit For
            End If
        Next cel
    End If

End Sub

Sub writeValue(ByVal CheckString As String, _
               ByVal CheckCellAddress As String, _
               ByVal Searchstring As String, _
               ByVal SearchRangeAddress As String, _
               ByVal WriteCellAddress As String, _
               Optional ByVal ColumnOffset As Long = 1)

    Dim cel As Range
    If Range(CheckCellAddress).Value = CheckString Then
        For Each cel In Range(SearchRangeAddress).Cells
            If cel.Value = Searchstring Then
                Range(WriteCellAddress).Value _
                  = cel.Offset(0, ColumnOffset).Value
                Exit For
            End If
        Next cel
    End If

End Sub

Sub writeDensities()
    Dim Series(1) As Variant
    
    ' If you add or remove, you have to adjust the previous number after Series.
    Series(0) = Array("Bulk Density", "F6", "Moisture Content", _
                      "F3:F11", "M20", 1)
    Series(1) = Array("Dry Density", "F8", "Degree of Compaction", _
                      "F3:F11", "M21", 1)
    
    Dim j As Long
    For j = 0 To UBound(Series)
        writeValue Series(j)(0), Series(j)(1), Series(j)(2), _
                   Series(j)(3), Series(j)(4), Series(j)(5)
    Next j

End Sub