时间戳

首页 > 解决方案 > 根据变量更改列号

excel - 根据变量更改列号

问题描述

我有一些 VBA 代码将公式插入到一系列命名的 Excel 单元格中。这些公式用于从第二个工作簿 ( DoD_Options.xlsm) 中的特定工作表中获取值，其中要使用的工作表名称由具有结构的字符串变量 ( DrawingCode)的一部分定义DODss.sxww。"x" (DODss.s) 左侧的字符定义要使用的工作表名称（例如 DOD4.3、DOD8.7 等），而 "x" (nn) 右侧的字符间接确定要使用的工作表名称工作表中保存数据的列。的值nn可以是 7 个值（19、24、29、34、39、44、49）中的任何一个，并且对应于相应工作表中的 L、AP、BT、CX、EB、FF 和 GJ 列。

例如，当DrawingCode = DOD2.9x19，代码

Range("FS1.0_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]"" & LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$L$5"")*Dock_Count"

使用代码插入公式以从L5工作表中的单元格中获取值DOD2.9Drawingcode = DOD2.9x24

Range("FS1.0_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]"" & LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$AP$5"")*Dock_Count"

AP5从sheet中的单元格获取值DOD2.9。

目前，我有代码可以检查 7 个可能的值，nn如下所示：

If nn = "19" Then
Range("FS1.0_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]"" & LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$L$5"")*Dock_Count"
Range("FV1.04_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]"" & LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$L$6"")*Dock_Count"
.... etc for 18 more ranges

ElseIf nn = "24" Then
Range("FS1.0_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]"" & LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$AP$5"")*Dock_Count"
Range("FV1.04_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]"" & LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$AP$6"")*Dock_Count"
... etc for 18 more ranges

ElseIf nn = 29" Then
...etc
End If

nn有没有一种方法可以通过使用数组根据 的值自动设置列号来压缩编码，而不是为 的每个值设置多行几乎相同的代码nn？

标签： excelvbaoffice365

解决方案

动态列 ( Application.Match)

  • 根据您声明的方式nn，您可能需要将值放在引号中，例如“19”、“24”...
Sub matchNN()

    Dim nns As Variant
    nns = Array(19, 24, 29, 34, 39, 44, 49)
    Dim cols As Variant
    cols = Array("L", "AP", "BT", "CX", "EB", "FF", "GJ")
 
    Dim cIndex As Variant: cIndex = Application.Match(nn, nns, 0)
    
    If IsNumeric(cIndex) Then
        Range("FS1.0_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]""" _
            & "&LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$" _
            & cols(cIndex) & "$5"")*Dock_Count"
        Range("FV1.04_Count").Formula = "=INDIRECT(""[DoD_Options.xlsm]""" _
            & "&LEFT(DrawingCode,FIND(""x"",DrawingCode)-1)&""!$" _
            & cols(cIndex) & "$6"")*Dock_Count"
        '... etc for 18 more ranges
    Else
        MsgBox "nn not found"
    End If

End Sub

推荐阅读