VBA使用變數呼叫單元格

我剛開始玩 Excel 宏，已經卡住了。

我有一個主表，其中的資料結構如下

France    10
Germany   14
US        20

然后我還有另外三張紙，分別是：France, Germany, US

我的目標是將數字復制到每個相應的作業表中。總是復制到單元格 O1 的主作業表上定義的同一個單元格（只是在不同的作業表上），我有 = B5，在單元格 P1 中，我有 = 3（因為我想要它 3 次）

我的想法是逐行瀏覽作業表并有兩個變數

country
value

例如，我設法將 France 放入 country 并將 10 放入 value

但是當我嘗試在回圈中執行此操作時，出現此錯誤（星星所在的位置）

錯誤 1004：物件“全域”的方法“范圍”失敗

Sub trial()
Dim destination As String
Dim inputer As Long
Dim country As String
Dim counter As Boolean
Dim maxcounter As Boolean

maxcounter = Range("P1").Value

counter = "1"

While maxcounter > counter:

    
  destination = Range("O1").Value

    **country = Range("A" & counter).Value**

    inputer = Range("B" & counter).Value

    Sheets(country).Range(destination).Value = inputer

    counter = counter   1
Wend

End Sub

uj5u.com熱心網友回復：

我沒有嘗試重現你的問題，但看了一眼你的代碼后，我想知道你為什么寫

Dim counter As Boolean Dim maxcounter As Boolean 計數器 = "1"

我認為您應該首先將型別從 Boolean 更改為 Integer，然后寫入 counter = 1（即使 VBA 可能以您希望的方式處理它，給定 Integer 型別）

可能是對未來的提示：如果你知道回圈將執行多少次，你可以考慮使用 For counter ... Next counter 而不是 counter = 1 While ... counter = counter 1 ... Wend

uj5u.com熱心網友回復：

Option Explicit

Sub trial()

    Dim destination As String
    Dim inputer As Long
    Dim country As String
    Dim counter As Long
    Dim maxcounter As Long
    
    Dim ws As Worksheet
    Set ws = Sheets(1) ' Main Sheet
    
    maxcounter = ws.Range("P1").Value
    destination = ws.Range("O1").Value
    
    counter = 1
    While counter <= maxcounter
    
        country = ws.Range("A" & counter).Value
        inputer = ws.Range("B" & counter).Value
        Sheets(country).Range(destination).Value = inputer
    
        counter = counter   1
    Wend
End Sub

uj5u.com熱心網友回復：

布林值是這里的問題。布林值用于 True/False 值Windows Boolean Data Type Reference。maxcounter 的定義很好，但如果您希望始終對整個表執行此操作，您可以將其更改為自動查找資料集的最后一行而不是特定的單元格。您還應該至少為包含資料表的作業表定義一個作業表變數。以下是我將如何使用相同的通用設計來完成此操作：

Sub trial()
Dim ws As Worksheet
Dim destination As String
Dim country As String
Dim counter As Long
Dim maxcounter As Long

Set ws = Sheets("Sheet1")
'Sheet1 is placeholder. Replace with whatever name of the sheet is where the data table is.

maxcounter = ws.Range("P1").Value
destination = ws.Range("O1").Value


For i = 1 To maxcounter
    country = ws.Cells(i, 1).Value
    Sheets(country).Range(destination).Value = ws.Cells(i, 2).Value
Next

End Sub

uj5u.com熱心網友回復：

復制值

Option Explicit

Sub CopyValues()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1") ' Source Worksheet
    
    Dim DestinationAddress As String ' (destination)
    DestinationAddress = sws.Range("O1").Value
    
    Dim LastRow As Long ' (maxcounter As Boolean)
    LastRow = sws.Range("P1").Value
    
    Dim r As Long ' (counter As Boolean)
    r = 1
    
    Dim dws As Worksheet ' Each Destination Worksheet (Object)
    Dim dCell As Range ' Each Destination Cell (Object)
    Dim Country As String ' (country)
    Dim Points As Long ' (inputer) ' use more appropriate than 'Points'
    
    Do While r <= LastRow ' 'Do...Loop' is an improved 'While...Wend'
        Country = sws.Range("A" & r).Value
        Points = sws.Range("B" & r).Value
        Set dws = wb.Worksheets(Country)
        Set dCell = dws.Range(DestinationAddress)
        dCell.Value = Points
        r = r   1
    Loop

    MsgBox "Data copied.", vbInformation

End Sub

標籤：擅长VBA

上一篇：使用JSONCOVERTER創建JSON

下一篇：使用VBA清除單元格中特定字符的文本