用宏填充

问题描述

我正在尝试编写一个自动从单元格C4填充并填充到数据集中现有行数的宏。我在C4和F4中输入一个公式，并在格式化数据集之后（就在我将它作为表格之前），我想在这些公式中填充
。我发现它是在创建宏时填充到特定单元格，然后总是填充到该单元格。不幸的是，我的数据集每次都会改变记录的数量，所以它要么填充得太远，要么不够远。
我知道这是用户错误，但我不知道怎么写它以便它会填满。帮助!!

谢谢！

我的宏到目前为止如下（显然，我已经停用了我尝试过但失败的代码行，但它们在那里）：  

 列（"H：H"）。选择

    Application.CutCopyMode = False

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

   列（"I：M"）。选择

    Selection.Delete Shift：= xlToLeft

   列（"J：O"）。选择

    Selection.Delete Shift：= xlToLeft

   范围（"B1：G1"）。选择

    Selection.Cut目的地：=范围（"L1：Q1"）

   列（"B：B"）。选择

    Selection.Delete Shift：= xlToLeft

    Selection.Delete Shift：= xlToLeft

   列（"E：E"）。选择

    Selection.Delete Shift：= xlToLeft

    Selection.Insert Shift：= xlToRight

   列（"C：C"）。选择

    Selection.Insert Shift：= xlToRight

   范围（"C3"）。选择

    ActiveCell.FormulaR1C1 =" sku2"

   范围（"F3"）。选择

    ActiveCell.FormulaR1C1 =" qty calc"

   范围（"C4"）。选择

    ActiveCell.FormulaR1C1 =" = MID（RC [-1]，FIND（"" - ""，RC [-1]）+ 1，LEN（RC [-1]））"

   范围（"K1：P1"）。选择

    Selection.Cut目的地：=范围（"B1：G1"）

   范围（"F4"）。选择

  &NBSP; ActiveCell.FormulaR1C1 =" = IFERROR（IF（RC [-1]< R1C3,0，R1C4），0）"

  &NBSP;范围（"C4"）。选择

  &NBSP; 'Selection.End（x1Down）。选择

  &NBSP; 'ActiveCell.Offset（0,1）。选择

  &NBSP; '范围（选择，选择。结束（x1Up））。选择

  &NBSP; 'Selection.FillDown

  &NBSP; 'ActiveCell.Range（" A1：A3560"）。选择

  &NBSP;范围（"A3"）。选择

  &NBSP;范围（选择，选择。结束（xlToRight））。选择

  &NBSP;范围（选择，选择。结束（xlDown））。选择

  &NBSP;   Dim tbl As ListObject

  &NBSP;设置tbl = ActiveSheet.ListObjects.Add（xlSrcRange，Selection ,, xlYes）

  &NBSP; tbl.TableStyle =" TableStyleMedium15"

  &NBSP;范围（"A1"）。选择

End Sub

解决方案

I'm trying to write a macro that automatically fills down from cell C4 and fills to the amount of existing rows in a data set. I enter a formula into C4 and F4, and after formatting the data set (and right before I make it a table), I want to fill down those formulas. What I'm finding is that it fills down to a specific cell when creating the macro, and then always fills to that cell. Unfortunately, my data set changes the number of records each time, so it's either filling too far, or not far enough. I know it's user error, but I don't know how to write it so that it'll fill down. Help!!

Thank you!

My macro so far is as follows (obviously, I've deactivated the lines of code that I've tried and failed, but they're in there): 

  Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("I:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("J:O").Select
    Selection.Delete Shift:=xlToLeft
    Range("B1:G1").Select
    Selection.Cut Destination:=Range("L1:Q1")
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Insert Shift:=xlToRight
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "sku2"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "qty calc"
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""-"",RC[-1])+1,LEN(RC[-1]))"
    Range("K1:P1").Select
    Selection.Cut Destination:=Range("B1:G1")
    Range("F4").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[-1]<R1C3,0,R1C4),0)"
    Range("C4").Select
    'Selection.End(x1Down).Select
    'ActiveCell.Offset(0, 1).Select
    'Range(Selection, Selection.End(x1Up)).Select
    'Selection.FillDown
    'ActiveCell.Range("A1:A3560").Select
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
     Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
    tbl.TableStyle = "TableStyleMedium15"
    Range("A1").Select
End Sub

解决方案

这篇关于用宏填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！