如题所述
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 2 Then '判断改写的单元格为B2
Dim RowNums As Integer '行数变量
Dim ColNums As Integer '列数变量
'行数默认为2
RowNums = 2
'列数默认为3,从C列开始
ColNums = 3
'控制while循环
Dim bWhile As Boolean
bWhile = True
'死循环,判断B2后的单元格是否为空
While bWhile
If Cells(RowNums, ColNums) = "" Then '判断是否为空值
'如果为空,则填入B2的值
Cells(RowNums, ColNums) = Cells(2, 2)
'退出循环
bWhile = False
Else '如果不为空,则增加列数
ColNums = ColNums + 1
End If
Wend
End If
End Sub追问
If Target.Column = 2 And Target.Row = 2 Then '判断改写的单元格为B2
Dim RowNums As Integer '行数变量
Dim ColNums As Integer '列数变量
'行数默认为2
RowNums = 2
'列数默认为3,从C列开始
ColNums = 3
'控制while循环
Dim bWhile As Boolean
bWhile = True
'死循环,判断B2后的单元格是否为空
While bWhile
If Cells(RowNums, ColNums) = "" Then '判断是否为空值
'如果为空,则填入B2的值
Cells(RowNums, ColNums) = Cells(2, 2)
'退出循环
bWhile = False
Else '如果不为空,则增加列数
ColNums = ColNums + 1
End If
Wend
End If
End Sub追问
B2可以了,如果要B3 B4 B5......整个B列都要这样需要怎么改呢,谢谢
追答Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then '判断改写的单元格为B2
Dim RowNums As Integer '行数变量
Dim ColNums As Integer '列数变量
'行数默认为3
RowNums = Target.Row
'列数默认为3,从C列开始
ColNums = 3
'控制while循环
Dim bWhile As Boolean
bWhile = True
'死循环,判断B2后的单元格是否为空
While bWhile
If Cells(RowNums, ColNums) = "" Then '判断是否为空值
'如果为空,则填入B2的值
Cells(RowNums, ColNums) = Cells(RowNums, 2)
'退出循环
bWhile = False
Else '如果不为空,则增加列数
ColNums = ColNums + 1
End If
Wend
End If
End Sub
先试试吧,有bug的话再调整
温馨提示:答案为网友推荐,仅供参考