Macros Run time error - please help

[Lethal27]

Hi all, i am the lucky one in my company that apparently volunteered to do reports for everyone.
I have set up a macros to do several look ups and to create a final csv file used to upload into a CRM.

I have re-written the macros several times and when its first finished it works ok but when i do an update days later, i get the run time error '1004'
Application-defined or object defined error

When i debug the file it goes here - please help ???
Run time error debug.jpg

[BellyGas]

What's the active cell that command is trying to change?
Is it locked?

That looks very much like recorded code. If you post the entire subroutine in [code] tags we can tidy it up for a start.

[Lethal27]

i hope this is what you are after.
It looks up 3 other excel sheets and performs a vlookup - that's it.
As mentioned, it will work after originally creating the macros, then a day later it will not.

[jeffreybrown]

f you post the entire subroutine in [code] tags we can tidy it up for a start.

You may have missed this part of the last response. Please adjust accordingly.

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #3 about the proper use of code tags and adjust accordingly...

[Lethal27]

Even after reading the code tags - still not 100% sure what i need to provide or how to find it. Clearly not an expert.

[jeffreybrown]

You post your code, then highlight your code and select the # in the toolbar above.

In the end...

[ code ]This is where your code goes [ /code ]

Note: Without the spaces

As in the example

[ code ]
Sub HelloWorld()
Msgbox "HelloWorld"
End Sub
[ /code ]

[Lethal27]

Hope i have it right - don't want to waste people's time but i really want to find a solution - thanks jeffreybrown

Please Login or Register  to view this content.

[mike7952]

Would be a lot better If you could upload an example of your workbook with dummy data but shows the exact data structure. And show what your data should look like after macro runs. Looks like your using 2 or three other workbooks in your vlookup.

[humdingaling]

you have a lot of redundant code there

replace

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

and it should avoid any issues with activecell

[humdingaling]

your code runs like a snail without the 3 files being open

Please Login or Register  to view this content.

with all 3 files open before you run the code...it seems to run a lot faster

[Lethal27]

Hi Guys, even with a tidy up, it still doesn't work and yes i always run the macros with the 3 files open.
I have added all 5 files that i work with (dummy info)
1 is the download csv - the one i run the macros in
2 - 4 is the look up info
5 is the copy / save as file i use to upload.

[humdingaling]

sorry Rookie mistake

vba cannot output #N/A so if your first vlookup gives you #n/a it will crash
which is why it probably worked before but not now

need to go back to drawing board on how to circumvent

[Lethal27]

Is there any other way to change the #N/A to 0 so it will work in the macros? i have tried a few different ways of replacing the #N/A but maybe in the macros i could find all #N/A and replace with 0? Would this work or is it based on the result of #N/A that the macros doesn't like?

[humdingaling]

duplication

[humdingaling]

your worksheet name changed

change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

full code now is

Please Login or Register  to view this content.

[Lethal27]

I have copied the code and it works but i will try it again tomorrow - petty sure it will return the same error - Hopefully not. i also downloaded my 3 other files and run the macros on them as well.
Thanks in advance

[snb]

If you use VBA the use of Excelformulae like 'Vlookup' is redundant and slowing down the calculations dramatically.

[Lethal27]

Has worked today where previously it didn't so i would say problem solved - thanks to all who helped.

[jeffreybrown]

You are very welcome, but suppose I did not help much at all. There are plenty of truly smart folks about VBA other than me...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.