VBA Working between Worksheets

[pahy96]

I am just learning the ropes of VBA and have the following problem I am trying to work through. I am able to handle the first two requests, but am struggling to understand the best way to incorporate loops and working between values in one worksheet in order to populate cells in another worksheet:

Write a subroutine that creates a new tab named “Misc_Calc” and populate the new tab with calculations using the numbers located on the worksheet called “Numbers”.

Sub New_WS()

Dim i As Integer
Dim ws_Misc As Worksheet
Dim ws_Num As Worksheet
Set ws_Misc = Sheets.Add(After:=Sheets(Sheets.Count))
Set ws_Num = Sheets("Numbers")


ws_Misc.Name = "Misc_Calc"

On “Misc_Calc”, populate the first row for Columns A, B, and C with “Calculation X”, “Calculation Y”, and “Calculation Z” respectively.

Range("A1") = "Calculation X"Range("B1") = "Calculation Y"
Range("C1") = "Calculation Z"

In the second row for Columns A, B, and C, populate the cells with the formula for the calculations below.

On “Misc_Calc”, write a function that calculates the answer to A²+B²-C (where A, B, and C come from the “Numbers” Tab) and call that function to populate the cells in Column A. Similarly, on “Misc_Calc”, write a function that calculate A*B/C rounded to the nearest integer and call that function to populate the cells in Column B. Finally, on “Misc_Calc”, write a function that calculates the remainder when the maximum of A, B, and C is divided by the minimum of A, B, and C and call that function to populate the cells in Column C.

This is where things breakdown for me. The way I would type out the equations in Excel are commented out after the For loop, but I assume this would not be how I would go about things in VBA. In the equation I have typed out within the For loop, I would like to reference the right side of the equation to the worksheet titled "Numbers." Unless of course, my logic is completely off and I should approach things in an entirely different manner.

For i = 2 To 31    Cells(i, 1).Value = Cells(i, 1).Value ^ 2 + Cells(i, 2).Value ^ 2 - Cells(i, 3).Value
Next i
    
'Range("A2") = "=Numbers!A2^2+Numbers!B2^2-Numbers!C2"
'Range("B2") = "=ROUND(Numbers!A2*Numbers!B2/Numbers!C2),0)"
'Range("C3") = "=MOD(MAX(Numbers!A2,Numbers!B2,Numbers!C2)/MIN(Numbers!A2,Numbers!B2,Numbers!C2))"


End Sub

 

[pahy96]

I have made a little bit of progress but don't believe it is the most efficient way. Also, I'm not sure how to use round (to nearest whole number) for the second formula in the For Loop:

Sub New_WS()

Dim i As Integer
Dim ws_Misc As Worksheet
Dim ws_Num As Worksheet
Set ws_Misc = Sheets.Add(After:=Sheets(Sheets.Count))
Set ws_Num = Sheets("Numbers")


ws_Misc.Name = "Misc_Calc"


Range("A1") = "Calculation X"
Range("B1") = "Calculation Y"
Range("C1") = "Calculation Z"


For i = 2 To 31


    Worksheets("Misc_Calc").Cells(i, 1).Formula = Worksheets("Numbers").Cells(i, 1).Value ^ 2 + Worksheets("Numbers").Cells(i, 2).Value ^ 2 - Worksheets("Numbers").Cells(i, 3).Value
    Worksheets("Misc_Calc").Cells(i, 2).Formula = Worksheets("Numbers").Cells(i, 1).Value * Worksheets("Numbers").Cells(i, 2).Value / Worksheets("Numbers").Cells(i, 3).Value


Next i


End Sub

 

[RCBricker]

I am just learning the ropes of VBA and have the following problem I am trying to work through. I am able to handle the first two requests, but am struggling to understand the best way to incorporate loops and working between values in one worksheet in order to populate cells in another worksheet:

Write a subroutine that creates a new tab named “Misc_Calc” and populate the new tab with calculations using the numbers located on the worksheet called “Numbers”.

Sub New_WS()

Dim i As Integer
Dim ws_Misc As Worksheet
Dim ws_Num As Worksheet
Set ws_Misc = Sheets.Add(After:=Sheets(Sheets.Count))
Set ws_Num = Sheets("Numbers")


ws_Misc.Name = "Misc_Calc"

On “Misc_Calc”, populate the first row for Columns A, B, and C with “Calculation X”, “Calculation Y”, and “Calculation Z” respectively.

Range("A1") = "Calculation X"Range("B1") = "Calculation Y"
Range("C1") = "Calculation Z"

In the second row for Columns A, B, and C, populate the cells with the formula for the calculations below.

On “Misc_Calc”, write a function that calculates the answer to A²+B²-C (where A, B, and C come from the “Numbers” Tab) and call that function to populate the cells in Column A. Similarly, on “Misc_Calc”, write a function that calculate A*B/C rounded to the nearest integer and call that function to populate the cells in Column B. Finally, on “Misc_Calc”, write a function that calculates the remainder when the maximum of A, B, and C is divided by the minimum of A, B, and C and call that function to populate the cells in Column C.

This is where things breakdown for me. The way I would type out the equations in Excel are commented out after the For loop, but I assume this would not be how I would go about things in VBA. In the equation I have typed out within the For loop, I would like to reference the right side of the equation to the worksheet titled "Numbers." Unless of course, my logic is completely off and I should approach things in an entirely different manner.

For i = 2 To 31    Cells(i, 1).Value = Cells(i, 1).Value ^ 2 + Cells(i, 2).Value ^ 2 - Cells(i, 3).Value
Next i
    
'Range("A2") = "=Numbers!A2^2+Numbers!B2^2-Numbers!C2"
'Range("B2") = "=ROUND(Numbers!A2*Numbers!B2/Numbers!C2),0)"
'Range("C3") = "=MOD(MAX(Numbers!A2,Numbers!B2,Numbers!C2)/MIN(Numbers!A2,Numbers!B2,Numbers!C2))"


End Sub

If I was doing this (and I do this a lot) I would record a macro of insert the formulas into the first set of cells (A2,B2 & C3). Then I take the code for how the formulas are written and replace parts with variables where needed.

rich

 

[pahy96]

If I was doing this (and I do this a lot) I would record a macro of insert the formulas into the first set of cells (A2,B2 & C3). Then I take the code for how the formulas are written and replace parts with variables where needed.

rich

That's basically what I did to get the three formulas I commented out. I have gotten all three formulas to work, but wanted to know if there were any suggestions on performing the loop more efficiently or if what I currently have is sufficient:

For i = 2 To 31    Worksheets("Misc_Calc").Cells(i, 1).Formula = Worksheets("Numbers").Cells(i, 1).Value ^ 2 + Worksheets("Numbers").Cells(i, 2).Value ^ 2 - Worksheets("Numbers").Cells(i, 3).Value
    Worksheets("Misc_Calc").Cells(i, 2).Formula = Round((Worksheets("Numbers").Cells(i, 1).Value * Worksheets("Numbers").Cells(i, 2).Value / Worksheets("Numbers").Cells(i, 3).Value), 0)
    Worksheets("Misc_Calc").Cells(i, 3).Formula = Application.Max(Worksheets("Numbers").Cells(i, 1).Value, Worksheets("Numbers").Cells(i, 2).Value, Worksheets("Numbers").Cells(i, 3).Value) Mod Application.Min(Worksheets("Numbers").Cells(i, 1).Value, Worksheets("Numbers").Cells(i, 2).Value, Worksheets("Numbers").Cells(i, 3).Value)
Next i

 

[RCBricker]

ahh got ya.

No the loop looks good.

thanks,