Multiplication in VBA For Loop-CodePudding

have a problem, I need to create a multiplication table range from 11 to 20 but it needs to be output in B2 not in its generic value, so when I write in my code using a for loop I can not get it to output the results anywhere else in the sheet and I need it to output the result table to B2.

The output goes from K11 to T20 , but I need these results to output from B1 to K10. Any help will be much appreciated!

Here's what I've got so far:

Private Sub CommandButton1_Click()
Dim i As Integer, x As Integer
For i = 11 To 20
For x = 11 To 20
cells(i, x) = i * x
Next
Next
End Sub

CodePudding user response：

Try this

Private Sub CommandButton1_Click()
Dim i As Integer, x As Integer
For i = 1 To 10
For x = 2 To 11
Cells(i , x) = (i   10) * (x   9)
Next
Next
End Sub

CodePudding user response：

A good option it to set some variables beforehand and then use offset:

Sub Main()
        
    Dim ST_RG As Range  'Start range, this is where your values will begin being output
    Dim Y As Long       'Y axis count
    Dim X As Long       'X axis count
    Dim lFrom As Long   'Count From
    Dim lTo As Long     'Count To
    
    ' Set all your parameters here:
    Set ST_RG = Sheet4.Range("A1")
    lFrom = 11
    lTo = 20
    
    For Y = lFrom To lTo
        For X = lFrom To lTo
            ST_RG.Offset(Y - lFrom, X - lFrom).Value = Y * X
        Next
    Next

End Sub

Example:

CodePudding user response：

Create Multiplication Table

The best place for the WriteMultiplicationTable procedure is in a standard module e.g. Module1, so you could easily call it from any other module.
Be careful to adjust the correct cell since you're first mentioning cell B2 and later the range B1:K10.

Option Explicit

Private Sub CommandButton1_Click()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    Dim fCell As Range: Set fCell = ws.Range("B1")
    WriteMultiplicationTable fCell, 11, 20
    
    ' Alternatively, if the command button is in the worksheet where
    ' you want to create the table, instead of the previous, you could use
    ' the following one-liner:
    'WriteMultiplicationTable Me.Range("B1"), 11, 20

End Sub

Sub WriteMultiplicationTable( _
        ByVal FirstCell As Range, _
        ByVal StartNumber As Long, _
        ByVal EndNumber As Long)
    
    Dim Data() As Long
    ReDim Data(StartNumber To EndNumber, StartNumber To EndNumber)
    
    Dim r As Long, c As Long
    
    For r = StartNumber To EndNumber
        For c = StartNumber To EndNumber
            Data(r, c) = r * c
        Next c
    Next r
    
    Dim nCount As Long: nCount = EndNumber - StartNumber   1
    
    FirstCell.Cells(1).Resize(nCount, nCount).Value = Data
    
End Sub