excel - 从引用的列中选择最后一个填充的单元格

Question

我试图找到最后一个空列并写下列名。代码通过它的名称找到这个新列，然后选择第二个单元格并将 B 列和 C 列的值粘贴到 exceldown。但是，代码用连字符“-”填充了 D 列的所有空白单元格。我想以某种方式定义最后一个填充的单元格并粘贴到 B 列的最后一个填充单元格。我面临语法错误。任何线索我如何实现它？谢谢！

表“水果蔬菜”

	一种	乙	C
1	类别	水果	蔬菜
2	空白的	苹果	菠菜
3	空白的	橙子	卷心菜

工作表“ FruitsVege ”中的所需结果

	一种	乙	C	D
1	类别	水果	蔬菜	水果蔬菜
2	空白的	苹果	菠菜	苹果菠菜
3	空白的	橙子	卷心菜	橙白菜

编码

Sub Merge_FV ()
  
Dim r1 As Range, r2 As Range, r3 As Range

Dim emptyColumn As Long


'find empty Column (actually cell in Row 1)'
emptyColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
emptyColumn = emptyColumn + 1
End If

 Cells(1, emptyColumn).Value = "FruitsVege"

With Rows(1)
    Set r1 = .Find(What:="Fruits", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set r2 = .Find(What:="Vegetables", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set r3 = .Find(What:="FruitsVege", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
     If Not r1 Is Nothing And Not r2 Is Nothing And Not r3 Is Nothing Then
         r3.Offset(1, 0).Select
         Range(ActiveCell, ActiveCell.End(xlDown)).Formula = "=" & r1.Offset(1).Address(0, 0) & " & ""-"" & " & r2.Offset(1).Address(0, 0)
    End If
End With

     
End Sub

标签： excelvba

Answer 1

未经测试但应该可以工作 - 我刚刚修改了公式行。您需要从工作表底部向上查找最后一行，无需选择任何内容。

Sub Merge_FV()
  
Dim r1 As Range, r2 As Range, r3 As Range

Dim emptyColumn As Long, LastRow As Long

'find empty Column (actually cell in Row 1)'
emptyColumn = Cells(1, Columns.Count).End(xlToLeft).Column
If emptyColumn > 1 Then
    emptyColumn = emptyColumn + 1
End If

Cells(1, emptyColumn).Value = "FruitsVege"

With Rows(1)
    Set r1 = .Find(What:="Fruits", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set r2 = .Find(What:="Vegetables", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    Set r3 = .Find(What:="FruitsVege", Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not r1 Is Nothing And Not r2 Is Nothing And Not r3 Is Nothing Then
        LastRow = Cells(Rows.Count, r1.Column).End(xlUp).Row
        r3.Offset(1, 0).Resize(LastRow - 1).Formula = "=" & r1.Offset(1).Address(0, 0) & " & ""-"" & " & r2.Offset(1).Address(0, 0)
    End If
End With

End Sub