[RESOLVED] Inset vlookup into a Excel Cell
Hi,
I am trying to insert a vlookup formula into a excel cell.
This
vb.net Code:
moApp.Cells(row, col).Formula = "=IF(ISNA(VLOOKUP($B:$B,'" & dDircetory & "[PricingSheet.xls]Pricing'!$A$1:$D$5000,2,FALSE)),"",VLOOKUP($B:$B,'" & dDircetory & "[PricingSheet.xls]Pricing'!$A$1:$D$5000,2,FALSE))" '.ToString
causes a error:
vb.net Code:
System.Runtime.InteropServices.COMException was unhandled
ErrorCode=-2146827284
Message="Exception from HRESULT: 0x800A03EC"
Source=""
StackTrace:
at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object o, Type& objType, String name, Object[] args, String[] paramnames, Boolean OptimisticSet, CallType UseCallType) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase, CallType CallType) at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSetComplex(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean OptimisticSet, Boolean RValueBase) at DIRECTORY_V2.TakeOffList.ExportToExcel() in C:\Users\toe\My Documents\Visual Studio 2008\Designing\DIRECTORYv2.5.0\DIRECTORY\TakeOffList.vb:line 280 at DIRECTORY_V2.TakeOffList.ExcelToolStripMenuItem_Click(Object sender, EventArgs e) in C:\Users\toe\My Documents\Visual Studio 2008\Designing\DIRECTORYv2.5.0\DIRECTORY\TakeOffList.vb:line 359 at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e) at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e) at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e) at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met) at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ScrollableControl.WndProc(Message& m) at System.Windows.Forms.ToolStrip.WndProc(Message& m) at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg) at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at DIRECTORY_V2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel) at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly() at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData) at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext) at System.Activator.CreateInstance(ActivationContext activationContext) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()
InnerException:
It seems to be something to do with the "=" sign as when i remove it it is inserted into the cell as a string not a formula.
A messagebox seems to indicated the formula is correct
http://img801.imageshack.us/img801/4742/msgbox.jpg
Re: Inset vlookup into a Excel Cell
Try setting the FormulaR1C1 property of the cell instead.
Re: Inset vlookup into a Excel Cell
Thanks ForumAccount,
I think i just needed a break from the computer as it seems the quotes in the centre of the formula were incorrect.
vb.net Code:
Dim fFormulaToEnter As String = Nothing
fFormulaToEnter = "=IF(ISNA(VLOOKUP($B:$B,'" & dDircetory & "[PricingSheet.xls]Pricing'!$A$1:$D$5000,2,FALSE)),"""",VLOOKUP($B:$B,'" & dDircetory & "[PricingSheet.xls]Pricing'!$A$1:$D$5000,2,FALSE))" '.ToString
Try
moApp.Cells(row, col).Formula = fFormulaToEnter
Catch ex As Exception
MsgBox(ex.ToString())
End Try
