如果两个单元格在一行中匹配，那么需要给出一个名字，下一行匹配的需要在excel中动态给出另一个名字vba

Question

我有点糊涂写excel vba代码。请帮助我。 这里是我的要求

如果两个细胞连续匹配的话，需要给一个名称，下一行匹配的需要在Excel VBA中给予另一名动态地通过循环

若小区A = 1和小区-B = 2那么弗里斯特名字，如果再次macthes下一行的第二个名字

我的代码是

Sub fill_name() 
    Dim i As Integer 
    Dim nm As String 
    Dim j As Integer 
    Dim f_name() As String 

    For i = 1 To 4 

     ReDim f_name(2) 
     For j = 1 To 2 
      f_name(j) = Cells(j, 12).Value 

      If Cells(i, 1).Value = "1" And Cells(i, 3).Value = "2" Then 
       Cells(i, 2).Value = f_name(j) 
      End If 

     Next j 
    Next i 

End Sub 

Answer 1

您不需要VBA。您可以通过一个公式

将这个公式中B2实现这一目标，将其拉下来

=IF(AND(A2=1,C2=2),IF(COUNTIF($B$1:$B1,"Hi")=COUNTIF($B$1:$B1,"Hello"),"Hi","Hello"),"") 

如果你仍然想使用VBA然后用上述公式这样

Sub fill_name() 
    Dim ws As Worksheet 
    Dim lRow As Long 
    Dim sFormula As String 

    sFormula = "=IF(AND(A2=1,C2=2),IF(COUNTIF(" & _ 
       "$B$1:$B1,""Hi"")=COUNTIF($B$1:$B1,""Hello"")" & _ 
       ",""Hi"",""Hello""),"""")" 

    '~~> Change this as per your requirement 
    Set ws = ThisWorkbook.Sheets("Sheet1") 

    With ws 
     lRow = .Range("A" & .Rows.Count).End(xlUp).Row 

     With .Range("B2:B" & lRow) 
      .Formula = sFormula 
      .Value = .Value 
     End With 
    End With 
End Sub 

如果你不想使用公式，但想循环，然后像这样使用它

Sub fill_name() 
    Dim ws As Worksheet 
    Dim lRow As Long 
    Dim i As Long, j As Long 
    Dim MyAr(1 To 2) As String 

    MyAr(1) = "Hi" 
    MyAr(2) = "Hello" 
    j = 1 

    '~~> Change this as per your requirement 
    Set ws = ThisWorkbook.Sheets("Sheet1") 

    With ws 
     lRow = .Range("A" & .Rows.Count).End(xlUp).Row 

     For i = 1 To lRow 
      If .Range("A" & i).Value = 1 And .Range("C" & i).Value = 2 Then 
       .Range("B" & i).Value = MyAr(j) 
       If j = 1 Then j = 2 Else j = 1 
      End If 
     Next i 
    End With 
End Sub