VBA Macro FormulaR1C1 problem

natphonics

New Member
Joined
Aug 6, 2007
Messages
11
HI the following is a section of macro I've written to take data from a workbook and put it into another sheet and then run formula's against the data. It worked fine when I only wanted the data to come from one workbook. Now i Want it to loop through several workbooks in the fille and do the samething.
This worked fine wen it was always just one workbook

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",'[Budget model OFD 25-06-07.xls]Salary
Budget'!R1C2)"

Now I need it to go through several different workbooks and pull data from the sheet called Salary budget in each. The Budget model OFD 25-06-07.xls now needs to change depending on wich file the macro is being run in.

This what i tried:

Dim model As String
Dim FTEdb As String

'Application.ScreenUpdating = False

model = ActiveWorkbook.Name

Workbooks.Add

ActiveWorkbook.SaveAs ("G:\DVC\DVC Finance Budget\Budget 2007-08\Budget Models\FTE Database\FTE Data " & model)

FTEdb = ActiveWorkbook.Name

Workbooks.Open ("G:\DVC\DVC Finance Budget\Budget 2007-08\Budget Models\FTE Database\FTE Database Temp.xls")

Cells.Select
Range("A1").Activate
Selection.Copy
Windows(FTEdb).Activate
ActiveSheet.Paste

Windows(model).Activate

'Application.ScreenUpdating = True

UserForm1.Show

'Application.ScreenUpdating = False

Sheets("Salary Budget").Activate
Range("A7:A36").Select
Selection.Copy
Windows(FTEdb).Activate
Range("b4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""", model &'Salary Budget'!R1C2)"
Range("C4").Select
Selection.Copy
Range("C5:C40").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Any Help would be greatly apreciated.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi
try
A1 = cells("filename")
B1 = find("[",A1)
C1 = find("]",a1)
D1 = mid(A1,B1+1,C1-B1-1)
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",'[" & cells(1,4) & "]Salary
Budget'!R1C2)"
Ravi
 
Upvote 0
Hi
They are cell references. you insert the formula in those cells or choose a set of cells which you are comfortable with
Ravi
 
Upvote 0
I've changed the way the Macro works, I made so that it creates a New sheet within the file and does creates all the data i need in the same workbook and then moves out to a new workbook when its finished. It seems to work better this way. Thanks for your help Ravi. I'll let you know if i encounter any problems doing it this way.

Thnaks again

Nathan
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top