Problem : Excel formula using VB code

[Syan]

Hi All,

I've got a problem with excel.

I am facing this problem while I enter formula in the excel sheet using vb code. I have selected a cell and then I use the code,

activecell.formulaR1C1 = "=vlookup(blah,blah...)"

and then I fill this formula to the desired range of cells.

What happens is....the formula is written in the cells as a text and the formula is not picking the value from the page mentioned in it.

In the excel sheet, in the desired cell (eg H1) the content remains as =vlookup(blah,blah...) instead of the value.

Can anyone help me out of this???

[emartinho]

Welcome to the forum!

Give us a full example. "blah, blah, blah" doesn't tell us if you correctly coded the formula! Also, instead of Activecell try using a range:

Worksheets("Sheet1").Range("B1").FormulaR1C1 = <formula here>

It's just better practice to use a specific range instead of Activecell.

Another suggestion: use a different, simpler formula i.e. =SUM and see if you get the same result. For example, this works fine for me when A1 through A3 have 1,2,3 respectively, giving me a value of 6:

Worksheets("Sheet1").Range("B3").FormulaR1C1 = "=SUM(r1C1:r3C1)"

Just thought of something else, check the format of the cell. Is it "Text"? If so, formulas won't work in any cell that is "Text" format.

Sorry for the train of thought post, but I hope it helps.
-EM

[Siddharth Rout]

activecell.formulaR1C1 = "=vlookup(blah,blah...)"

Welcome to the forums Syan

I guess this is what you are trying?

ActiveCell.Formula = "=vlookup(blah,blah...)"

If it still shows you the formula rather than calculating it then do the following... (Since I don't know whether you are using Excel 2003 or 2007, I am including solution for both versions)

1) Excel 2003

Click on Tool~~>Options~~>View Tab (see picture below)



Uncheck the checkbox "Formulas" under "Window options"

2) Excel 2007

Under Formula Tab, unselect the option "Show Formulas"

Hope this helps...

[anhn]

The cell was formatted as Text.
Change it's NumberFormat to General, it will accept formula as formula not Text.

[Syan]

Guys,

Thanks for your replies...

I used the formula,

activecell.formular1c1 = "=vlookup(B:B,'EMP DETAILS'!B:E,4,false)"

;;;;;;;;;;;;;;;

I even tried using

activesheet.range("H3").value = application.worksheetfunction.vlookup([B3],worksheet("EMP DETAILS").range("B:E").value,4,false)

but I get an error called

unable to get the vlookup property of worksheet function.....

Actually what I am trying to do is to fill in values from a sheet into other sheet.

Is there any other possibles that you guys could suggest..

[Siddharth Rout]

I recommend going through Post # 3 again

[Syan]

koolsid,

Its already unchedked....

[Syan]

Even the format of the cell was "General"..

[SQLADOman]

Hi Syan,

What happens when you run this:

With ActiveCell
    .NumberFormat = "General"
    .Formula = "=vlookup(B:B,'EMP DETAILS'!B:E,4,false)"
End With

[Siddharth Rout]

koolsid,

Its already unchedked....

I recommend going through Post # 3 once again