Solved: VBA: Values on Sheet

[sammclean23]

Hi All,

To introduce myself, my name is Sam and I am from the UK. Currently progressing with a foundation degree whilst working for British Telecom.

Was wondering if anybody can help.

The code as follows does the obvious which is set some variables, write some values to the spreadsheet and bold the headings.

I need to:

- Make sure each time the code is run that the inputted values are written to the next row down to the previous so that it doesnt overwrite the last line. (EDIT: just looked at the code and it is obvious that each time it will always write to the same cell. Can somebody suggest another way to do this so that the above is possible)

Also, im pretty sure the code can be cut down. I would greatly appreciate any help on this.

The code is as follows:

[vba]
Option Explicit
Sub Cylinder()
Const PI = 3.14
Dim UserRadius As Double
Dim UserHeight As Double
Dim Volume As Double
UserRadius = InputBox("Enter Radius", "Cylinder Radius")
UserHeight = InputBox("Enter Height", "Cylinder Height")
Volume = PI * (UserRadius * UserRadius) * UserHeight
MsgBox "The Volume of the Cylinder = " & Format(Volume, "0.0")

Worksheets("Sheet1").Select
Range("A1").Value = "Radius"
Range("B1").Value = "Height"
Range("C1").Value = "Volume(Cubed)"
Range("A1:C1").Select
Selection.Font.Bold = True

Range("A2").Value = UserRadius
Range("B2").Value = UserHeight
Range("C2").Value = Volume
ActiveCell.Offset(1, 0).Select


End Sub
[/vba]

Thankyou in advance,
Sam

[sammclean23]

Okay iv tried this but still doesnt get the required effect:
[VBA]UserRadius = InputBox("Enter Radius", "Cylinder Radius")
UserHeight = InputBox("Enter Height", "Cylinder Height")
Volume = PI * (UserRadius * UserRadius) * UserHeight
MsgBox "The Volume of the Cylinder = " & Format(Volume, "0.0")

Worksheets("Sheet1").Select
Range("A1").Value = "Radius"
Range("B1").Value = "Height"
Range("C1").Value = "Volume(Cubed)"
Range("A1:C1").Select
Selection.Font.Bold = True

ActiveCell.Value = Range("A2")
ActiveCell.Offset(0, 1).Value = UserRadius
ActiveCell.Offset(0, 2).Value = UserHeight
ActiveCell.Offset(0, 3).Value = Volume
ActiveCell.Offset(1, 0).Activate[/VBA]

[Bob Phillips]

Hello Sam in the UK.

You already have headings, so

[vba]

Sub Cylinder()
Const PI = 3.14
Dim UserRadius As Double
Dim UserHeight As Double
Dim Volume As Double
Dim Lastrow As Long

UserRadius = InputBox("Enter Radius", "Cylinder Radius")
UserHeight = InputBox("Enter Height", "Cylinder Height")
Volume = PI * (UserRadius * UserRadius) * UserHeight
MsgBox "The Volume of the Cylinder = " & Format(Volume, "0.0")

With Worksheets("Sheet1")

Lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row

.Cells(Lastrow + 1, "A").Value = UserRadius
.Cells(Lastrow + 1, "B").Value = UserHeight
.Cells(Lastrow + 1, "C").Value = Volume
End With
End Sub[/vba]

[sammclean23]

Brilliant thanks a lot!

Works exactly as required.

One thing though, because I wish to learn how this works, can you quickly just explain how the following bit of code works:

[VBA]Lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row
[/VBA]

Thanks again,
Sam

[Bob Phillips]

What that does is to start from the last row in the spreadsheet in column A (Rows.Count, "A") and does the equivalent of Ctrl-UpArrow, which is the End(xlUp) part. This finds the last occupied cell in that column, and we extract the row number.

[sammclean23]

What that does is to start from the last row in the spreadsheet in column A (Rows.Count, "A") and does the equivalent of Ctrl-UpArrow, which is the End(xlUp) part. This finds the last occupied cell in that column, and we extract the row number.

Okay thank you!

Sorry but can i also ask, if i were to do this using offset and active cell, how could I achieve the same result?

I know the above does work, but just want to see the same result using offsets too.

Thanks again in advance, I appreciate the help as can be frustrating at times.
Sam

[Bob Phillips]

That is a VERY bad idea. Every time you reset the activecell, Excel will repaint the screen, which takes a lot of resources and time.

[sammclean23]

That is a VERY bad idea. Every time you reset the activecell, Excel will repaint the screen, which takes a lot of resources and time.

I understand it is yes

However, just for understanding purposes, would you be able to explain how to achieve the same result using offset and activecell.

I know by all means that this is not the most effective, im just trying to get a grasp for offest

Thanks!

Sam

[Bob Phillips]

Not tested

[vba]

Sub Cylinder()
Const PI = 3.14
Dim UserRadius As Double
Dim UserHeight As Double
Dim Volume As Double
Dim i As Long

UserRadius = InputBox("Enter Radius", "Cylinder Radius")
UserHeight = InputBox("Enter Height", "Cylinder Height")
Volume = PI * (UserRadius * UserRadius) * UserHeight
MsgBox "The Volume of the Cylinder = " & Format(Volume, "0.0")

With Worksheets("Sheet1")

With Activecell.Offset(0, -.Column + 1)

Do While .Offset(i, 0).Value <> ""

.Offset(i, 0).Value = UserRadius
.Offset(i, 1).Value = UserHeight
.Offset(i, 2).Value = Volume

i = i + 1
Loop
End With
End With
End Sub [/vba]

[sammclean23]

Brilliant thank you. Thanks a lot for your help