Application.Calculate & VLOOKUP

I have several Vlookup functions in my worksheet. They do not compute until
I F2 into every cell. (Tools Options Calculation is Automatic) Can I use a
macro function that computes the value of the cell and saves me the pain of
having to F2 every cell that has the Vlookup formula

TIA!!

My code is as below:
Sub update()
Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'ActiveWorkbook.Worksheets(1).Calculate
Application.CalculateFull
End Sub




Regards,



--
Manager, International Private Banking, International Banking Group, ICICI
Bank
East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
400051

"BHARATH RAJAMANI" <[email protected]> wrote in
message news:[email protected]...
>
> I have several Vlookup functions in my worksheet. They do not compute
until
> I F2 into every cell. (Tools Options Calculation is Automatic) Can I use
a
> macro function that computes the value of the cell and saves me the pain
of
> having to F2 every cell that has the Vlookup formula
>
> TIA!!
>
> My code is as below:
> Sub update()
> Range("B1").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(0, 2).Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> 'ActiveWorkbook.Worksheets(1).Calculate
> Application.CalculateFull
> End Sub
>
>
>
>
> Regards,
>
>
>
> --
> Manager, International Private Banking, International Banking Group, ICICI
> Bank
> East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai
India
> 400051

Try

Sub update()
Application.CalculateFull
End Sub

/Fredrik

Fredrik,

That doesnt work. I tried using Selection.Calculate
worksheets.cells.calculate Calculate Application.caLculate
Application.calculatefull Call Sub (Application.Calculate) & none of these
tries worked.

Does the Calculate function fail with Vlookup, or is there a problem if
VLookup has a formula that concatenates strings and function calls.

Regards,

"Fredrik Wahlgren" wrote:

>
> "BHARATH RAJAMANI" <[email protected]> wrote in
> message news:[email protected]...
> >
> > I have several Vlookup functions in my worksheet. They do not compute
> until
> > I F2 into every cell. (Tools Options Calculation is Automatic) Can I use
> a
> > macro function that computes the value of the cell and saves me the pain
> of
> > having to F2 every cell that has the Vlookup formula
> >
> > TIA!!
> >
> > My code is as below:
> > Sub update()
> > Range("B1").Select
> > Selection.End(xlDown).Select
> > ActiveCell.Offset(0, 2).Range("A1").Select
> > Range(Selection, Selection.End(xlToRight)).Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
> > False, Transpose:=False
> > Application.CutCopyMode = False
> > 'ActiveWorkbook.Worksheets(1).Calculate
> > Application.CalculateFull
> > End Sub
> >
> >
> >
> >
> > Regards,
> >
> >
> >
> > --
> > Manager, International Private Banking, International Banking Group, ICICI
> > Bank
> > East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai
> India
> > 400051
>
> Try
>
> Sub update()
> Application.CalculateFull
> End Sub
>
> /Fredrik
>
>
>

"BHARATH RAJAMANI" <[email protected]> wrote in
message news:[email protected]...
> Fredrik,
>
> That doesnt work. I tried using Selection.Calculate
> worksheets.cells.calculate Calculate Application.caLculate
> Application.calculatefull Call Sub (Application.Calculate) & none of these
> tries worked.
>
> Does the Calculate function fail with Vlookup, or is there a problem if
> VLookup has a formula that concatenates strings and function calls.
>
> Regards,
>

That's strange. I think you need to show your VLOOKUP formulas.
CalculateFull should do an unconditional recalculation of all functions.

/Fredrik

Original formula is :

"=VLOOKUP("&""""&F$1&""","&"'"&$BI$15&"["&$BG837&".xls]"&$BG837&"'!$1:$65536,6,0)")

Same is copied and pasted as value. It becomes:

=VLOOKUP("BAJAJAUTO",'D:\INVESTMENTS\XLS\[cm13APR2005bhav.xls]cm13APR2005bhav'!$1:$65536,6,0)

This is because cell F1 contains "BAJAJAUTO", Cell BI contains
"'D:\INVESTMENTS\XLS\" and so on.

Now it is only after I press F2 that excel calculates this value, which is
say some number say 1085 which is the value stored in file named
cm13APR2005bhav.xls against BAJAJAUTO in column 6.

How to make excel calculate the formula by its own.

Thanks

"Fredrik Wahlgren" wrote:

>
> "BHARATH RAJAMANI" <[email protected]> wrote in
> message news:[email protected]...
> > Fredrik,
> >
> > That doesnt work. I tried using Selection.Calculate
> > worksheets.cells.calculate Calculate Application.caLculate
> > Application.calculatefull Call Sub (Application.Calculate) & none of these
> > tries worked.
> >
> > Does the Calculate function fail with Vlookup, or is there a problem if
> > VLookup has a formula that concatenates strings and function calls.
> >
> > Regards,
> >
>
> That's strange. I think you need to show your VLOOKUP formulas.
> CalculateFull should do an unconditional recalculation of all functions.
>
> /Fredrik
>
>
>

Original formula is :

"=VLOOKUP("&""""&F$1&""","&"'"&$BI$15&"["&$BG837&".xls]"&$BG837&"'!$1:$65536,6,0)")

Same is copied and pasted as value. It becomes:

=VLOOKUP("BAJAJAUTO",'D:\INVESTMENTS\XLS\[cm13APR2005bhav.xls]cm13APR2005bhav'!$1:$65536,6,0)

This is because cell F1 contains "BAJAJAUTO", Cell BI contains
"'D:\INVESTMENTS\XLS\" and so on.

Now it is only after I press F2 that excel calculates this value, which is
say some number say 1085 which is the value stored in file named
cm13APR2005bhav.xls against BAJAJAUTO in column 6.

How to make excel calculate the formula by its own.

Thanks

"Fredrik Wahlgren" wrote:

>
> "BHARATH RAJAMANI" <[email protected]> wrote in
> message news:[email protected]...
> > Fredrik,
> >
> > That doesnt work. I tried using Selection.Calculate
> > worksheets.cells.calculate Calculate Application.caLculate
> > Application.calculatefull Call Sub (Application.Calculate) & none of these
> > tries worked.
> >
> > Does the Calculate function fail with Vlookup, or is there a problem if
> > VLookup has a formula that concatenates strings and function calls.
> >
> > Regards,
> >
>
> That's strange. I think you need to show your VLOOKUP formulas.
> CalculateFull should do an unconditional recalculation of all functions.
>
> /Fredrik
>
>
>

These are my VLOOKUP formulae in each cell:

=IF($B30=TodayDate,VLOOKUP(D$1,'D:\~Pradip\~My
Documents\2_Personal\RSI\FINAL\[online
data.xls]SUMMARY'!$A$3:$B$55,2,0),IF($B30="","","=VLOOKUP("&""""&D$1&""","&"'"&$BI$15&"["&$BG30&".xls]"&$BG30&"'!$1:$65536,6,0)"))


I use 50 columns and 30 rows for the VLookup function.


Regards,


"BHARATH RAJAMANI" wrote:

>
> I have several Vlookup functions in my worksheet. They do not compute until
> I F2 into every cell. (Tools Options Calculation is Automatic) Can I use a
> macro function that computes the value of the cell and saves me the pain of
> having to F2 every cell that has the Vlookup formula
>
> TIA!!
>
> My code is as below:
> Sub update()
> Range("B1").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(0, 2).Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> 'ActiveWorkbook.Worksheets(1).Calculate
> Application.CalculateFull
> End Sub
>
>
>
>
> Regards,
>
>
>
> --
> Manager, International Private Banking, International Banking Group, ICICI
> Bank
> East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India
> 400051

the code you show pastes values on a sheet. What does that have to do with
Vlookup formulas.

In any event, what you describe would indicate that your formulas are being
seen as text values rather than formulas. when you look at the cell is it
displaying the formula? If not, what does it display?

If you formulas are seen as text and not formulas, you can calculate until
you are blue in the face and they won't be treated as formulas until you
force Excel to reevaluate them. You can do this by selecting the range that
contains the formulas and do

Edit=>Replace
What = (equal sign)
With = (equal sign)

This will be like a mass F2 of your worksheet and your formulas should then
work.

--
Regards,
Tom Ogilvy

"BHARATH RAJAMANI" <[email protected]> wrote in
message news:[email protected]...
>
> I have several Vlookup functions in my worksheet. They do not compute
until
> I F2 into every cell. (Tools Options Calculation is Automatic) Can I use
a
> macro function that computes the value of the cell and saves me the pain
of
> having to F2 every cell that has the Vlookup formula
>
> TIA!!
>
> My code is as below:
> Sub update()
> Range("B1").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(0, 2).Range("A1").Select
> Range(Selection, Selection.End(xlToRight)).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
> False, Transpose:=False
> Application.CutCopyMode = False
> 'ActiveWorkbook.Worksheets(1).Calculate
> Application.CalculateFull
> End Sub
>
>
>
>
> Regards,
>
>
>
> --
> Manager, International Private Banking, International Banking Group, ICICI
> Bank
> East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai
India
> 400051