Easier Method
I have the following macro that is assigned to a button on the formatting bar.
Note: The macro is in a different file than the file where the cells are selected. Sub SumSelectedCells() Application.ScreenUpdating = False Dim wb As Workbook Set wb = ActiveWorkbook wb.Activate myVar = InputBox("Enter Cell Location", "Create Variable") Range(myVar).Value = Application.WorksheetFunction.Sum(Application.Sele ction) Windows("zCalc.xls").Close (0) Application.ScreenUpdating = True End Sub So what this does is if I have cells highlighted in a file and I want to get the sum in a cell I click on the button to run this macro. The only thing is I have to input a cell location for the macro to put the value. How can I do this without having to input a cell address. The ideal would be : I highlight the cells ; click to run the macro ; then click in a cell and paste .... or something like that . Thank you , Steven |
Easier Method
Hi Steven,
Try something like: '========== Public SumSelectedCells() Dim mySum As Double Dim Rng As Range mySum = Application.Sum(Selection) On Error Resume Next Set Rng = Application.InputBox( _ Prompt:="Selectation cell", _ Title:="Sum Cell", _ Type:=8) On Error GoTo 0 If Not Rng Is Nothing Then Rng.Cells(1).Value = mySum End If End Sub '<<========== --- Regards. Norman "Steven" wrote in message ... I have the following macro that is assigned to a button on the formatting bar. Note: The macro is in a different file than the file where the cells are selected. Sub SumSelectedCells() Application.ScreenUpdating = False Dim wb As Workbook Set wb = ActiveWorkbook wb.Activate myVar = InputBox("Enter Cell Location", "Create Variable") Range(myVar).Value = Application.WorksheetFunction.Sum(Application.Sele ction) Windows("zCalc.xls").Close (0) Application.ScreenUpdating = True End Sub So what this does is if I have cells highlighted in a file and I want to get the sum in a cell I click on the button to run this macro. The only thing is I have to input a cell location for the macro to put the value. How can I do this without having to input a cell address. The ideal would be : I highlight the cells ; click to run the macro ; then click in a cell and paste ... or something like that . Thank you , Steven |
Easier Method
Hi Steven,
There is a sdignificant typo in my suggested code. Public SumSelectedCells() Should read: Public Sub SumSelectedCells() --- Regards. Norman |
All times are GMT +1. The time now is 08:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com