Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA conundrum
Ok, so here's the problem...
I prompt the user to enter a size (say 9,000), then I subject that number to a condition. If size =0<10,000 Then This is where I get confused. First, I would like to determine whether 9,000 is in the range that I've specified. Ok, so it is. Second, I want to take the user's number (9,000) and determine how many times a default number for that range (3,500) goes into 9,000. 3,500 goes into 9,000 twice so numbers "1" and "2" would comprise column 1. Third, I want to display the default number, 3,500, in a second column. Fourth, I would like to be able to work out the remainder (9,000-7,000) and put "3" and "2000" in the third row of the first and second columns. Finally, in a third column, I would like to take the number in each of the rows of the second column (3,500, 3,500, and 2,000) and divide it by 1000, square the result, and multiply that by 15. This is another example of how it would look if I could make it work: The user enters 25,800. If size =20,00<=30,000 Then... The default number for this range is 6,000. 1 6000 37 2 6000 37 3 6000 37 4 6000 37 5 1800 20 Is it possible to do all these calculations easily? vbab |
#2
|
|||
|
|||
VBA conundrum
Is it possible to do all these calculations easily? Yes. |
#3
|
|||
|
|||
VBA conundrum
Outdoing yourself for helpfulness today, aren't you.
OP, Here is a little macro that does what I think you ask. It doesn't get column C as you predict, so it probably means I don't understand what you need there. Dim myVal As Long Const nDefault As Long = 6000 Const nMin As Long = 20000 Const nMax As Long = 30000 Application.ScreenUpdating = False myVal = InputBox("Input a number between 0 and 10,000") If myVal nMin And myVal < nMax Then If myVal nDefault Then For i = 1 To myVal \ nDefault Cells(i, "A").Value = i Cells(i, "B").Value = nDefault Cells(i, "C").Value = (nDefault / 1000) * (nDefault / 1000) * 15 Next i End If Cells(i, "A").Value = i Cells(i, "B").Value = myVal Mod nDefault Cells(i, "C").Value = (Cells(i, "B").Value / 1000) * _ (Cells(i, "B").Value / 1000) * 15 End If Application.ScreenUpdating = True -- HTH RP (remove nothere from the email address if mailing direct) "Jezebel" wrote in message ... Is it possible to do all these calculations easily? Yes. |
#4
|
|||
|
|||
VBA conundrum
Thank-you for the info; it was quite useful.
By the way, what does "Dim myVal As Long" do in terms of this macro? -- vbab "Bob Phillips" wrote: Outdoing yourself for helpfulness today, aren't you. OP, Here is a little macro that does what I think you ask. It doesn't get column C as you predict, so it probably means I don't understand what you need there. Dim myVal As Long Const nDefault As Long = 6000 Const nMin As Long = 20000 Const nMax As Long = 30000 Application.ScreenUpdating = False myVal = InputBox("Input a number between 0 and 10,000") If myVal nMin And myVal < nMax Then If myVal nDefault Then For i = 1 To myVal \ nDefault Cells(i, "A").Value = i Cells(i, "B").Value = nDefault Cells(i, "C").Value = (nDefault / 1000) * (nDefault / 1000) * 15 Next i End If Cells(i, "A").Value = i Cells(i, "B").Value = myVal Mod nDefault Cells(i, "C").Value = (Cells(i, "B").Value / 1000) * _ (Cells(i, "B").Value / 1000) * 15 End If Application.ScreenUpdating = True -- HTH RP (remove nothere from the email address if mailing direct) "Jezebel" wrote in message ... Is it possible to do all these calculations easily? Yes. |
#5
|
|||
|
|||
VBA conundrum
It declares a variable, called myVal, of type Long, which means that it can
hold an integer value between -2,147,483,648 and 2,147,483,647. It is this variabel that I store the user input number. -- HTH RP (remove nothere from the email address if mailing direct) "csi" wrote in message ... Thank-you for the info; it was quite useful. By the way, what does "Dim myVal As Long" do in terms of this macro? -- vbab "Bob Phillips" wrote: Outdoing yourself for helpfulness today, aren't you. OP, Here is a little macro that does what I think you ask. It doesn't get column C as you predict, so it probably means I don't understand what you need there. Dim myVal As Long Const nDefault As Long = 6000 Const nMin As Long = 20000 Const nMax As Long = 30000 Application.ScreenUpdating = False myVal = InputBox("Input a number between 0 and 10,000") If myVal nMin And myVal < nMax Then If myVal nDefault Then For i = 1 To myVal \ nDefault Cells(i, "A").Value = i Cells(i, "B").Value = nDefault Cells(i, "C").Value = (nDefault / 1000) * (nDefault / 1000) * 15 Next i End If Cells(i, "A").Value = i Cells(i, "B").Value = myVal Mod nDefault Cells(i, "C").Value = (Cells(i, "B").Value / 1000) * _ (Cells(i, "B").Value / 1000) * 15 End If Application.ScreenUpdating = True -- HTH RP (remove nothere from the email address if mailing direct) "Jezebel" wrote in message ... Is it possible to do all these calculations easily? Yes. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Conundrum | Excel Discussion (Misc queries) | |||
ISERROR Conundrum | Excel Worksheet Functions | |||
ISERROR Conundrum | Excel Worksheet Functions | |||
Pivot Table conundrum | Excel Discussion (Misc queries) | |||
Delete Row syntax conundrum | Excel Discussion (Misc queries) |