Formula in Vba

[sathya]

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 :)

[Rudi]

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

[HansV]

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.

[Rudi]

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

[Doc.AElstein]

Hi,
Actually I was just lurking and picking up those references from Rudi, but while I am here…

Hi sathya,

..am dragging it down from top cell to bottom cell but does it possible to do it in vba code? ...

I think in your last Thread we said this is possible.. with vba code and manually quick…

_...................

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

_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:

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 

_7) Here Rudi’s code again, for comparison, as you may want it

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

_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

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

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

[sathya]

Great Work,

Thanks a lot again guys :)