Hi all,
Thanks for looking at my post again. I have two workbook.
Workbook 1 - Source workbook
Workbook 2 - Destination workbook.
I have cell formula in "Workbook2" which take data from "Workbook1".
That is i have sheet name called "DATA" in workbook2 in which column "B2" is linked to "ORIGINALDATA" sheet in "Workbook1".
i have formula in "B2" in workbook 2 as ='[Workbook1.xlsb]ORIGINALDATA!$B$2
Basically i wanted to continue the formula till B100000
that is
"B100000" = ='[Workbook1.xlsb]ORIGINALDATA!$B$100000
I am dragging it down from top cell to bottom cell but does it possible to do it in vba code?
I am using below code but i am getting solution for only one cell. Is there is any way to carry on till B100000?
Private Sub Workbook_Open()
Worksheets("DATA").Range("B2").Formula = "=''[Workbook1.xlsb]ORIGINALDATA!$B$2"
End Sub
Thanks again :)
Formula in Vba
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Formula in Vba
Try this:
Code: Select all
Private Sub Workbook_Open()
Dim lCnt As Long
lCnt = Worksheets("DATA").UsedRange.Rows.Count
Worksheets("DATA").Range("B2:B" & lCnt).FormulaR1C1 = "=[Workbook1.xlsb]ORIGINALDATA!RC2"
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula in Vba
Rudi is setting the FormulaR1C1 property. When you set this property, you must use R1C1 notation instead of the usual A1 notation.
Oops, this is a reaction to a reply that disappeared.
Oops, this is a reaction to a reply that disappeared.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Formula in Vba
Excel has two cell reference notations;
1. A1 notation where you can refer to cells with address reference like: A1 and B2
2. R1C1 notation where you can refer to cells based on row and column like: R1C1 (similar to $A$1) and RC2 (similar to $B2)
The R is for ROW
The C is for COLUMN
So RC2 essentially stands for: In the same row as where the formula resides but explicitly in Column 2.
For more details, see:
- R1C1 notation
- R1C1-Style Notation And The FormulaR1C1 Property In Excel VBA: Tutorial And Examples
1. A1 notation where you can refer to cells with address reference like: A1 and B2
2. R1C1 notation where you can refer to cells based on row and column like: R1C1 (similar to $A$1) and RC2 (similar to $B2)
The R is for ROW
The C is for COLUMN
So RC2 essentially stands for: In the same row as where the formula resides but explicitly in Column 2.
For more details, see:
- R1C1 notation
- R1C1-Style Notation And The FormulaR1C1 Property In Excel VBA: Tutorial And Examples
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Formula in Vba
Hi,
Actually I was just lurking and picking up those references from Rudi, but while I am here…
Hi sathya,
_...................
But anyway….
_1) I think you have an error in your formula, an extra '
='[Workbook1.xlsb]ORIGINALDATA!$B$2
but possibly that extra ' does something I am ignorant about. So may be I am wrong here
But that is not too important….
_2) I am not quite sure why you have the code in a Workbook_Open routine - May be you have a reason – I expect you know that every time you open the Workbook that code will run and you will paste the Formulas in.
_3) BTW. The formula type you are using
=[Workbook1.xlsb]ORIGINALDATA!$B$2
is the one that works for an Open Workbook, that is to say Workbook1 must be open. I am answering a similar Thread just now where Workbook1 would be closed. The formula Link would then be
'C:\Users\Elston\Desktop\ExcelFiles\[Workbook1.xlsb]ORIGINALDATA'!$B$2
with this bit
C:\Users\Elston\Desktop\ExcelFiles\
changed to Your Folder path with an extra \ added on
_4) I am also not quite sure why Rudi has done his code to copy down to as far as the row count of your used range. That may give you the wrong range if your first row is empty, and/ or if your last data entry is not in row 100000
_5) I expect you know that if you drag down your original formula you will get exactly the same formula in every Cell. You need to get rid of one of the $ so as to drag down and get the number to change from 2
=[Workbook1.xlsb]ORIGINALDATA!$B2
_6) If you are happy with
=[Workbook1.xlsb]ORIGINALDATA!$B2
=[Workbook1.xlsb]ORIGINALDATA!$B3
=[Workbook1.xlsb]ORIGINALDATA!$B4
etc… then you can put your formula in very quickly as Stuart and I explained to you here
http://www.eileenslounge.com/viewtopic. ... 52#p192980" onclick="window.open(this.href);return false;
and here
http://www.eileenslounge.com/viewtopic. ... 52#p192987" onclick="window.open(this.href);return false;
I just tried, and it takes a split second then to put those formulas in that way manually
_6a) If you do a macro recording whilst carrying out what I and Stuart showed you , then you produce this code
_6b) If you look at that last code and check this post out
http://www.eileenslounge.com/viewtopic. ... 02#p193871" onclick="window.open(this.href);return false;
then you will see how to tidy that code up a bit, .. - here just one example:
_7) Here Rudi’s code again, for comparison, as you may want it
_7a) Note: The formulas that come in from both my code and RudI’s also have the second $ missing, like in row 11 you would get
=[Workbook1.xlsb]ORIGINALDATA!$B11
_8) This last code will include both $ s
so like in row 11 you would get
=[Workbook1.xlsb]ORIGINALDATA!$B$11
Alan
EDIT:I was just in another Thread where some of these methods broke down when the Formulas were much more complicated , as Excel crashed on trying to Pate in one go a large Range. One solution was a code such as 6b) slightly modified to loop and paste one row at a time.
Another alternative was to use the Loop Bound Variable in a similar Loop to build up each formula in its R C form and once again paste that out at each loop
Actually I was just lurking and picking up those references from Rudi, but while I am here…
Hi sathya,
I think in your last Thread we said this is possible.. with vba code and manually quick…sathya wrote:..am dragging it down from top cell to bottom cell but does it possible to do it in vba code? ...
_...................
But anyway….
_1) I think you have an error in your formula, an extra '
='[Workbook1.xlsb]ORIGINALDATA!$B$2
but possibly that extra ' does something I am ignorant about. So may be I am wrong here
But that is not too important….
_2) I am not quite sure why you have the code in a Workbook_Open routine - May be you have a reason – I expect you know that every time you open the Workbook that code will run and you will paste the Formulas in.
_3) BTW. The formula type you are using
=[Workbook1.xlsb]ORIGINALDATA!$B$2
is the one that works for an Open Workbook, that is to say Workbook1 must be open. I am answering a similar Thread just now where Workbook1 would be closed. The formula Link would then be
'C:\Users\Elston\Desktop\ExcelFiles\[Workbook1.xlsb]ORIGINALDATA'!$B$2
with this bit
C:\Users\Elston\Desktop\ExcelFiles\
changed to Your Folder path with an extra \ added on
_4) I am also not quite sure why Rudi has done his code to copy down to as far as the row count of your used range. That may give you the wrong range if your first row is empty, and/ or if your last data entry is not in row 100000
_5) I expect you know that if you drag down your original formula you will get exactly the same formula in every Cell. You need to get rid of one of the $ so as to drag down and get the number to change from 2
=[Workbook1.xlsb]ORIGINALDATA!$B2
_6) If you are happy with
=[Workbook1.xlsb]ORIGINALDATA!$B2
=[Workbook1.xlsb]ORIGINALDATA!$B3
=[Workbook1.xlsb]ORIGINALDATA!$B4
etc… then you can put your formula in very quickly as Stuart and I explained to you here
http://www.eileenslounge.com/viewtopic. ... 52#p192980" onclick="window.open(this.href);return false;
and here
http://www.eileenslounge.com/viewtopic. ... 52#p192987" onclick="window.open(this.href);return false;
I just tried, and it takes a split second then to put those formulas in that way manually
_6a) If you do a macro recording whilst carrying out what I and Stuart showed you , then you produce this code
Code: Select all
Sub Makro1()
Range("B2").Select
Selection.Copy
Application.Goto Reference:="R2C2:R100000C2"
ActiveSheet.Paste
End Sub
http://www.eileenslounge.com/viewtopic. ... 02#p193871" onclick="window.open(this.href);return false;
then you will see how to tidy that code up a bit, .. - here just one example:
Code: Select all
Sub OpenWorkbookFormulaIn() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=25068
'Worksheets info
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("DATA")
'Put single formula in Cell B2
Let Ws.Range("B2").Value = "=" & "[Workbook1.xlsb]ORIGINALDATA!$B2"
'Copy Paste
Ws.Range("B2").Copy
Ws.Range("B3:B100000").PasteSpecial xlPasteFormulas
End Sub
'Rem Ref Copy Paste Ways: http://www.eileenslounge.com/viewtopic.php?f=27&t=25002#p193871
Code: Select all
Sub OpenWorkbookFormulaInRudi2() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=25068
' https://smurfonspreadsheets.wordpress.com/2007/11/12/r1c1-notation/ http://powerspreadsheets.com/r1c1-formular1c1-vba/
'Dim lCnt As Long
' lCnt = Worksheets("DATA").UsedRange.Rows.Count
' Worksheets("DATA").Range("B2:B" & lCnt).FormulaR1C1 = "=[Workbook1.xlsb]ORIGINALDATA!RC2"
Worksheets("DATA").Range("B2:B" & 100000 & "").FormulaR1C1 = "=[Workbook1.xlsb]ORIGINALDATA!RC2"
End Sub
=[Workbook1.xlsb]ORIGINALDATA!$B11
_8) This last code will include both $ s
Code: Select all
Sub OpenWorkbookFormula3() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=25068
'Worksheets info
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("DATA")
Dim rngB As Range
Set rngB = Ws.Range("B2:B100000")
' use evaluate to put formulas in
Dim strEval As String: Let strEval = """=[Workbook1.xlsb]ORIGINALDATA!$B$""&" & "ROW(" & rngB.Address & ")": Debug.Print strEval
Let rngB.Value = Evaluate(strEval)
End Sub
' rem ref http://www.excelforum.com/excel-programming-vba-macros/1105916-macro-to-copy-formulas-down-one-row-at-a-time-to-prevent-excel-crashing.html
=[Workbook1.xlsb]ORIGINALDATA!$B$11
Alan
EDIT:I was just in another Thread where some of these methods broke down when the Formulas were much more complicated , as Excel crashed on trying to Pate in one go a large Range. One solution was a code such as 6b) slightly modified to loop and paste one row at a time.
Another alternative was to use the Loop Bound Variable in a similar Loop to build up each formula in its R C form and once again paste that out at each loop
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 3StarLounger
- Posts: 276
- Joined: 17 Dec 2015, 16:28
Re: Formula in Vba
Great Work,
Thanks a lot again guys :)
Thanks a lot again guys :)