Value = Value error

[jomili]

I'm trying a simple macro, and am not sure why I'm getting an error. The macro is below.

Please Login or Register  to view this content.

The error occurs when trying to copy the value from column Z into column X. Besides the "Columns" method above, I've also tried using

Please Login or Register  to view this content.

Both ways I get a runtime 1004, Application Defined or Object defined error.

The macro lives in my Personal.xls, and I'm running it on TodaysWPRs.xls. Do I need to declare a sheet name or anything? The first two lines work just fine.

[JBeaucaire]

All of the macros that I store in my Personal.xls I construct to work on the 'activesheet' or the specific file.

For the activesheet, I'd suggest:

Please Login or Register  to view this content.

To only run on one specific file like you've suggested, set a reference to the file AND to the sheet in the file the code should run on:

Please Login or Register  to view this content.

[jomili]

Thanks Jerry, for showing me two ways to do it. I wasn't sure I'd need the "Activesheet", but after putting that in the macro works like a champ. For this application I don't need to specify in the macro to look for the specific worksheet, but that's a good techinique to keep for future use. Thanks so much!

John

[jomili]

Okay, same problem, except this time I'm running it from Workbook1, operating on the "Details" tab of Workbook2 (the active sheet). At this stage I've done a few things already on the active sheet, so don't see the need to have the "With" part below, but I put it to be on the safe side. I get the same error code no matter how I configure it, either "With Activesheet" and the periods, or without it and no periods. Both ways I get a runtime 1004, Application Defined or Object defined error at both of these lines:

Please Login or Register  to view this content.

. VBA drives me crazy! Below is the code I'm using. I'm sure it's something simple I'm overlooking.

Please Login or Register  to view this content.

[snb]

Try to be consistent in writing code:

Please Login or Register  to view this content.

[jomili]

One day, when I move from neophyte to rank amateur, I'll be able to write code consistently. For the time being I have to rely on the crumbs the Excel gods throw down from their table. Thank you so much for the code and advice. I had solved the problem temporarily by doing a copy/paste, but your code is much more elegant. Thank you.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[jomili]

I said it was more elegant; I didn't say it worked.

SNB, I've just gotten a chance to test your macro. I'm getting the same error code, runtime 1004, Application Defined or Object defined error, at the line

Please Login or Register  to view this content.

Or, lets say I got that at first.

I wanted to send sample data, but have to be careful with our data, so I did a Replace to randomize the names and words (replacing letters with symbols and numbers, etc.).

Then I saved my workbook, reopened it, and ran the code again. It worked just fine.

So I went back to my original data, saved, reopened, ran the code, and got the error again.

So it looks like something in my original data is hosing the macro. Any ideas what could be fouling the code?

[JBeaucaire]

I don't see why the .Value is failing in either version of the macro. Perhaps this less elegant approach will at least work:

Please Login or Register  to view this content.

If that fails, then perhaps:

Please Login or Register  to view this content.

[romperstomper]

Out of curiosity, try a loop one cell at a time, and see which one blows up.

[jomili]

I've attached a scaled down sample of the workbooks I'm working with. The columns are a little different, but still SNBs original code. In this one we're working in the SNB Code workbook, and copying from the Today's WPR workbook. If things are the same on your system as on mine, it will fail when it hits the "value" line.

I haven't tried the new code you provided, because I already have a copy/paste one that works. I'm just wanting to find out why this one DOESN'T work, because that same error keeps tripping me up.

Going line by line is my next task, to see if I can find out where/why it's blowing up.

Thanks,
John

[jomili]

Okay, commenting out the Value = value line got the code to start pasting into column E, where it was consistently stopping at line 783, WPR A110075553.

I then replaced the text in X1100 of Today's WPR with the phrase "Text Phrase" and resized the row. Then when I ran SNBs full code, it ran without a flaw.

I close everything out, and tried again, and again AFTER changing X1100 the code ran fine.

So I guess the question here is, what in X1100 is causing the problem, and how do I safeguard against it?

[jomili]

Whoops, not X1100; I just reopened my "today's wpr", and it looks like it's on line 1353. I forgot I shuffed things. Sorry to misdirect.

[jomili]

Okay, I think I've solved part of the "Why", but not the "Why" of the "Why". The factor that's messing up the code is that the cell being brought over, the text for WPR A110075553, has 929 characters in it. By trial and error, I've found that if the number of characters exceed 911, the macro fails. By trimming down that cell, the macro works.

So, now I need one of two things:

A) A way to enable my macros to do their tasks without being affected by the character limit (that may be tricky)

Or

B) A trim function that will take off characters at the backend of the cell to limit it to 911 characters (which in this case would be a very acceptable solution).

Should I start a new post on that, or can someone help me tackle it in this one?

[romperstomper]

See MSKB.

You could use LEFT in an evaluate formula, or dump the data into an array, loop and limit to 910 and then populate the target.

[jomili]

Thanks Romper. I think the suggestion about using LEFT is the one I'll go with ('cause I think I know how to do that one). Since I rerun the "Today's WPRs" every morning, I'll have a macro in my personal.xls that will populate the LEFT formula in an adjacent column, then replace the original cells with the truncated values. Then in my later routines where I harvest data from "Today's WPRs" I won't be blindsided by these errors.

As soon as I whip this little macro together I'll post it and close out this thread, but I'm ALWAYS open to suggestions and advice.

Thanks,
John

[jomili]

Sorry I didn't get back to this yesterday. I whipped it up before I left for the day, but had no time to test it or reply back. But, I've tested it today and it works like a champ. Here's the code I used:

Please Login or Register  to view this content.

I may revise it using SNBs code; that's a more elegant approach and I want to explore it, but this one works for me now. And NOW, I can close some other threads, because that 911 error has caused me OTHER problems elsewhere. I love it when I find a root cause and can fix it!

Thanks to everyone for all the help. You are the best!

[snb]

I'm not to keen on using worksheetformula when using VBA.

Please Login or Register  to view this content.

if the number of characters will pose a problem:

Please Login or Register  to view this content.

[jomili]

SNB,

I'm trying it out, and hitting an error at "SN = ...", saying the variable isn't defined. What should I define it as?

Defining it as a Range, I hit the same error on sp, st, and j, then on the line

Please Login or Register  to view this content.

I get an error that it expects an array.

[snb]

Simply delete 'Option Explicit'

[jomili]

Okay, that's better.

In your "SNB" macro, I get an error code 1004 at the line

Please Login or Register  to view this content.

However, the SNB2 macro seems to work just fine, and truncates the text at 911 characters.