Invoke VBA - Error in VB Script

Hi, I receive an error when using Invoke VBA, it seems to be an issue with my script.
I will attach my script for reference.

Any information will be greatly appreciated!

Can you post the screenshot of your workflow @shawnmurray

1 Like

Please see image

Two ways to rectify the error @shawnmurray

  1. Remove Option Explicit line from the code or
  2. declare every variable you have in the code with their corresponding types :slight_smile:

Thanks for the quick response @HareeshMR

Which would you recommend? :slight_smile:

This one is easy to do and

This one needs to be done very clearly and carefully.

Go with first one. It won’t affect the execution :slight_smile:

1 Like

Perfect thank you :slight_smile:

I got so far and ran into error!

And see script


Can you explain what is CSR there @shawnmurray

1 Like

It is the column header of column BU @HareeshMR

I don’t find any variable with CSR in the code you have and you are trying to pass the variable in the code. Instead of that , you can give the column value directly as BU right?

Please make sure you are passing the correct value @shawnmurray

1 Like

So instead of using CSR , i can just reference the column by using BU as thats what column CSR is? @HareeshMR

yes, as you are giving as BT2, use $BU there. But I don’t know why you are using A:B in the formula. If it is so, then I hope CSR is a sheet name :slight_smile: @shawnmurray

You want me to replace BT2 with $BU or replace CSR.Name with $BU?

Thanks :slight_smile: @HareeshMR

This one you need to replace along with the double quotes and ampersands you have before and after

1 Like

Like this?

Even after replacing, why do you have A:B there? @shawnmurray

Make sure the vlookup is correct . Copy and paste the formula in the same excel and check if the value is correct. I’m sure that the value needs to be a sheet name there. Please confirm this :slight_smile:

1 Like

If i run as a macro in the workbook manually it works fine, would it be easier to use the execute Macro Activity?

All i was told to do was: In Column BU enter a V-look up formula: =VLOOKUP(BT,CSR!A:B,2,0)
And thats what i am trying to do @HareeshMR

Then pass it as same as above . It will work @shawnmurray … and a aphostrophe is missing in the above formula. Please check. It should be before the sheet name

1 Like

An apostrophe is missing from the formula in my screenshot? @HareeshMR


Yes, please try pasting it in a cell manually and check if it is working. Then use it in the Macro @shawnmurray

1 Like