双击自动填充 - 基于相邻单元格的动态 [英] Double-click autofill - dynamic based on adjacent cell

问题描述

我需要做：

我正在Excel中使用正常的自动填充功能（双击单元格侧面的点）将内容复制到子单元格，因此在这个例子单击单元格A1中的点将执行以下操作：

I am using the normal auto-fill function in Excel (double click the dot on the side of a cell) to copy the contents to the sub cells, so in this case clicking the dot in Cell A1 will do this:

我需要一个脚本，在整个列中重复该过程，直到相邻单元格中没有更多的值。 >

I need a script that will repeat the process down the entire column, until there are no more values in the adjacent cell.

推荐答案

大概这是你要找的：

Option Explicit
Sub FillInTheBlanks()
    Dim StartCell As Range, EndCell As Range
    Set StartCell = ActiveCell
    Set EndCell = ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count + 1, StartCell.Offset(0, 1).Column).End(xlUp)

    Dim currentText As String
    Dim i As Long
    For i = StartCell.Row To EndCell.Row
        If Not IsEmpty(ActiveSheet.Cells(i, StartCell.Row)) Then
            currentText = ActiveSheet.Cells(i, StartCell.Row).Text
        Else
            ActiveSheet.Cells(i, StartCell.Row).Value = currentText
        End If
    Next i
End Sub

该代码执行以下操作：

如果你真的想你的屏幕截图是什么，那么你需要这样做：

If you really want what's in your screenshot, then you'll need to do this:

Option Explicit
Sub FillInTheBlanks()
    Dim StartCell As Range, EndCell As Range, NextCell As Range
    Set StartCell = ActiveCell
    Set EndCell = Cells(ActiveSheet.UsedRange.Rows.Count + 1, StartCell.Offset(0, 1).Column).End(xlUp)

    While StartCell.Row < EndCell.Row
        Set NextCell = StartCell.Offset(1, 1).End(xlDown).Offset(0, -1)
        StartCell.AutoFill Destination:=ActiveSheet.Range(StartCell, NextCell), Type:=xlFillDefault
        Set StartCell = NextCell.Offset(1, 0)
    Wend
End Sub

这是什么：

这篇关于双击自动填充 - 基于相邻单元格的动态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！