CODEWITHSUNDEEP
excelvbaloops
Gayantha Akalanka
Gayantha Akalanka

Reputation: 25

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

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.sstatic.net/jqWbC.png

Upvotes: 0

Views: 779

Answers (2)

Spinner
Spinner

Reputation: 1088

This does what you want (to the best of my interpretation thereof) 😊
Notes:
I've changed variable syntax to something I'm more comfortable with.
Change as you like. But it's good practice to include the data type in the name (e.g. 'in' or 'i' for integer)
Also: You don't need to 'set' ranges in most cases. And avoiding that can mean you're at less risk of breaking undo chain.

Sub Test()
    
''' When using whole numbers: Integers are best
    Dim in1%, in2%

''' Where fractions are required, doubles are best
''' Though you should consider rounding any result that involves doubles (or singles for that matter)
    Dim dbY#, dbSG1#, dbSG2#, dbSGF#
    
''' Getting unvalidated user inputs (You should consider validating)
    dbSG1 = Val(InputBox("Enter SG1 Value:"))
    dbSG2 = Val(InputBox("Enter SG2 Value:"))
    dbSGF = Val(InputBox("Enter dbSGF Value:"))
    
''' Expand the table per the algorithm
    For in1 = 1 To 10
        For in2 = 1 To 10
        ''' Calculate 'Y'
            dbY = Val((dbSG1 * in1) + (dbSG2 * in2))
            
        ''' Add results to table
            With ActiveSheet.ListObjects("Table4").ListRows.Add
                .Range(1) = in1
                .Range(2) = in2
                .Range(3) = dbY
                .Range(4) = dbSGF - dbY
            End With
        Next in2
    Next in1

End Sub

Upvotes: 1

Lokesh Kumar
Lokesh Kumar

Reputation: 82

    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

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))
        Set newrow = ws.ListObjects("Table4").ListRows.Add
        With newrow
            .Range(1) = a
            .Range(2) = b
            .Range(3) = Y
            .Range(4) = Val(SGF - Y)
        End With
    Next b
Next a
End Sub

Upvotes: 0

Related Questions