CODEWITHSUNDEEP
excelvba
Python_newbie
Python_newbie

Reputation: 111

Populate a table using VBA macros

enter image description hereenter image description hereI need to fill in the table in the image by plugging in the values of mass and acceleration in C15 and C16 respectively and copying the corresponding value of force from C17 to the table.

Any help will be appreciated.

Sub NestedLoop()

    Dim cell As Range, rgSource1 As Range, rgDestination1  As Range, cell2 As Range, rgSource2 As Range, rgDestination2 As Range

    Set rgSource1 = ThisWorkbook.Worksheets("sheetname").Range("A1:A6")
    Set rgSource2 = ThisWorkbook.Worksheets("sheetname").Range("B1:E1")
    Set rgDestination1 = ThisWorkbook.Worksheets("SHEETNAME").Range("C15")
    Set rgDestination2 = ThisWorkbook.Worksheets("SHEETNAME").Range("C16")
    For Each cell In rgSource2[![enter image description here][1]][1]
        For Each cell2 In rgSource1
        
        
    rgSource1.Copy
    rgDestination1.PasteSpecial xlPasteValues
    
    Next cell2
    
    rgSource2.Copy
    rgDestination2.PasteSpecial xlPasteValues

    Next cell
    
    
End Sub

Upvotes: 0

Views: 397

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Multiply First Row By First Column

  • By using an array, you can simplify the code and increase its efficiency.

The Code

Option Explicit

Sub Multiplication()
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
    Dim Data As Variant: Data = rng.Value
    Dim i As Long
    Dim j As Long
    For i = 2 To UBound(Data, 1)
        For j = 2 To UBound(Data, 2)
            Data(i, j) = Data(i, 1) * Data(1, j)
        Next j
    Next i
    rng.Value = Data
End Sub

Upvotes: 1

Chris Strickland
Chris Strickland

Reputation: 3490

It's a little difficult to answer your question without knowing something a little closer to the actual problem. I don't know which parts I can modify and which ones I can't. For instance, iterating through the cells copying and pasting seems like the wrong way to go about it, but I don't know exactly what you're trying to accomplish, so I don't know how to suggest. Notice in the code given here I don't paste the answer back, I just figure out where it needs to go and write it there. I have added a sheet object to make range assignment easier, although you can accomplish this entire task without ever using a range at all. Further, I would just about always prefer to work in r1c1 than a1.

Sub NestedLoop()

Dim cell As Range, rgSource1 As Range, rgDestination1  As Range, _
    cell2 As Range, rgSource2 As Range, rgDestination2 As Range

Dim this As Worksheet: Set this = ActiveSheet

Set rgSource1 = this.Range("A2:A6")
Set rgSource2 = this.Range("B1:E1")
Set rgDestination1 = this.Range("C15")
Set rgDestination2 = this.Range("C16")
Set rgResult = this.Range("c17")

For Each cell In rgSource2
    For Each cell2 In rgSource1
        
        cell.Copy
        rgDestination1.PasteSpecial xlPasteValues
        
        cell2.Copy
        rgDestination2.PasteSpecial xlPasteValues
        
        this.Cells(cell2.Row, cell.Column) = rgResult
        
        Next
    Next

End Sub

Here's the output:

enter image description here

Upvotes: 0

Related Questions