Home > Enterprise >  How to add values of a loop to new rows of a table in Excel?
How to add values of a loop to new rows of a table in Excel?

Time:10-05

I'm a macro newbie and I'm just making a simple macro program using a nested for loop and an excel table. I have attached my code here.

What I want is when I input P,Q, and SGF values, I want to see results for each loop indices in row wise in "Table4". (My table has four columns and they should be displyed a,b,Y and (SGF-Y) values.) But this code only creates a one new row and shows results of the last loop value. (When a=10 and b=10) But I actually need 100 results. Hope you understand my question. Thank you!

[![enter image description here][1]][1]

Dim SGF As Single
Dim Y As Single
Dim a As Single
Dim b As Single
Dim P As Single
Dim Q As Single
Dim ws As Worksheet
Dim newrow As ListRow
Set ws = ActiveSheet
Set newrow = ws.ListObjects("Table4").ListRows.Add

P = Val(InputBox("Enter SG1 Value:"))
Q = Val(InputBox("Enter SG2 Value:"))
SGF = Val(InputBox("Enter SGF Value:"))

For a = 1 To 10
    For b = 1 To 10
        Y = 0
        Y = Val((P * a)   (Q * b))
        With newrow
            .Range(1) = a
            .Range(2) = b
            .Range(3) = Y
            .Range(4) = Val(SGF - Y)
        End With
    Next b
Next a
End Sub


  [1]: https://i.stack.imgur.com/jqWbC.png

CodePudding user response:

This does what you want (to the best of my interpretation thereof)

  • Related