Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help in macro
Hi
I have the below procedures. Saskour40per = (Saskour20 / Sask20) Saskourper = (Saskour20 + Saskour40 + sask53) / SaskTotal The calculations are right when the results are displayed in a MsgBox, but I want the results diplayed in a cell. I get displayed the words of the variable ("saskour40per") instead of the numerical result. I know I have to use the word "application" in the procedure to display the numerical result but I can't put together the procedure. Could anyone help me please. Thanks, Orquidea |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help in macro
hi
Sub testt() Dim saskour40per As Long Dim saskour20 As Long Dim sask20 As Long saskour20 = InputBox("enter something") sask20 = InputBox("enter something") saskour40per = (saskour20 / sask20) Range("A1").Value = saskour40per End Sub loose the double quotes and the brackets around saskour40per not ("saskour40per") just saskour40per the double quotes means put the word saskour40per in the cell literally. with no double quotes, the value of saskour40per will go in the cell. Regards FSt1 "orquidea" wrote: Hi I have the below procedures. Saskour40per = (Saskour20 / Sask20) Saskourper = (Saskour20 + Saskour40 + sask53) / SaskTotal The calculations are right when the results are displayed in a MsgBox, but I want the results diplayed in a cell. I get displayed the words of the variable ("saskour40per") instead of the numerical result. I know I have to use the word "application" in the procedure to display the numerical result but I can't put together the procedure. Could anyone help me please. Thanks, Orquidea |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help in macro
Hi
I tried your example and it works but when I put those subprocedures on my macro I get the words of the variable displayed instead of the numerical result. Maybe because the Saskour20, Saskour40 and SaskTotal come from other calculation. I don't enter them. Below is my macro, and I have undeline the subprocedures that are involved in this macro that I can't make it work. I hate to bother too much but I have spent a lot of time trying to figure out what the problem is and I can't Dim Saskour20per As Long, saskour40per As Long, saskourper As Long Sub other() Range("B2").Select FirstRow = ActiveCell.Row EndRow = Cells(FirstRow, "B").End(xlDown).Row Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B")) Set CriteriaRange = Range(Cells(FirstRow, "C"), Cells(EndRow, "C")) sask20 = Application.SumIf(CriteriaRange, "=20", SumRange) Sask40 = Application.SumIf(CriteriaRange, "=40", SumRange) sask53 = InputBox("How many UUUU were sent out to Saskatoon?", "UUUU's to Saskatoon", "0") SaskTotal = Application.Sum(sask20 + Sask40 + sask53) ----------------------------------------------------------------------------- Range("B2").Select FirstRow = ActiveCell.Row EndRow = Cells(FirstRow, "B").End(xlDown).Row Set SumRange = Range(Cells(FirstRow, "B"), Cells(EndRow, "B")) Set CriteriaRange = Range(Cells(FirstRow, "H"), Cells(EndRow, "H")) Saskcn20 = Application.SumIf(CriteriaRange, "=20CNF001", SumRange) Saskcn40 = Application.SumIf(CriteriaRange, "=40CNF001", SumRange) Saskcp20 = Application.SumIf(CriteriaRange, "=20CPF001", SumRange) Saskcp40 = Application.SumIf(CriteriaRange, "=40CPF001", SumRange) saskour20 = (sask20 - Saskcn20 - Saskcp20) ------------------------------------------------------------------- Saskour40 = (Sask40 - Saskcn40 - Saskcp40) ------------------------------------------------------------------ If sask20 < 0 Then Saskour20per = Application.Sum(saskour20 / sask20) ------------------------------------------------------------------------- End If saskour40per = (Saskour40 / Sask40) --------------------------------------------------------- saskourper = (saskour20 + Saskour40 + sask53) / SaskTotal -------------------------------------------------------------------------------- Range("A1").Select Selection.Value = "Saskatoon " & sask20 & " - 20's & " & Sask40 & " - 40's & " & sask53 & " - NFFU's" Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select If Saskcn20 = 0 And Saskcn40 = 0 And Saskcp20 = 0 And Saskcp40 = 0 Then Selection.Value = "100% of the equipment supplied by us" Else Selection.Value = "saskour20per " & " of the 20' equipment supplied by us" ActiveCell.Offset(1, 0).Select Selection.Value = "saskour40per " & " of the 40' equipment supplied by us" ActiveCell.Offset(1, 0).Select Selection.Value = "We supplied " & " saskourper " & " of the equipment sent into Saskatoon" End If End Sub "FSt1" wrote: hi Sub testt() Dim saskour40per As Long Dim saskour20 As Long Dim sask20 As Long saskour20 = InputBox("enter something") sask20 = InputBox("enter something") saskour40per = (saskour20 / sask20) Range("A1").Value = saskour40per End Sub loose the double quotes and the brackets around saskour40per not ("saskour40per") just saskour40per the double quotes means put the word saskour40per in the cell literally. with no double quotes, the value of saskour40per will go in the cell. Regards FSt1 "orquidea" wrote: Hi I have the below procedures. Saskour40per = (Saskour20 / Sask20) Saskourper = (Saskour20 + Saskour40 + sask53) / SaskTotal The calculations are right when the results are displayed in a MsgBox, but I want the results diplayed in a cell. I get displayed the words of the variable ("saskour40per") instead of the numerical result. I know I have to use the word "application" in the procedure to display the numerical result but I can't put together the procedure. Could anyone help me please. Thanks, Orquidea |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help in macro
Hi
Disregard my previous message. I found the error in my macro. I was using "saskour20" instead of saskour20. I read your email again and found out my error. It is working now. Thanks for your help. Orquidea "FSt1" wrote: hi Sub testt() Dim saskour40per As Long Dim saskour20 As Long Dim sask20 As Long saskour20 = InputBox("enter something") sask20 = InputBox("enter something") saskour40per = (saskour20 / sask20) Range("A1").Value = saskour40per End Sub loose the double quotes and the brackets around saskour40per not ("saskour40per") just saskour40per the double quotes means put the word saskour40per in the cell literally. with no double quotes, the value of saskour40per will go in the cell. Regards FSt1 "orquidea" wrote: Hi I have the below procedures. Saskour40per = (Saskour20 / Sask20) Saskourper = (Saskour20 + Saskour40 + sask53) / SaskTotal The calculations are right when the results are displayed in a MsgBox, but I want the results diplayed in a cell. I get displayed the words of the variable ("saskour40per") instead of the numerical result. I know I have to use the word "application" in the procedure to display the numerical result but I can't put together the procedure. Could anyone help me please. Thanks, Orquidea |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |