时间戳

首页 > 解决方案 > 如何创建一个可以将公式应用于动态单元格范围的宏，最好使用数组？

excel - 如何创建一个可以将公式应用于动态单元格范围的宏，最好使用数组？

问题描述


我对 VBA 完全陌生，所以这项任务对我来说有点困难，但我敢打赌对你们来说很容易。我正在尝试创建一个宏命令，该命令可以自动将一系列日期从文本转换为 excel 可以识别的日期格式。这是我经常执行的任务，因此让宏为我执行此任务会非常节省时间。基本上，我会定期下载时间序列，例如股票的历史价格。时间序列的长度每次都不同。接下来，我需要将下载数据中的日期转换为 excel 可以识别的格式。

为此，我使用以下代码：

=DATE(RIGHT(B2,4),MONTH("1 "&MID(B2,4,3)),LEFT(B2,2))

在与日期系列的第一行相邻的单元格中。然后我将这个公式自动填充到系列的末尾。

我使用以下代码创建了一个为我执行此任务的宏：

Sub FacsetDates()
' FacsetDates Macro
' Turn Factset dates into excel format
'
' Keyboard Shortcut: Ctrl+Shift+D

   ActiveCell.FormulaR1C1 = _
        "=+DATE(RIGHT(RC[-1],4),MONTH(""1 ""&MID(RC[-1],4,3)),LEFT(RC[-1],2))"
    Selection.End(xlToLeft).Select
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count - 1, ActiveCell.Column).End(xlUp).Row
    Selection.End(xlToRight).Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & Lastrow - 1)
    ActiveCell.Range("A1:A" & Lastrow - 1).Select
End Sub

我的问题是，此代码仅在日期系列从第 2 行开始时才有效。如果从第 1 行插入该系列，则自动填充将停止短行，如果该系列从第 3 行开始，则自动填充将填充out 一行太多（与数据系列的长度相比）

我想要一个无论数据系列从哪一行开始都可以工作的宏。例如，即使日期系列从 B10 开始，我也希望宏能够工作。我想解决方案是在VBA中将数据系列设置为一个数组，然后执行一个循环来操作每个文本字符串，然后最后将操作后的数据粘贴到相邻的列中。我已经开始生成以下代码：

Sub FSdate()

 Dim arrMarks() As Long
 Lastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
 ReDim arrMarks(1 To Lastrow)

Dim i As Long
 For i = LBound(arrMarks) To UBound(arrMarks)
    arrMarks(i) = ActiveCell
 Next i

我尝试首先定义数组及其大小，然后从活动单元格（作为数据系列的第一行）“复制”文本字符串，但此代码失败。在定义了数组之后，我想运行一个循环，使用上面的 DATE 函数来操作数组中的每一个条目。但是我目前在 VBA 方面的技能不足，我根本不知道如何进行。

任何人都可以帮助创建这样的代码吗？甚至，你们对完成这项任务的替代方法有意见吗？无论数据系列从哪一行开始，都可以操纵初始代码以使其工作。

我希望有人能够并且愿意帮助我！

标签： excelvba

解决方案

这是@Dave 答案的不同简化细分，因为您想使用您选择开始的单元格。第一的; 通过计算活动单元格左侧列中的行数来设置最后一行。第二; 设置从活动单元格到最后一行变量的范围。第三：将您的公式写入范围。注意：lRow - ActiveCell.Row + 1根据活动单元格的行号调整您的范围。

Dim lRow As Long
lRow = Cells(Rows.Count, ActiveCell.Offset(, -1).Column).End(xlUp).Row

ActiveCell.Resize(lRow - ActiveCell.Row + 1).FormulaR1C1 = "=+DATE(RIGHT(RC[-1],4),MONTH(""1 ""&MID(RC[-1],4,3)),LEFT(RC[-1],2))"

完成任务的更简单方法；通过覆盖当前文本将使用TextToColumns

ActiveSheet.Columns("F").TextToColumns Destination:=ActiveSheet.Columns("F"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True
    Columns("F").NumberFormat = "m/d/yyyy"

推荐阅读