Invoke VBA - Error in VB Script

Help
,
1

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

image
image722×958 26.5 KB

Any information will be greatly appreciated!
Thanks

2

Can you post the screenshot of your workflow @shawnmurray

3

Please see image
image
Thanks

4

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:
5

Thanks for the quick response @HareeshMR

Which would you recommend? :slight_smile:

6

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:

7

Perfect thank you :slight_smile:

I got so far and ran into error!
image

And see script

image
image1023×922 28.3 KB

Thanks!

8

Can you explain what is CSR there @shawnmurray

9

It is the column header of column BU @HareeshMR

10

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

11

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

12

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

13

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

Thanks :slight_smile: @HareeshMR

14

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

15

@HareeshMR
Like this?

image
image721×264 8.39 KB

16

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:

17

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

18

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

19

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

Thanks

20

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