Can't fix "Method Range of Object"_Global Failure. 2

[RSJohnson]

I am trying to define where data copied from one worksheet should be placed on another. I am able to set all other variables except this one. The variable is dimensioned only in the sub where it is located. As I F8 through the sub I get a Runtime error 1004;: Method ‘Range of Object’_Global failed.

The code below is my attempt to set my variables. The Set rngDestin is where the error occurs.

No clue what to look for a or where to start. Can someone suggest possible cause of error?

Previous help from this forum seem to fix the problem. But it didn't. I was told to change the last statement to:

Set rngDestin = Worksheets("Prepared_Expense").Range("R6")

This did not work. Thread 707-797295 was the orginal request for help and the two responses I got.


Sub MatchPCA()
'
' MatchPCA Macro
' Macro recorded 2/20/2004 by Robert S. Johnson

Dim intCounter As Integer, intStop As Integer
Dim rngGMPCA As Range, rngDAFRPCA As Range, rngDestin As Range, rngToCopy As Range

'This initializes the variable for the first match.
Set rngDAFRPCA = Range(Worksheets("Prepared_Expense").Range("Q6").Offset(1, -15))
Set rngGMPCA = Range(Worksheets("First QT").Range("B4")) 'Change to variable!
Set rngToCopy = Worksheets("First QT").Range("CB5:CE5") 'Change to variable!
Set rngDestin = Range(Worksheets("Prepared_Expense").Range("R6"))

 

[gh61]

Try removing the Range keyword from your Set lines:

Set rngDAFRPCA = Worksheets("Prepared_Expense").Range("Q6").Offset(1, -15)
    Set rngGMPCA = Worksheets("First QT").Range("B4")
    Set rngToCopy = Worksheets("First QT").Range("CB5:CE5")
    Set rngDestin = Worksheets("Prepared_Expense").Range("R6")

HTH

 

[TonyJollans]

Hi semaphore,

From your initial response in the earlier thread it seems that the advice given solved the problem. You now have another response giving you the same advice.

When you remove the extra Range methods you should not get the same error so what error is it that you do get? Did you change anything else before the (new) error? Can you post any further information which might help?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[RSJohnson]

GH61,
I apologize, the code in my message is not the code I am current working with. But the results is the same.

Tony,

I remover the first Range as you suggested earlier.The execution stops at;

Sheets("Prepared_Expense").Select

The error is the same; Runtime error 1004;: Method ‘Range of Object’_Global failed

The error is the same as before with the same variable except in a different place in the code


Sub MatchPCA()
'
' MatchPCA Macro
' Macro recorded 2/20/2004 by Robert S. Johnson

Dim intCounter As Integer, intStop As Integer
Dim rngGMPCA As Range, rngDAFRPCA As Range, rngDestin As Range, rngToCopy As Range

Sheets("Prepared_Expense").Select
Range("R5").Select

'This initializes the variable for the first match.
Set rngDAFRPCA = Sheets("Prepared_Expense").Range("Q6").Offset(1, -15)
Set rngGMPCA = Sheets("First QT").Range("B4") 'Change to variable!
Set rngToCopy = Sheets("First QT").Range("CB4:CE4") 'Change to variable!
Set rngDestin = Sheets("Prepared_Expense").Range("R6")

'Initializes counter.
intCounter = 1

'Get starting cell for GM expenses data.
Do
mstrGMStart = InputBox("Enter cell location of the start of GM expense data.")
If mstrGMStart <> "" Then
ActiveCell.Value = mstrGMStart
Exit Do
Else
If mstrGMStart = "" Then
MsgBox "You didn't enter cell location, please do so. "
End If
End If
Loop


'On Error Resume Next
'Set mstrGMStart = Application.InputBox _
'(prompt:="Enter cell location where GM data begins on GM worksheet.")

'On Error GoTo 0
'If mstrGMStart Is Nothing Then
'MsgBox "You didn't enter cell location, please do so. "
'End If

intStop = Sheets("First QT").Range(mstrGMStart).CurrentRegion.Rows.Count 'Change to find l

Do Until intCounter = 53 'intStop
If rngDAFRPCA = rngGMPCA Then

'ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("First QT").Select
rngToCopy.Select
Selection.Copy
Sheets("Prepared_Expense").Select
Range(rngDestin).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'Moves to next row in both worksheets and the next cell to receive copied data.
rngDAFRPCA = rngDAFRPCA.Offset(1, 0)
'rngGMPCA = rngGMPCA.Offset(1, 0)
rngDestin = rngDestin.Offset(1, 0)

Else
'Moves to next DAFR PCA and test for match.
'rngDAFRPCA = rngDAFRPCA.Offset(1, 0)

'Moves to next GM PCA and test for match.
rngGMPCA = rngGMPCA.Offset(1, 0)

'Records comment in cell with DAFR PCA to alert user to non-matching problem _
all the DAFRPCAs have been tested.

If intCounter = intStop Then
rngDAFRPCA.Comment = "DAFR PCA has no match in GM worksheet, please research."
rngGMPCA.Comment = "DAFR PCA has no match in GM worksheet, please research."
rngDestin.Comment = "No data retreived."

'Moves to next row in both worksheets and a new destination cell.
rngDAFRPCA = rngDAFRPCA.Offset(0, 0)
rngGMPCA = rngGMPCA.Offset(1, 0)
rngDestin = rngDestin.Offset(1, 0)
intCounter = 0
End If
End If
'Increments counter.
intCounter = intCounter + 1
Loop
End Sub

I had to computed the 4 percentages based on actual expenses. They are computed from data on Sheet 1 and stored on sheet 1. I am attempting to copy the 4 percentages from sheet 1 and paste their values to the appropriated row on sheet 2 then calculate and record the dollar value for 4 categories of expense on sheet 2 in the appropriate row. This is an attempt to just copy the percentages from one page to the other.

 

[PHV]

Does the "Prepared_Expense" sheet belongs to the Active workbook when the macro is called ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Yes all the worksheets are in one workbook.

 

[TonyJollans]

Hi semaphore,

The error message refers to object _Global. If something in your code is unqualified it falls, by default, under the Global object and it may or may not be possible to properly resolve it. In this case it is a Range and Excel doesn't know to which Sheet you are referring.

Right at the beginning you have ..

[purple]    Sheets("Prepared_Expense").Select
    Range("R5").Select[/purple]

.. you need to be more explicit. What sheet is the [purple]Range("R5")[/purple] on? Try ..

[blue]    Sheets("Prepared_Expense").Select
    [highlight]Sheets("Prepared Expense").[/highlight]Range("R5").Select[/blue]

(or other sheet if that's the wrong one)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[RSJohnson]

Thanks, I will change combine to two statements.

 

[RSJohnson]

Thanks, I will substitute you suggested code.

 

[RSJohnson]

Tony,
I substituted the code and got the same error. I then commented out the two lines of code. This code just place the cursor where I know where it is because whatever is entered in the input box is also written in the cell where ever the cursor happens to be. So I placed the cursor where it would not be where data might be transfered to.

I tried being more specific with the code that is highlighted when I get the ...'Global...error.

'Range(rngDestin).Select
Sheets("Prepared_Expense").Range(rngDestin).Select

Now I get a 1004; Application defined or Object defined error, with the, Sheets("Prepared_Expense").Range(rngDestin).Select being highlighted.
Same error number different description of error.

 

[xlbo]

I think the issue belongs to this line:

Set rngDestin = Sheets("Prepared_Expense").Range("R6")

Because you have set the range to include the sheet, you do not use the sheet to use the range

Also, it is defined as a RANGE not a STRING - therefore the syntax

Range(rngDestin).Select

is incorrect - you need to use:

rngDestin.Select / rngDestin.Activate

instead

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi semaphore,

I do appreciate you are trying to help yourself (and don't want to discourage you in any way) but each time you come back there seems to be some subtle change which introduces a new error.

If I understand correctly, you are now trying to use rngDestin at the beginning of your code before it has been set to anything. Whether or not that's the case, what Geoff says is also true and you seem to be getting mixed up with Objects (Sheets and Ranges) and Strings (Names and Addresses).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[RSJohnson]

Tony,

The location of the code did not change. I was attempting to apply your advice (see below) to where the error was occurring.

you need to be more explicit. What sheet is the Range("R5") on? Try ..


CODE
Sheets("Prepared_Expense").Select
Sheets("Prepared Expense").Range("R5").Select

I will make the change Geff suggests.

 

[RSJohnson]

Thanks Geff and Tony,

Tony, I will try to contain my urge to "try something" in lieu of a logic based reasoned approach to debuggging my code (a lot more experiance would also be useful in my case.)

Correcting the syntax worked. Now when, rngDestin = rngDestin.Offset(1, 0), is executed it causes the value in the destination cell (R6) to be erased. The values in the other cell remain unchanged.

 

[xlbo]

and is that what you want to happen ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[RSJohnson]

No, I want the next iteration to start put the results at R7 with not effect on the previous results.

 

[TonyJollans]

Hi semaphore,

My apologies - the last thing I want to do is stop you trying; I'm just finding it hard to follow which bits of code have been changed each time.

I think you're getting Ranges and Values mixed up - perhaps understandably in this case.

The statement .. [purple][tt]rngDestin = rngDestin.Offset(1,0)[/tt][/purple]

.. actually acts on the contents of the Cells. A Range has a default Property of [blue].Value[/blue], so the statement above is actually short for ..

[blue][tt]rngDestin[highlight].Value[/highlight] = rngDestin.Offset(1,0)[highlight].Value[/highlight][/tt][/blue]

To act upon the Range itself, you must use the Set statement, so ..

[blue][tt][highlight]Set[/highlight] rngDestin = rngDestin.Offset(1,0)[/tt][/blue]

.. will, I think, do what you want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[RSJohnson]

Thanks Tony. The code loops ok but stops for no appearent reason and it's one iteration short when ever the PCAs change. I think I can find how to fix the iteration problem the stopping is another matter.

Thanks to both you and Geoff for taking the time to explain what I was doing wrong rather that just saying here try this code.