时间戳

首页 > 解决方案 > 使用变体数组更新单元格

excel - 使用变体数组更新单元格

问题描述

在以下代码子集中，我试图通过在范围内每个单元格的开头添加撇号来让 Excel vba 更新第二列。如何引用每个单元格以在此行之后添加撇号：

myRange = .Range("b2:b").Value

这是到目前为止的其余代码：

> Private Sub cmdTest_Click()
> 
> Dim objExcel As Excel.Application Dim objWB As Excel.Workbook Dim
> objWBtoAdd As Excel.Workbook Dim newWS As Excel.Worksheet Dim objWS As
> Excel.Worksheet Dim i As Long Dim myRange As Range Dim Cell As Range
> 
>   Set objExcel = New Excel.Application   objExcel.Visible = True   Set
> objWB = Workbooks.Open("C:\TEST\Drop\Bank.xlsx", , False) Set
> objWBtoAdd = Workbooks.Add Set newWS = objWBtoAdd.ActiveSheet   Set
> objWS = objWB.ActiveSheet
>  
>     With objWS
>    
>         
>         myRange = .Range("b2:b").Value
>         
>         .Range("b2:b").Value = myRange
>         
>                  
>     
>     End With
> 
>  
> 
> 
> 
> End Sub

标签： excelvba

解决方案

你的代码：

  • 声明和分配objWBtoAdd，newWS但它们没有在任何地方使用
  • 变量i，cell也没有使用
  • 它只是尝试将无效范围分配.Range("b2:b").Value给另一个 Range 变量
  • 然后将 Range 变量恢复为无效范围，无需修改值

此表示法不是有效的范围：.Range("b2:b")

  • 它应该包含第一个单元格的地址 - "B2"& ":"& "B9"（范围内最后一个单元格的地址）

如果您需要“通过在范围内每个单元格的开头添加撇号来更新第二列”并将该列复制到新工作表，请尝试以下代码

Option Explicit

Private Sub cmdTest_Click()

    Const INI_PATH = "C:\TEST\Drop\Bank.xlsx"
    Const NEW_PATH = "C:\TEST\Drop\BankNew.xlsx"

    Dim xlApp As Excel.Application: Set xlApp = New Excel.Application
    Dim wbIni As Excel.Workbook:    Set wbIni = xlApp.Workbooks.Open(INI_PATH, , False)
    Dim wbNew As Excel.Workbook:    Set wbNew = xlApp.Workbooks.Add

    Dim ws As Excel.Worksheet, colBIni As Range, colBNew As Range, lr As Long

    'xlApp.Visible = True
    Set ws = wbIni.ActiveSheet  'Prefer explicit: Set ws = wbIni.Worksheets("Sheet3")

    lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    Set colBIni = ws.Range(ws.Cells(2, "B"), ws.Cells(lr, "B"))
    Set ws = wbNew.ActiveSheet  'Prefer explicit: Set ws = wbNew.Worksheets("Sheet1")
    Set colBNew = ws.Range(ws.Cells(2, "B"), ws.Cells(lr, "B"))

    Dim arr As Variant, r As Long

    arr = colBIni.Value2            'copy colB to array
    For r = 1 To UBound(arr)
        arr(r, 1) = "'" & arr(r, 1) 'modify array values
    Next
    'colBIni.Value2 = arr           'paste array to colB (Ini file)
    colBNew.Value2 = arr            'paste array to colB (New file)

    wbNew.Close SaveChanges:=True, Filename:=NEW_PATH   'Save new File
    wbIni.Close SaveChanges:=False
End Sub

.

如果 B 列仅包含公式或 URL，则不需要使用For循环

With ws.UsedRange.Columns("B")  'all cells with data (and formatting) in column B
    .Replace "=", "'="
    .Replace "https://", "'https://"
End With

推荐阅读