按钮点击查找空单元格 [英] Button click find empty cell

问题描述

我很高兴VBA，并尝试创建一个表单来让用户填写。我遇到一个循环问题，找到缺少的单元格这是表格的样子这些字段分为2色红色和绿色。红色是强制单元格，绿色是可选字段。
如您所见，用户缺少一些必需的单元格。因此，我想为用户创建一个按钮，以检查他们应该在检查后填写哪一个。我想突出显示缺少的单元格，以便他们知道他们填写的单元格。这是表应该看起来像点击检查按钮

现在我的问题是我用于每个循环或for循环来找出缺少的单元格，但我只能找到那些单元格设置范围如

  dim rng As range 
 
 
设置rng =范围（B3：j10）
  

但这是一个用户填写表单这是一个动态工作表，我永远不会知道用户将填写多少数据。如果我使用我的代码，工作表将不会停止突出显示所有空单元格。
我想要检查验证可以检测并突出强制（红色字段）缺少填充，但不是所有的空单元格。即使一个新用户填写表单，检查按钮仍然可以正常工作

这是我的代码：

  sub CommandButton1_click（）
 
 Dim rng As range 
 Dim found，emptyCell 
 
设置rng =范围（B3：J10 ）
 emptyCell = IsEmpty（emptyCell）
 found = false 
 
对于每个emptyCell在rng 
如果emptyCell.Cells =然后
 found = True 
 emptyCell.Interopr.ColorIndex = 6 
 Else 
 emptyCell.Interopr.ColorIndex = 0 
如果
 End Sub 
  

解决方案

应该这样做。

相交函数测试两个范围是否相交。如果他们不交叉，那么它不会做任何事情。

  Sub CommandButton1_click（）
 
 Dim rng As Range 
 Dim found As Boolean，emptyCell As Range 
 Dim lastrow As Long 
 
 lastrow = Range（B3）。end（xldown）.row 
 
设置rng =范围（B3：J& lastrow）
'emptyCell = IsEmpty（emptyCell）
 found = False 
 
每个emptyCell在rng 
如果不相交（emptyCell，Range（B4：B& lastrow&，D4：E& lastrow&，H4：I& lastrow））Is Nothing Then 
 If emptyCell.value =然后
 found = True 
 emptyCell.Interior.ColorIndex = 6 
 Else 
 emptyCell.Interior.ColorIndex = 0 
 End If 
结束如果
下一个
 End Sub 
  

I am new to excel VBA and trying to do create a form to let user fill in.I got a problem of loop to find missing cell It is how the table look like The fields separate by 2 color red and green. Red is mandatory cells and Green is optional field. As you can see, there are some mandatory cells is missing by user. Therefore, I would like to create a button for user to check which one they should fill in after the checking. I would like to highlight the missing cell so they will know which one they fill in. It is the table should look like after clicking the check button

Now my problem is I used for each loop or for loops to find out the missing cells but i can only find those cells by setting the range like

dim rng As range 


Set rng = Range("B3:j10")

but It is a user fill in form It is a dynamic worksheet that I will never know how many data user will fill in. If i use my code, the worksheet will just non stop highlighting all the empty cells. I want the check validation can be detect and highlight the mandatory(Red fields) missing fill but not ALL the empty cells. Even a new user fill in the form the check button can still work properly

It is my code:

sub CommandButton1_click()

Dim rng As range 
Dim found, emptyCell

Set rng = Range("B3:J10")
emptyCell = IsEmpty(emptyCell)
found = false

For each emptyCell In rng
If emptyCell.Cells = "" Then
found = True
emptyCell.Interopr.ColorIndex = 6
Else 
emptyCell.Interopr.ColorIndex = 0
End If
End Sub

解决方案

This should do it.

The intersect function tests whether the two ranges cross. If they do not cross then it does not do anything.

Sub CommandButton1_click()

Dim rng As Range
Dim found As Boolean, emptyCell As Range
Dim lastrow As Long

lastrow = Range("B3").end(xldown).row

Set rng = Range("B3:J" & lastrow)
'emptyCell = IsEmpty(emptyCell)
found = False

For Each emptyCell In rng
If Not Intersect(emptyCell, Range("B4:B" & lastrow & ",D4:E" & lastrow & ",H4:I" & lastrow)) Is Nothing Then
    If emptyCell.value = "" Then
        found = True
        emptyCell.Interior.ColorIndex = 6
    Else
        emptyCell.Interior.ColorIndex = 0
    End If
End If
Next
End Sub

这篇关于按钮点击查找空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！