updating a range of cells by a multiplier

[Guest]

I have a range of cells with dollar values (no formulas) and am wondering if
there is any way to increase the values of all cells in the range by a
certain percentage, say 5.5%. I've got to do this in several hundred
workbooks and so would like to find a way to automate the process.
Unfortunately my range isn't currently named, though that could be remedied
if necessary.

Thanks in advance,
spence

 

[Guest]

For a single sheet:

Enter 1.055 into an empty cell
Copy it
Select the range of data to change
Choose Edit->Paste Special->Values->Multiply from the menu
Click on OK

For hundreds of workbooks: unless the ranges are all consistent or can be
programmatically identified, you are looking at a manual process

 

[T. Valko]

I've got to do this in several hundred workbooks

Ouch!

Try this:

Enter 1.055 in an empty cell
Copy that cell: Edit>Copy
Now, select the range of cells you want to increase
Then do: Edit>Paste Special>Multiply>OK

Clear the 1.055 form the cell.

Biff

 

[Guest]

Thanks a lot. The cell ranges are actually consistent in all my workbooks, so
theoretically I should be able to create a macro that does what you've
described here. (The sheets are PW protected which I never seem to be able to
get past with macros.)

Let me throw a curveball into the scenario. My multiplier unfortunately
isn't going to be 1.055 in all instances but will instead be prorated based
on the number of months left in the budget, which will need to be calulated
based on an existing end date cell (A8). All calculations will be based on
the date of 07/01/07. A budget whose end date is 06/30/08 will need the
aforementioned range multipled by 5.5%; a budget whose end date is 05/31/08
will need the range multipled by 11/12*5.5%, one that ends on 04/30/06 will
have the multiplier of 10/12*5.5%, etc., etc.,etc.

If you have the time and inclination, could you offer a suggestion on this
more complicated scenario? (If it makes you have mercy on my, I work for a
small non-profit who reports to the state and this whole business is the
result of a sudden and unfunded government mandate.)

Thanks again for your time,
spence

 

[Guest]

My oh my. Please say that the password is the same on all the workbooks.
Also, please say that the worksheets are named consistently.

Well, the first thing to do is make a back-up copy of all your workbooks
Next, if they are not already in their own directory, put all the workbooks
- an only the workbooks to be changed - in a separate directory

Actually - just put a couple of test files in the directory and use this
code. If it works, then put all the files in the directory and go to town

Option Explicit

Sub OpenWorkbooks()
Dim strFile As String
Dim strPath As String
Dim wb As Workbook
Dim pWord As String
Const Fctr As Single = 0.055
Dim strAddress As String
Dim mnth As Integer

Dim currFctr As Single

' change this to the path of the directory with all your workbooks
strPath = "c:\files to change\*.xls"

' change this to the password
pWord = "password"

' change this to the address of the range to be adjusted
strAddress = "A10:A100"


strFile = Dir(strPath, vbNormal)
If strFile <> "" Then
Do While strFile <> ""
Set wb = Workbooks.Open(strFile, Password:=pWord)
' you will need to supply the sheet name (replace "target")
Worksheets("target").Activate
mnth = Month(Range("A8")) + 6
currFctr = 1 + Fctr * mnth / 12

' identify an empty cell - has to be empty on all sheets
' replace "A1" with address of empty cell
Range("A1").Value = currFctr
Range("A1").Copy
Range(strAddress).PasteSpecial xlPasteValues,
xlPasteSpecialOperationMultiply

wb.Close (True)

' go to the next file in the directory
strFile = Dir
Loop
End If

End Sub

 

[Guest]

I made an assumption that may be a logic bomb - the assumption that all your
budget end dates would be prior to mid-year. If that is not the case, the
line that reads

mnth = Month(Range("A8")) + 6

is wrong. For anything past June 30 you'll get some factor greater than 5.5%.

I have to head out of here for today, but you can either leave the
assumption alone or add a min check, like so

currFctr = WorksheetFunction.Min(1 + Fctr * mnth / 12, 1 + Fctr)

or roll your own formula.