宏以自动向下填充到最后一个相邻的单元格 [英] Macro to Auto Fill Down to last adjacent cell

问题描述

我正在尝试在Excel中使用宏记录器来记录宏以填充一列单元格，但是由于每次填充的单元格数量不同，因此它要么填充得很短，要么填充得太长，这似乎是因为该宏标识了单元格范围及其固定范围.

I am trying to use macro recorder in Excel to record a macro to fill down a column of cells, however because the fill down each time is a different number of cells it either fills down to short or too long and this seems to be because the macro identifies the cell range and its fixed.

无论如何，我可以得到它来填充到最后一个填充的相邻单元格.例如.自动填充E列，直到到达D列中最后一个填充的行.我在这里查看了一些示例，但是代码看起来都非常不同，因此不确定是否可以使用宏记录器来完成，或者我必须找人来写一些代码代码还是必须手动完成?

Is there anyway that I can get it to fill down to the last populated neighbouring cell. E.g. AutoFill down column E until it reaches the last populated row in column D. I have looked at some examples on here but the code all looks very different so not sure if it can be done with macro recorder or I have to get someone to write some code or is it something that has to be done manually?

这是我在宏中的代码.

ActiveCell.FormulaR1C1 = _
        "=IF(MONTH(RC[-1])>3,"" ""&YEAR(RC[-1])&""-""&RIGHT(YEAR(RC[-1])+1,2),"" ""&YEAR(RC[-1])-1&""-""&RIGHT(YEAR(RC[-1]),2))"
    Selection.AutoFill Destination:=Range("E2:E1344")
    'Selection.AutoFill Destination:=Range("E2:E1344")
    Range("E2:E1344").Select

如果有人可以帮助我，我将非常感激

If anyone can help i'd be extremely grateful

推荐答案

未经测试....但应该可以.

Untested....but should work.

Dim lastrow as long

lastrow = range("D65000").end(xlup).Row

ActiveCell.FormulaR1C1 = _
        "=IF(MONTH(RC[-1])>3,"" ""&YEAR(RC[-1])&""-""&RIGHT(YEAR(RC[-1])+1,2),"" ""&YEAR(RC[-1])-1&""-""&RIGHT(YEAR(RC[-1]),2))"
    Selection.AutoFill Destination:=Range("E2:E" & lastrow)
    'Selection.AutoFill Destination:=Range("E2:E"& lastrow)
    Range("E2:E1344").Select

唯一的例外是您确定您的自动填充代码是完美的...

Only exception being are you sure your Autofill code is perfect...

这篇关于宏以自动向下填充到最后一个相邻的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！