VBA For循环与If循环 [英] VBA For loop with If loop

问题描述

我的VBA代码有问题。我尝试比较2列，A列和B列。如果一些数据匹配，比如我们说A2包含B3中的文本，那么我需要比较单元格C2和列D.我不明白为什么，但是我得到错误End If without block If。

以下是我的代码：

  Sub Compare（）
 For i = 1 To 100 
 For j = 1 To 50 
 If InStr（1，ActiveSheet.Cells（i，1）.Value，ActiveSheet.Cells j，2）.Value，vbTextCompare）<>如果InStr（1，ActiveSheet.Cells（i，3）.Value，ActiveSheet.Cells（k，4）.Value，vbTextCompare）<> 0，则对于k = 1到20 
， 0 Then MsgBox i 
 End If 
 Next k 
 End If 
 Next j 
 Next i 
 
 End Sub 
 < code $> 




解决方案

我发现if语句的结构有点混乱，当然，你可以做一个循环作为一个单一的班轮这样摆脱所有结束如果。对于它的价值，我认为这个代码更容易一些：



  Sub Compare（）
对于我= 1到100 
对于j = 1到50 
如果InStr（1，ActiveSheet.Cells（i，1）.Value，ActiveSheet.Cells（j，2）.Value，vbTextCompare）<> ; 0然后
对于k = 1到20 
如果InStr（1，ActiveSheet.Cells（i，3）.Value，ActiveSheet.Cells（k，4）.Value，vbTextCompare）< 0 Then MsgBox i 
 Next k 
 End If 
 Next j 
 Next i 
 
 End Sub 
  

运行w / oa编译错误，但不能评论它是否符合您的要求。

I have an issue with my VBA code. I try to compare 2 columns, both A and B columns. If some data match, for example let's say that A2 contains text in B3, then I need to compare the cell C2 with the column D. I don't understand why but I get the error "End If without block If". Thanks a lot for you help guys.

Here is my code :

Sub Compare()
For i = 1 To 100
   For j = 1 To 50
     If InStr(1, ActiveSheet.Cells(i, 1).Value, ActiveSheet.Cells(j, 2).Value, vbTextCompare) <> 0 _
     Then For k = 1 To 20
        If InStr(1, ActiveSheet.Cells(i, 3).Value, ActiveSheet.Cells(k, 4).Value, vbTextCompare) <> 0 Then MsgBox i
        End If
     Next k
     End If
   Next j
Next i

End Sub  

解决方案

I found the structure of your if statements a bit confusing and I'm not entirely sure you can do a for loop as a one-liner like that to get rid of all the end ifs. For what it's worth, I think this code is a bit easier to follow:

Sub Compare()
For i = 1 To 100
   For j = 1 To 50
     If InStr(1, ActiveSheet.Cells(i, 1).Value, ActiveSheet.Cells(j, 2).Value, vbTextCompare) <> 0 Then
        For k = 1 To 20
            If InStr(1, ActiveSheet.Cells(i, 3).Value, ActiveSheet.Cells(k, 4).Value, vbTextCompare) <> 0 Then MsgBox i
        Next k
    End If
   Next j
Next i

End Sub

This runs w/o a compile error, but can't comment if it does what you want it to do.

这篇关于VBA For循环与If循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！