VBA use variables for calling cells-CodePudding

I am just starting to play with Excel macros and am already stuck.

I have a main sheet where data is structured like this

France    10
Germany   14
US        20

and then I have three other sheets called: France, Germany, US

My goal is to copy the number into each corresponding sheet. Always copying into the same cell (just on different sheets) defined on the main sheet in cell O1 I have = B5 and in cell P1 I have = 3 (because I want it of it 3 times)

My idea was to go through the sheet row by row and have two variables

country
value

I managed to put for example France into country and 10 into value

But when I try to do it in the loop I get this error (where the stars are)

error 1004: method "range" of object "global" failed

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

CodePudding user response：

I have not tried to reproduce yout problem, but after a short look a yout code I wonder why you write

Dim counter As Boolean Dim maxcounter As Boolean counter = "1"

I think you should first change the type from Boolean to Integer and then write counter = 1 (even if VBA might handle this in a way you want it to be, given the type in Integer)

May be a hint for the future: If you know how many times a loop will be executed you may consider to use the For counter ... Next counter instead of counter = 1 While ... counter = counter 1 ... Wend

CodePudding user response：

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

CodePudding user response：

Boolean is the issue here. Boolean is used for True/False values Windows Boolean Data Type Reference. Definition of maxcounter is fine, but if you want to always perform this on the entire table you could change this to automatically find the last row of the dataset instead of a specific cell. You also should at least define a sheet variable for the worksheet with your datatable. Here is how I would accomplish this using the same general design:

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

CodePudding user response：

Copy Values

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