Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace a range of cell values
Hello,
I have a bunch of excel files with several worksheets. On the worksheet named TA in all files i have two cells R11 and R13, these hold the row numbers of a begin and end point entered by the user respectively. I am having trouble writing a simple macro that will replace a range of values in column K with the value of cell C2 after the user tabs out of entering the value of R13. C2 contains the formula =AVERAGE(B2:B150) The range of K is determined by the values in R11 and R13. Example: C2 has the value .0013 R11 has the value 15 R13 has the value 25 After the user enters 25 in R13, cells K15 through K25 now have the value . 0013 Any assistance would be greatly approached. Thank you. -- Message posted via http://www.officekb.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace a range of cell values
Not sure what you want to do with ALL the files, but the macro below will do
what you want in one file. This is an event macro and must be placed in the sheet module of sheet TA. You can access that module by right-clicking on the sheet tab and selecting View Code. "X" out of the module to return to your sheet. Additional coding will be required if you want the code to loop through all the files that you have. Note that I assumed that the values in R11 & R13 are always integers. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim TheRng As Range If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Not Intersect(Target, Range("R13")) Is Nothing Then Set TheRng = Range(Cells(Range("R11").Value, 11), Cells(Range("R13").Value, 11)) Range("C2").Copy TheRng.PasteSpecial xlPasteValues End If End Sub "SunshineStateBroker via OfficeKB.com" <u49529@uwe wrote in message news:9cd9f51c17bb5@uwe... Hello, I have a bunch of excel files with several worksheets. On the worksheet named TA in all files i have two cells R11 and R13, these hold the row numbers of a begin and end point entered by the user respectively. I am having trouble writing a simple macro that will replace a range of values in column K with the value of cell C2 after the user tabs out of entering the value of R13. C2 contains the formula =AVERAGE(B2:B150) The range of K is determined by the values in R11 and R13. Example: C2 has the value .0013 R11 has the value 15 R13 has the value 25 After the user enters 25 in R13, cells K15 through K25 now have the value . 0013 Any assistance would be greatly approached. Thank you. -- Message posted via http://www.officekb.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace a range of cell values
The macro works great! Thank you very much.
When i was refering to several files, i didnt know if there was a way i could run this specific macro in other similar files without having to paste the code in each of the the Sheet TA's code areas when i am working on them. Is there a way i can make this a global macro? Otto Moehrbach wrote: Not sure what you want to do with ALL the files, but the macro below will do what you want in one file. This is an event macro and must be placed in the sheet module of sheet TA. You can access that module by right-clicking on the sheet tab and selecting View Code. "X" out of the module to return to your sheet. Additional coding will be required if you want the code to loop through all the files that you have. Note that I assumed that the values in R11 & R13 are always integers. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim TheRng As Range If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Not Intersect(Target, Range("R13")) Is Nothing Then Set TheRng = Range(Cells(Range("R11").Value, 11), Cells(Range("R13").Value, 11)) Range("C2").Copy TheRng.PasteSpecial xlPasteValues End If End Sub Hello, I have a bunch of excel files with several worksheets. [quoted text clipped - 20 lines] Any assistance would be greatly approached. Thank you. -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace a range of cell values
Global as in:
If a workbook has a sheet named "TA" than this macro would run after a value has been entered in R13. (Sorry, i know this is a lot to ask) SunshineStateBroker wrote: The macro works great! Thank you very much. When i was refering to several files, i didnt know if there was a way i could run this specific macro in other similar files without having to paste the code in each of the the Sheet TA's code areas when i am working on them. Is there a way i can make this a global macro? Not sure what you want to do with ALL the files, but the macro below will do what you want in one file. This is an event macro and must be placed in the [quoted text clipped - 19 lines] Any assistance would be greatly approached. Thank you. -- Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace a range of cell values
No, I don't think that can be done. You can have a "global" macro in your
Personal.xls workbook that can be accessed by any other workbook, but not an event macro like I gave you. You can put the meat of the macro I gave you in the Personal.xls workbook, but you would still have to have an event macro in each "TA" workbook to pick up on a change to R13 and call that macro, so that wouldn't help you. Sorry. Otto "SunshineStateBroker via OfficeKB.com" <u49529@uwe wrote in message news:9cda9d67c685d@uwe... Global as in: If a workbook has a sheet named "TA" than this macro would run after a value has been entered in R13. (Sorry, i know this is a lot to ask) SunshineStateBroker wrote: The macro works great! Thank you very much. When i was refering to several files, i didnt know if there was a way i could run this specific macro in other similar files without having to paste the code in each of the the Sheet TA's code areas when i am working on them. Is there a way i can make this a global macro? Not sure what you want to do with ALL the files, but the macro below will do what you want in one file. This is an event macro and must be placed in the [quoted text clipped - 19 lines] Any assistance would be greatly approached. Thank you. -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to replace a range of cell values
Gotcha.
Thank you again for everything. Otto Moehrbach wrote: No, I don't think that can be done. You can have a "global" macro in your Personal.xls workbook that can be accessed by any other workbook, but not an event macro like I gave you. You can put the meat of the macro I gave you in the Personal.xls workbook, but you would still have to have an event macro in each "TA" workbook to pick up on a change to R13 and call that macro, so that wouldn't help you. Sorry. Otto Global as in: [quoted text clipped - 20 lines] Any assistance would be greatly approached. Thank you. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace cell values Macro | Excel Programming | |||
Macro that will add multiple emails based on a range of cell values | Excel Programming | |||
Find and Replace Values in Range | Excel Programming | |||
Replace Values in range selection | Excel Programming | |||
Macro to insert formula result into range with zero values in cell | Excel Programming |