# Help with this code, please!!

#### kgvba

##### New Member
-- removed inline image ---

Using Excel’s VBA, write a program to perform volume calculations on a set of shapes in accordance with the instructions and similar to the spreadsheet layout below:
• Three types of shapes are to be processed: 1-cylinder, 2-cone, and 3-sphere segment.
• Volume equations for the shapes are as follows:

Volcyl = πr2h
Volcon = πr2h/3
Volsph = πr2h/2+ πh3/6
• Data defining the shapes will be read directly from the spreadsheet. The program will read one row of data at a time. Each row of data will contain the shape type (1 = cylinder, 2 = cone, 3 = sphere segment), radius and height of the shape. The program will continue to read and process shape data until it reaches a blank row.
• The main program will use a single subroutine or function to calculate the volume of each shape and return this data to the main program. The main program will immediately output the results in a cell adjacent to each row of data.
• The program will also calculate summary data in a 2D array (4 rows x 2 columns, as shown in the spreadsheet above), including: o Total count of each shape
o Total count of all shapes
o Total volume of each shape
o Total volume of all shapes

• The summary statistics should be written back to the spreadsheet
• The program should also check for illegal data including o Negative numbers
o Illegal or undefined volume types

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
So far I got this code, but I'm completely lost. any idea??
Sub vol()
ReDim Shape(1 To 7), rad(1 To 7), ht(1 To 7), vol(1 To 7)
For i = 1 To 7
Shape(i) = Cells(i + 1, 1)
rad(i) = Cells(i + 1, 2)
ht(i) = Cells(i + 1, 3)
Next i
End Sub

Function volume()

If Shape(i) = 1 Then
vol = Worksheet.Function.Pi * rad(i) ^ 2 * ht(i)
ElseIf Shape(i) = 2 Then
vol = Worksheet.Function.Pi * rad(i) ^ 2 * ht(i) / 3
ElseIf Shape(i) = 3 Then
vol = Worksheet.Function.Pi * ht(i) ^ 2 / 2 + Worksheet.Function.Pi * ht(i) ^ 3 / 6
Else
End If
'output vol
For i = 1 To 7
Cells(i + 1, 4) = volume

End Function

Replies
16
Views
753
Replies
3
Views
4K
Replies
0
Views
345
Replies
3
Views
567
Replies
2
Views
333

1,212,046
Messages
6,105,585
Members
447,972
Latest member
carrieann

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back