Method 'Range' of object '_Global' Failed

[jsmilke]

Hello everyone,

New to VBA and I'm in need of some assistance. I'm attempting to insert a formula into a dynamic range of cells, but when I run the code I receive the "Method 'Range' of object '_Global' Failed" error message. Here is my code:

Please Login or Register  to view this content.

I can't seem to figure out what I'm doing wrong. Any Ideas?

[Norie]

What are the values of LC and LA when you get the error?

[jsmilke]

LC - 577
LA - 1048577

This code seems to work if I have data in more than the first row populated in column C, but as this is the start of the spreadsheet so there is no data other than the column header at this point.

[Norie]

When you use xlDown on a sheet with no data then you'll end up at the very last row on the sheet which is row 10458576.

With LA being 1048577 you are going beyond the bottom row of the sheet.

Where are you trying to put the formula?

[jsmilke]

The data in column B exceeds the data in column C. I would like the formulas to be pasted in column C up to the point where the data in column B stops.

Hope that makes sense.

[Norie]

So you want to find the first empty row in column C and copy the formula down until the last populated row in column B?

Please Login or Register  to view this content.

[jsmilke]

That's exactly right, and it worked like a charm when I ran the code by itself. However, when I run it with the rest of my code I get an 'Application defined or object defined error'.

[Norie]

What other code?

[jsmilke]

Don't laugh, I'm sure this is horrible inefficient, but I'm a noob.

Please Login or Register  to view this content.

[jsmilke]

FC_Macro_Sample.xlsm

This is a sample of what I'm trying to do. The data exists in Sheet1. I'm trying to write a macro get it in the format in sheet3.

[Norie]

Actually a mistake by me, missed a couple of important dots.

Please Login or Register  to view this content.

[jsmilke]

It works! But, due to my other code, Excel nearly craps out on me in the process.

Thank you for the fix.

[Norie]

I'm pretty sure the rest of the code can be tidied up and that would hopefully speed things up a bit.

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[jsmilke]

I've actually created another thread for what you discribed:

http://www.excelforum.com/excel-prog...aste-loop.html