比较不同工作表中的两个文本单元格 [英] Comparing two text cells in different sheets

问题描述

我试图从不同的表单比较两个文本单元格（如 abcDEF ）。

我的代码是：

  Private Sub Worksheet_Change（ByVal Target As Range）
 
 Dim i As Long，LastRow As Long，n As Long 
 Dim Project As String 
 Dim Responsible As String，Site As String，Sample As String，_ 
描述As String，参数为String，方法为String 
 Dim j As Long 
 
 Application.EnableEvents = False 
 
'在A中查找LastRow到Sheet2 
 LastRow = Sheet2.Range（A& Rows.Count）.End（xlUp）.Row 
 
' in project 
对于Sheet2.Range（A2：A& LastRow）中的每个值
 Project = Project& ，&值
下一个值
 
 Sheet1.Range（A2）。ClearContents：Sheet1.Range（A2）。Validation.Delete 
 
'创建数据验证列表
使用范围（A2）验证
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= xlBetween，Formula1：= Project 
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle =
 .ErrorTitle =
 .InputMessage =
 .ErrorMessage =
 .ShowInput = True 
 .ShowError = True 
结束于
 
'选择与单元格A2值一致的表格
对于j = 3到Sheets.Count 
 
如果Sheets（j）.Range（A2）。Text = Sheets（1）.Range（A2）。Text Then 
'当sheet1单元格C6 。 
 Sheet1.Range（C6）= 4 
结束如果
 
下一页j 
结束子
  

问题是如果... 行，可能是范围定义。我尝试过 .Text 和 .Value ，两者都不工作。

解决方案

您可能想使用的是

 如果StrComp j）.Range（A2）。Value2，Sheets（1）.Range（A2）。Value2，_ 
 vbTextCompare）= 0然后
' 
  

vbTextCompare 不区分大小写， vbBinaryCompare 区分大小写。有几个在线资源可帮助您字符串比较。

此外，我注意到您使用 Worksheet_Change 并更改 Sheet1上的单元格的值。我的猜测是你的 Worksheet_Change 是 Sheet1 ，是吗？如果是这样，那么每次修改 Sheet1 时，子会再次被调用（直到它崩溃）。要防止此情况，您要添加

  Application.EnableEvents = False 
  pre> 
 
 
到子开头，然后
  EnableEvents = True 
  
。这样，对工作表所做的任何更改都不会触发 Worksheet_Change 子。
 
I'm trying to compare two text cells (like abcDEF) from different sheets. One sheet is fixed but the other is not.

My code is:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Long, LastRow As Long, n As Long
Dim Project As String
Dim Responsible As String, Site As String, Sample As String, _
    Description As String, Parameter As String, Method As String
Dim j As Long

Application.EnableEvents = False

' Find LastRow in Col A into the Sheet2
LastRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row

' Select all Col A in Project
For Each Value In Sheet2.Range("A2:A" & LastRow)
   Project = Project & "," & Value
Next Value

Sheet1.Range("A2").ClearContents: Sheet1.Range("A2").Validation.Delete

' Create the Data Validation List
With Range("A2").Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween,     Formula1:=Project
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

' Select the sheet coinciding with the cell "A2" value
For j = 3 To Sheets.Count

    If Sheets(j).Range("A2").Text = Sheets(1).Range("A2").Text Then
        'Write 4 in sheet1 cell C6 when the two values are coinciding.
        Sheet1.Range("C6") = 4
    End If

Next j
End Sub
The problem is the If... line, probably is the range definition. I've tried .Text and .Value and neither works.
解决方案
What you may want to use is
If StrComp(Sheets(j).Range("A2").Value2, Sheets(1).Range("A2").Value2, _
    vbTextCompare) = 0 Then
'added the underscore since I made it two lines for neatness
vbTextCompare is case-insensitive, vbBinaryCompare is case-sensitive. There are several resources online about string comparison that can help you.

Also, I noticed you're using Worksheet_Change and changing the value of a cell on Sheet1. My guess is that your Worksheet_Change is for Sheet1, yes? If that's the case, then every time you modify Sheet1, the sub gets called again (and again and again... until it crashes). To prevent that, you want to add
Application.EnableEvents = False
to the start of the sub, then
Application.EnableEvents = True
at the end. This way any changes you make to the worksheet won't trigger the Worksheet_Change sub.

                        
这篇关于比较不同工作表中的两个文本单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！