[RESOLVED] Inset vlookup into a Excel Cell

[toecutter]

Hi,

I am trying to insert a vlookup formula into a excel cell.

This

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:

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

[ForumAccount]

Try setting the FormulaR1C1 property of the cell instead.

[toecutter]

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.

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