在excel vba中如何将 "col "值解析为Const NameCol?

我想使用相同的”殖民地“字符串从第一个子到”Const NameCol“中的第二个子,试了好几种方法都不能成功。我很着急,任何解决这个问题的方法都会对我很有帮助。我在最后一次运行中得到的错误是”编译错误。需要表达式“在第二个子上。如果我写上”const Namecol = “B” “它将工作,但它不与col字符串链接.下面是代码。

Sub CopyTest()
    Dim ws As Worksheet
    Dim wsnew As Worksheet
    Dim i As Integer
    Dim j As Integer
    Dim col As String

    col = InputBox("Enter column name")
    Set ws = ActiveWorkbook.Worksheets("Copy of Products-Export-2020-Ap")

    For i = 2 To ws.Range(col & ws.Rows.Count).End(xlUp).Row
    CopyConditional ws, ws.Range(col & i).Value
    Next i

End Sub

Sub CopyConditional(wshS As Worksheet, WhichName As String)
    Const Namecol = col
    Const FirstRow = 2

    Dim LastRow As Long
    Dim SrcRow As Long
    Dim TrgRow As Long
    Dim wshT As Worksheet

    On Error Resume Next
    Set wshT = Worksheets(WhichName)
    If wshT Is Nothing Then
        Set wshT = Worksheets.Add(After:=wshS)
        wshT.Name = WhichName
    End If
    On Error GoTo 0

    wshT.Rows.Clear
    wshS.Rows(1).Copy Destination:=wshT.Cells(1, 1)

    TrgRow = wshT.Cells(wshT.Rows.Count, Namecol).End(xlUp).Row + 1
    LastRow = wshS.Cells(wshS.Rows.Count, Namecol).End(xlUp).Row

    For SrcRow = FirstRow To LastRow
        If wshS.Cells(SrcRow, Namecol) = WhichName Then
            wshS.Cells(SrcRow, 1).EntireRow.Copy Destination:=wshT.Cells(TrgRow, 1)
            TrgRow = TrgRow + 1
        End If
    Next SrcRow

End Sub

解决方案:

根据定义,一个 恒定恒定 而不会改变。右边的 Const 声明 不能 是一个可变的表达式 col 是,因为用户正在输入它)。)

Const nameCol As String = "B" '<~ this is good
Const nameCol As String = col '<~ col can change ... so by definition, this is bad

为了解决你的问题,你可以给 CopyConditional 并传入 col.

Sub CopyConditional(ByVal wshS As Worksheet, ByVal WhichName As String, ByVal Namecol As String)
   ' remove the Const Namecol = col line

然后,当调用 CopyConditional:

CopyConditional ws, ws.Range(col & i).Value, col

给TA打赏
共{{data.count}}人
人已打赏
未分类

NodeJs中的Passport总是返回UnAuthorized。

2022-9-8 7:20:35

未分类

在VBScript中搜索字符串以验证单词的问题[重复]。

2022-9-8 7:20:37

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索