select method of range class failed (sometimes)

[vba_php]

hey you experts,

got another problem with the same files I've posted about 3 times now. sometimes this code breaks with the error ""select method of range class failed"":

With wsDrawings
    LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("O2").FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-1],RC[-3])"
    .Range("O2").Copy
    .Range("O2:" & "O" & CStr(LR1)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End With

and sometimes it doesn't. image 1 below shows one run where it actually DID work, broken one line after the offending line that sometimes throw the err. the second image shows that instance worked fine. other than me not killing Excel to dump all the memory before every test run, why would excel do this sometimes and sometimes not?

[vba_php]

I've just run into it again. below are 2 images of where it breaks.

[huuthang_bd]

If you select a range on an inactive sheet, the error will occur.
In your case, I thing the code work fine if wsDrawings is active sheet and error if active sheet is another.
Maybe the following code will solve your problem.

With wsDrawings
    LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("O2").FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-1],RC[-3])"
    .Range("O2").Copy
    .Select
    .Range("O2:" & "O" & CStr(LR1)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End With

Besides, you can paste without select destination range. Like this:

With wsDrawings
    LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("O2").FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-1],RC[-3])"
    .Range("O2").Copy .Range("O2:" & "O" & CStr(LR1))
End With

Or set formula for all cells of range one time.

With wsDrawings
    LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("O2:" & "O" & CStr(LR1)) = "=IF(ISBLANK(RC[-3]),RC[-1],RC[-3])"
End With

[vba_php]

your code does not include anything to ensure that DRAWINGS is the active workbook. and your code blocks may work, however since I posted this I was able to run a test on many different codes in order to get it to work. "'drawings"" was the active window/book anyway, and always was, per the resulting code that I ended up using in the end, which was this:

wbDrawings.SaveAs (ThisWorkbook.Path & Application.PathSeparator & "temp.csv")
Set wbDrawings = Workbooks("temp.csv")
Set wsDrawings = wbDrawings.Sheets("temp")

With wsDrawings
    LR1 = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("O2").FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-1],RC[-3])"
    .Range("O2").Copy
    .Range("O2:" & "O" & CStr(LR1)).PasteSpecial (xlPasteFormulas)
    Application.CutCopyMode = False
End With

as you can see, ''drawings'' is the active book because it is being SET before the relevant code runs. this kind of stuff is exactly why I don't work with office products anymore. it is totally not trustworthy and wastes a lot of my time with its inconsistencies. It's too bad that MS doesn't care about it, because office products really are quite useful in a lot of business scenarios. =/

thanks for your reply.

[romperstomper]

Assigning an object to a variable does not make it active. It is entirely trustworthy that you can't select a range on a sheet that is not active.

[vba_php]

Assigning an object to a variable does not make it active.

I realize that rorya, but based on the millions of tests that I ran, it became evident that this line of code automatically made the book active:

wbDrawings.SaveAs (ThisWorkbook.Path & Application.PathSeparator & "temp.csv")

thus, the SET statements are irrelevant with regard to the issue.

[romperstomper]

I wouldn't rely on that, especially in later versions of office. One reason it is always better to avoid selecting unless absolutely necessary (which is rarely). Also, reassigning the workbook variable is not necessary there.

As a general point, I'd suggest you might want to be less dismissive of people who try to help you, unless you want them to stop trying.

[vba_php]

what makes you think I was dismissive of you? I wasn't. sorry for the misunderstanding. I always appreciate professional advice like yours and Ali's, and it's always welcome of course. Like i've said many times, I'm more of a manager rather than a code writer. And as a last resort, if I can't find an answer, what I always do is test the living heck out of the issue because when that happens, eventually an answer is found, even if it's not the most efficient. there are so many possibilities for answers in code platforms, it's literally impossible to know what will work and what won't under any given circumstance. that's exactly why programmers are getting out of the business. The american ones anyway. and I assume also, that's why many programming jobs are given to green card holders. Americans don't want to do boring, cumbersome and soul-deadening jobs. That's classic American corporate greed for ya. But again, that's only my assumption. have you seen this?

too much to learn to be a software developer
https://www.youtube.com/watch?v=NN1oN0-1M4A

[romperstomper]

I never said you were dismissive of me.

[MollyBrennholz]

Adam, I have definitely had random occurrences of an error like you mentioned, all be it very rarely. When it has happened , I was pretty damm sure it shouldn’t have happened.
I think we all agree that Activateing and Selecting when dealing with worksheet ranges via VBA is rarely needed and is usually a bad idea as the interaction with a spreadsheet slams the brakes on.

I will usually optimise a macro first, with no Activateing and Selecting , ignoring the odd error of that sort you mentioned.
After that I will often see if I don’t compromise the performance much if I add an occasional code line pair of something like
Worksheet("x").Activate: Worksheet("x").Range("A1").Select
Or, if dealing with multiple open workbooks,
Workbooks("x“).Activate: Worksheet("x").Activate: Worksheet("x").Range("A1").Select
at some strategic points.

A typical point would be just before I start doing things to ranges in Worksheet("x") via VBA. I know those two ( three ) code lines should be unnecessary. But it’s been my experience that they help stop that occasional error.
I have no idea what causes the occasional error when all suggest it should not error. I think possibly Excel has some memories of what was last active. Possibly that can become corrupted, and doing a quick Worksheet("x").Activate: Worksheet("x").Range("A1").Select refreshes it.

One thing that has already been touched on here in the Thread a couple of times, which has caught me out a few times: Selecting a range does not activate the worksheet of the range you select.
If the worksheet is not active and you try to select that range then you will get that error.
But selecting a worksheet does activate that worksheet. (Activateing and Selecting a worksheet do something similar, - I think the main difference being that you can select things, but only activate a thing. I have not explored that much yet… )

…but based on the millions of tests that I ran, it became evident that this line of code automatically made the book active:

wbDrawings.SaveAs (ThisWorkbook.Path & Application.PathSeparator & "temp.csv")

.....

I would hazard a guess that that might be version dependent and possibly unreliable, as Rory suggested. That dose not consistently activate the workbook being saved, for me.

Molly