Doing addition for a range of values between columns

[BenjaminLWX]

0.002456706 0.002802535 0.003163419 0.003538764 0.00392804 0.004330764 0.004746497 0.005174833 0.0056154 0.006067848 <tbody> </tbody>

-7.77544E-05 -0.000155509 -0.000233263 -0.000311018 -0.000388772 -0.000466527 -0.000544281 -0.000622035 -0.00069979 -0.000777544 <tbody> </tbody>

<tbody>
</tbody>

Hi all,

I have an issue with the subtracting of the values from column 1 and 2 of each row of values respectively.
For example: Taking Row 1 values, (0.002456706+(-7.77544E-05)) = Answer
Is there anyone who could guide me on how to code for this type of calculation? Thank you.

Best Regards,
Benjamin Lim

 

[JLGWhiz]

I am not sure what your specific issue is. The rules of math apply in Excel just as if you were using pevcil and paper. But in Excel you can use the cell reference rather than typing the entire value into a formula. ie. =a1+b1. If your ranges of data were column A and Column B beginning in row 2, you could put the following formula in cell C2 and copy it down the C column as far as you have data in the other two columns.

=A2+B2

This would put the results in column C.

 

[BenjaminLWX]

Thank you for your reply. Sorry if my post is confusing.
What I mean is that I want to code for the addition/subtraction between unlimited values of the two columns.
For example, if I input in 10 values in a userform for column A and B and press a button it should enable the program to calculate and produce the values in column C.

The current problem that I am having is that instead of having values from column A and B to minus each other respectively, all my values in column A is being subtracted using the last value in column B.

I apologize for the confusion. Would it be better if I try to take out the code and show you?

 

[JLGWhiz]

If you want assistance with existing code, by all means, post the code, or at least the relevant portion of the code that you are having problems with. If you only post a portion, be sure to include information separately of the variables and macro calls that are used but not defined in that portion of the code. Since you are using controls with the code you also need to clarify in which code module the procedure resides, ie. standard module, sheet module, userform module, ThisWorkbook module, etc.

 

[BenjaminLWX]

Private Sub TabOkay_Click()

Sheet5.Cells.Clear


'------------Capacity Table-----------------------------------------------
With Sheet5.Range("A5:I5").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
End With


Sheet5.Range("A5").Value = "Dip(mm)"
Sheet5.Range("D5").Value = "Dip(mm)"
Sheet5.Range("G5").Value = "Dip(mm)"
Sheet5.Range("B5").Value = "Capacity(m^3)"
Sheet5.Range("E5").Value = "Capacity(m^3)"
Sheet5.Range("H5").Value = "Capacity(m^3)"
Sheet5.Range("C5").Value = AddTab.Value
Sheet5.Range("F5").Value = AddTab.Value
Sheet5.Range("I5").Value = AddTab.Value


R = 100
dia_cyvol = 2 * R
number = 2


For number = 1 To CInt(2)


DF = Sheet4.Range("K" & number + 1).Value
DT = Sheet4.Range("L" & number + 1).Value




For h = 0 To dia_cyvol
Sheet5.Cells(h + 6, 1) = h


    If h <= DF Then
        Sheet5.Cells(h + 6, 2).Value = Sheet3.Cells(h + 2, 12).Value
    End If
    
[U][B]    If h > DF And h < DT Then[/B]
[B]        For A = DF To DT[/B]
[B]            Sheet3.Cells(2 + A, 14).Value = Sheet4.Cells(2, 14).Value * (A - DF)[/B]
[B]            Sheet5.Cells(h + 6, 2).Value = Sheet3.Cells(h + 2, 12) + Sheet3.Cells(2 + A, 14)[/B]
[B]        Next A[/B]
[B]        End If[/B][/U]

    If h >= DT Then
        If number = 1 Then
        Sheet5.Cells(h + 6, 2).Value = Sheet3.Cells(h + 2, 12) + Sheet4.Range("M" & number + 1).Value
        'ElseIf number > 1 Then
        'Sheet5.Cells(h + 6, 2) = Sheet3.Cells(h + 2, 12) + WorksheetFunction.Sum(Sheet4.Range("M2:M100"))
        End If
    End If
            
Next h
Next number
    
Sheet5.Columns("A:Z").AutoFit


End Sub

I am working on a userform to code for a capacity table. As seen in the bold and underlined section, it is the problem that I was talking about.
The "sheet3.cell(h+2,12)" is a list of values in column A whereas the "sheet3.cells(2+A,14)" is the list of values in column B. However, the problem was that the list of values in A are current being subtracted with the final value of column B. Is there any mistake or modification that I should make to allow the values to subtract one another respectively?

 

[JLGWhiz]

As I read the code, your 'h' variable and your 'A' variable control which cells are being accessed and when, with 'h' controlling the outer loop and 'A' controlling the inner loop. The inner loop must complete before the next iteration of the outer loop, which means the value of h does not change during the inner loop iterations.

 For A = DF To DT
            Sheet3.Cells(2 + A, 14).Value = Sheet4.Cells(2, 14).Value * (A - DF)
            Sheet5.Cells(h + 6, 2).Value = Sheet3.Cells(h + 2, 12) + Sheet3.Cells(2 + A, 14)
        Next A

'Sheet5.Cells(h + 6, 2)' and 'Sheet3.Cells(h + 2, 12)' are static range references inside the inner loop, which means that if more than one iteration occurs, Sheet5.Cells(h + 6, 2) will only equal the last calculated value of Sheet3.Cells(h + 2, 12) + Sheet3.Cells(2 + A, 14). If this is the problem you were referring to, I am not sure what to recommend because I do not understand your overall objective. But if you want the cell reference to change on Sheet5, then you would have to insert a variable which would increment with each iteration of the inner loop.

 

[BenjaminLWX]

I could further elaborate on my code.
h represents the height.
DF(From) and DT(To) is the object inside a "tank" known as deadwood.
The values of sheet3.cells(h+2,12) are a range of values for each height produced from a previous userform. The main purpose of adding/subtracting A is because it occupies the a certain portion of the tank which would reduce the volume of the tank.
Therefore if the liquid height in the tank is from DF to DT, addition/subtraction must be done. The two column of values I gave above is an example of the volume of the tank with the object at the same range of liquid height.(The two column of values shows the volume per increment height) The amount of volume occupied by the object increases as the liquid level in the tank increases. Therefore I need to find a way to subtract/add the volume of the deadwood into the volume of tank.

Thanks for your advice! But I am not very sure what you meant. Could you elaborate it further for me?

 

[JLGWhiz]

What I am trying to point out, could be by design, but I don't think so.
This statement:

For A = DF To DT

Is assumed to increment the value of A for each iteration of the loop. What is happening is that only the last value produced in this loop for

Sheet3.Cells(h + 2, 12) + Sheet3.Cells(2 + A, 14)

will be posted to this cell

Sheet5.Cells(h + 6, 2) 'This cell reference does not change during the iterations of the inner loop. It only changes with the iteration of the outer loop.

Effectively, if 'Sheets5.Cells(h + 6, 2) equated to Cell A2 on Sheet 5 at the beginning of the loop, it would still be cell A2 at the end of the loop and its value would be the last calculated value. Because the value of 'h' does not change during the execution of the inner loop. So it is the same cell being posted over and over with different values until the final value of A is reached. Then that cell value will change as the outer loop iteration changes.
The other part of the inner loop is OK for sheet 3 range, because the value of A increments with each iteration of the inner loop.

 

[BenjaminLWX]

Thank you for your detailed explaination!
Does that mean that I must find a way to add in A into "Sheet5.Cells(h+6,2)" so that the cells would change with the iteration of inner loop?
Is there any guide I could follow for double looping of values?

 

[JLGWhiz]

Thank you for your detailed explaination!
Does that mean that I must find a way to add in A into "Sheet5.Cells(h+6,2)" so that the cells would change with the iteration of inner loop?
Is there any guide I could follow for double looping of values?

One technique for advancing the incremental value of a cell to advance rows:

Dim cnt As Long 'put this line with other Dim statements at top
cnt = 0 'Initialize the variable
For A = DF To DT
    Sheet3.Cells(2 + A, 14).Value = Sheet4.Cells(2, 14).Value * (A - DF)
    'add the variable to either the row or column value as applicable
    Sheet5.Cells(h + 6 + cnt, 2).Value = Sheet3.Cells(h + 2, 12) + Sheet3.Cells(2 + A, 14)
    cnt = cnt + 1 'Advance variable value
Next A
cnt = 0