使用vb.net将excel单元格值设置为查找公式 [英] Make excel cell value a look up formula using vb.net

问题描述

我正在尝试使用excel互操作创建一个excel工作簿。我有一张名为MonthlyBreakdowns的工作表，其中的值从数据集填充我现在想要的是填充另一张名为Template的工作表，其中包含来自MonthlyBreakdowns工作表的某些值



我试过了这两个但他们似乎给了我必要的结果

I am trying to get a excel work book created using the excel interop. I have one sheet called MonthlyBreakdowns where the values are populated from Dataset What I want now is to populate another sheet called Template with certain values from the MonthlyBreakdowns sheet

I have tried these 2 but they dun seem to give me the necessary result

CType(TempWorksheet.Cells(2, 2), Excel.Range).Formula = String.Concat("=VLOOKUP(", """", "010", """", ";MonthlyBreakdown!A1:E143;5;FALSE)")

CType(TempWorksheet.Cells(2, 2), Excel.Range).Value = String.Concat("=VLOOKUP(", """", "010", """", ";MonthlyBreakdown!A1:E143;5;FALSE)")

推荐答案

首先，在单元格中手动编写公式，确保它有效。

这是vb代码的模型。



然后，stor将此公式作为公式嵌入到单元格中。



Adice：查看VBA for Excel的教程

要知道如何做你手动做的事情，在你输入时记录一个宏，然后看看宏如何记录它，它应该给你提示如何做。

First, manually write your formula in a cell, make sure it works.
This is the model for the vb code.

Then, store this formula embedded in quotes as a formula into a cell.

Adice: look at tutorials for "VBA for Excel"
To know how to do something that you do manually, record a macro while you type it, then look how the macro recorded it, it should give you hints about how to do.

这篇关于使用vb.net将excel单元格值设置为查找公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！