根据相邻单元格中的值填充单元格 [英] Fill cells based on value in adjacent cell
问题描述
我有这个工作的宏按钮,可以将B2:B15范围内的所有状态"重置"为默认提示,以供同事指示作业的状态:
I have this working macro button that will "reset all status" in the range B2:B15 to a default prompt for my colleagues to indicate job's status:
Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("B2:B15").Value = "please enter status"
End Sub
如果相邻的A列中没有值,我希望此宏停止在B列中填充值请输入状态".查看屏幕快照,它将B6:B15留为空白.A列的值是vlookup结果.如果没有结果,则vlookup返回零(当前在条件格式的帮助下以白色字体隐藏)
I want this macro to stop populating the value "please enter status" in the B column if there are no values in adjacent A column. Looking at screenshot it would leave B6:B15 empty. Column A values are vlookup results. If no result, vlookup returns zeros (currently hidden in white font with the help of conditional formatting)
感谢您的帮助!
推荐答案
也许是这样的:
Private Sub CommandButton1_Click()
Dim rng as Range
Set rng = Sheets("Sheet1").Range("A2:A15")
If Application.CountA(rng) > 0 Then
rng.SpecialCells(xlCellTypeConstants).Offset(,1).Value = "please enter status"
End If
End Sub
编辑:
如果列中的单元格是公式,则将 xlCellTypeConstants
更改为 xlCellTypeFormulas
.
If the cells in the column are formulas, then change xlCellTypeConstants
to xlCellTypeFormulas
.
编辑2 :
这是执行所需操作的简单方法:
This is a simple way to do what you're looking for:
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Sheets("Sheet1").Range("B2:B15")
rng.Formula = "=IF(A2<>0,""please enter status"", """")"
rng.Value = rng.Value
End Sub
或使用 Evaluate
:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
Dim rng As Range
Set rng = ws.Range("B2:B15")
rng.Value = ws.Evaluate("IF(A2:A15<>0,""please enter status"", """")")
End Sub
编辑3 :(第3次是魅力吗?)
EDIT 3: (3rd time's the charm?)
另一种选择是,如果找不到工作编号,则Vlookup返回一个空白字符串"
而不是 0
.
Another option would be to have the Vlookup return a blank string ""
instead of 0
if there's no job number found.
然后您可以利用 Range.SpecialCells
的第二个参数,如下所示(由@JvdV提出):
Then you could leverage the 2nd parameter of Range.SpecialCells
, like this (as proposed by @JvdV):
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Sheets("Sheet1").Range("A2:A15")
rng.SpecialCells(xlCellTypeFormulas, xlNumbers).Offset(, 1).Value = "please enter status"
End Sub
编辑4 :
您还可以使用 自动筛选
:
You can also make use of AutoFilter
:
Private Sub CommandButton1_Click()
With Sheets("Sheet1").Range("A1:B15")
.AutoFilter 1, ">0"
If .Cells.SpecialCells(12).Count > 2 Then .Offset(1).Resize(14, 2).Columns(2).Value = "Please enter status"
.AutoFilter
End With
End Sub
这篇关于根据相邻单元格中的值填充单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!