Run-time error after calling a procedure

[Guest]

Here is a segment of my code:

InsertSpectrum
ActiveSheet.Range(Cells(12 + j, 22), Cells(12 + j, 35)).Value = _
ActiveSheet.Range("V10:AI10").Value

where InsertSpectrum is another procedure in the same project.

The 2nd line does not execute; instead, I get an error "Run-time error
'1004': Method 'Cells' of object '_Global' failed". If I remove the call to
InsertSpectrum, the next line executes properly.

What can I check to correct the problem, so that the second line will run
after the procedure call?

 

[Guest]

I am assuming 'j' is a defined variable? If so, you would need to make a
public statement. What happens is, if you dim and set j in this procedure and
then call insert spectrum, j's set data can be lost. Above all the subs in
your module, type something like 'Public j as variant'. Although make sure
that InsertSpectrum is in the same module, as sometimes even public dims wont
work across multiple modules.

 

[Dave Peterson]

Is this code in a worksheet module or in a General module?

If it's behind a worksheet, then the activesheet may not be the same as the
worksheet owning the code.

And those unqualified ranges (Cells(12+J,22) and cells(12+J,35) refer to the
worksheet owning that code--not the activesheet. In a general module, those
unqualified range references will refer to the activesheet.

This should work in either case:

with activesheet
.Range(.Cells(12 + j, 22), .Cells(12 + j, 35)).Value _
= .Range("V10:AI10").Value
end with

The dots in front of the .Range and .Cells mean that they belong to the object
in the previous With statement--in this case the activesheet.

Another way so you don't have to do the arithmetic:

with activesheet.range("v10:ai10")
.parent.cells(12+j,22).resize(.rows.count,.columns.count).value = .value
end with

The parent of the range("v10:ai10") is the activesheet. And then you just use
one cell and resize it to match the size of the range from which you're taking
the values.

 

[Guest]

Thanks Dave, it worked.

I would like to gain some insight about what was wrong and how your code
helped.

First, both procedures--InsertSpectrum and the one containing this code--are
in separate modules in the same project, listed in the project explorer under
"Modules," not under "Microsoft Excel Objects." Would this not make them
"general modules," not "worksheet modules." If so, then the unqualified
range references [i.e., Cells(...)] should have referred to the active sheet;
why didn't they work? i.e., Why was it necessary to explicity specify
ActiveSheet for Cells(...)?

Second, I applied what I thought was the same as your approach in another
line later in the code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

but this gave me a "Run-time error '1004': Application-defined or
object-defined error". What's wrong? (strExpt is a string variable. In
this case, i=1; cell A9 indeed contained a string.)

 

[Dave Peterson]

Without any knowledge of what InsertSpectrum does, I would have guessed that it
would work fine. Maybe something else is happening in that macro???

But I think it's better to qualify the ranges no matter what module you're in.
I use this lots of times:

Dim ActWks as worksheet
dim myRng as range
set ActWks = activesheet

with actwks
set myRng = ....
end with

I get the nice intellisense in the VBE and don't have to ever worry about what
module I'm in.

====

This code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

is the same as:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1).value).Value
End With

which means that it's looking at .cells(i+8,1) and returning that value and then
..Range() will use that value. And for that line to work, that value has to look
like a range (A1, x9:z77 or a range name).

I would bet that you wanted:

With ActiveSheet
strExpt = .Cells(i + 8, 1).Value
or
strExpt = .Range("A" & i+8).Value
End With

Thanks Dave, it worked.

I would like to gain some insight about what was wrong and how your code
helped.

First, both procedures--InsertSpectrum and the one containing this code--are
in separate modules in the same project, listed in the project explorer under
"Modules," not under "Microsoft Excel Objects." Would this not make them
"general modules," not "worksheet modules." If so, then the unqualified
range references [i.e., Cells(...)] should have referred to the active sheet;
why didn't they work? i.e., Why was it necessary to explicity specify
ActiveSheet for Cells(...)?

Second, I applied what I thought was the same as your approach in another
line later in the code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

but this gave me a "Run-time error '1004': Application-defined or
object-defined error". What's wrong? (strExpt is a string variable. In
this case, i=1; cell A9 indeed contained a string.)

Is this code in a worksheet module or in a General module?

If it's behind a worksheet, then the activesheet may not be the same as the
worksheet owning the code.

And those unqualified ranges (Cells(12+J,22) and cells(12+J,35) refer to the
worksheet owning that code--not the activesheet. In a general module, those
unqualified range references will refer to the activesheet.

This should work in either case:

with activesheet
.Range(.Cells(12 + j, 22), .Cells(12 + j, 35)).Value _
= .Range("V10:AI10").Value
end with

The dots in front of the .Range and .Cells mean that they belong to the object
in the previous With statement--in this case the activesheet.

Another way so you don't have to do the arithmetic:

with activesheet.range("v10:ai10")
.parent.cells(12+j,22).resize(.rows.count,.columns.count).value = .value
end with

The parent of the range("v10:ai10") is the activesheet. And then you just use
one cell and resize it to match the size of the range from which you're taking
the values.

 

[Guest]

Thanks again Dave.

Why does VBA need the form .Range(.Cells()).Value when setting values of
another range of cells, but .Cells().Value when setting the value of a VBA
variable?

Without any knowledge of what InsertSpectrum does, I would have guessed that it
would work fine. Maybe something else is happening in that macro???

But I think it's better to qualify the ranges no matter what module you're in.
I use this lots of times:

Dim ActWks as worksheet
dim myRng as range
set ActWks = activesheet

with actwks
set myRng = ....
end with

I get the nice intellisense in the VBE and don't have to ever worry about what
module I'm in.

====

This code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

is the same as:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1).value).Value
End With

which means that it's looking at .cells(i+8,1) and returning that value and then
..Range() will use that value. And for that line to work, that value has to look
like a range (A1, x9:z77 or a range name).

I would bet that you wanted:

With ActiveSheet
strExpt = .Cells(i + 8, 1).Value
or
strExpt = .Range("A" & i+8).Value
End With

Thanks Dave, it worked.

I would like to gain some insight about what was wrong and how your code
helped.

First, both procedures--InsertSpectrum and the one containing this code--are
in separate modules in the same project, listed in the project explorer under
"Modules," not under "Microsoft Excel Objects." Would this not make them
"general modules," not "worksheet modules." If so, then the unqualified
range references [i.e., Cells(...)] should have referred to the active sheet;
why didn't they work? i.e., Why was it necessary to explicity specify
ActiveSheet for Cells(...)?

Second, I applied what I thought was the same as your approach in another
line later in the code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

but this gave me a "Run-time error '1004': Application-defined or
object-defined error". What's wrong? (strExpt is a string variable. In
this case, i=1; cell A9 indeed contained a string.)

Is this code in a worksheet module or in a General module?

If it's behind a worksheet, then the activesheet may not be the same as the
worksheet owning the code.

And those unqualified ranges (Cells(12+J,22) and cells(12+J,35) refer to the
worksheet owning that code--not the activesheet. In a general module, those
unqualified range references will refer to the activesheet.

This should work in either case:

with activesheet
.Range(.Cells(12 + j, 22), .Cells(12 + j, 35)).Value _
= .Range("V10:AI10").Value
end with

The dots in front of the .Range and .Cells mean that they belong to the object
in the previous With statement--in this case the activesheet.

Another way so you don't have to do the arithmetic:

with activesheet.range("v10:ai10")
.parent.cells(12+j,22).resize(.rows.count,.columns.count).value = .value
end with

The parent of the range("v10:ai10") is the activesheet. And then you just use
one cell and resize it to match the size of the range from which you're taking
the values.



hmm wrote:

Here is a segment of my code:

InsertSpectrum
ActiveSheet.Range(Cells(12 + j, 22), Cells(12 + j, 35)).Value = _
ActiveSheet.Range("V10:AI10").Value

where InsertSpectrum is another procedure in the same project.

The 2nd line does not execute; instead, I get an error "Run-time error
'1004': Method 'Cells' of object '_Global' failed". If I remove the call to
InsertSpectrum, the next line executes properly.

What can I check to correct the problem, so that the second line will run
after the procedure call?

 

[Dave Peterson]

VBA is smart enough to know the difference between:

set myrng = .range(.cells(x,y),.cells(z,w))
and
set myrng = .range(.cells(x,y))

Thanks again Dave.

Why does VBA need the form .Range(.Cells()).Value when setting values of
another range of cells, but .Cells().Value when setting the value of a VBA
variable?

Without any knowledge of what InsertSpectrum does, I would have guessed that it
would work fine. Maybe something else is happening in that macro???

But I think it's better to qualify the ranges no matter what module you're in.
I use this lots of times:

Dim ActWks as worksheet
dim myRng as range
set ActWks = activesheet

with actwks
set myRng = ....
end with

I get the nice intellisense in the VBE and don't have to ever worry about what
module I'm in.

====

This code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

is the same as:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1).value).Value
End With

which means that it's looking at .cells(i+8,1) and returning that value and then
..Range() will use that value. And for that line to work, that value has to look
like a range (A1, x9:z77 or a range name).

I would bet that you wanted:

With ActiveSheet
strExpt = .Cells(i + 8, 1).Value
or
strExpt = .Range("A" & i+8).Value
End With

Thanks Dave, it worked.

I would like to gain some insight about what was wrong and how your code
helped.

First, both procedures--InsertSpectrum and the one containing this code--are
in separate modules in the same project, listed in the project explorer under
"Modules," not under "Microsoft Excel Objects." Would this not make them
"general modules," not "worksheet modules." If so, then the unqualified
range references [i.e., Cells(...)] should have referred to the active sheet;
why didn't they work? i.e., Why was it necessary to explicity specify
ActiveSheet for Cells(...)?

Second, I applied what I thought was the same as your approach in another
line later in the code:

With ActiveSheet
strExpt = .Range(.Cells(i + 8, 1)).Value
End With

but this gave me a "Run-time error '1004': Application-defined or
object-defined error". What's wrong? (strExpt is a string variable. In
this case, i=1; cell A9 indeed contained a string.)

:

Is this code in a worksheet module or in a General module?

If it's behind a worksheet, then the activesheet may not be the same as the
worksheet owning the code.

And those unqualified ranges (Cells(12+J,22) and cells(12+J,35) refer to the
worksheet owning that code--not the activesheet. In a general module, those
unqualified range references will refer to the activesheet.

This should work in either case:

with activesheet
.Range(.Cells(12 + j, 22), .Cells(12 + j, 35)).Value _
= .Range("V10:AI10").Value
end with

The dots in front of the .Range and .Cells mean that they belong to the object
in the previous With statement--in this case the activesheet.

Another way so you don't have to do the arithmetic:

with activesheet.range("v10:ai10")
.parent.cells(12+j,22).resize(.rows.count,.columns.count).value = .value
end with

The parent of the range("v10:ai10") is the activesheet. And then you just use
one cell and resize it to match the size of the range from which you're taking
the values.



hmm wrote:

Here is a segment of my code:

InsertSpectrum
ActiveSheet.Range(Cells(12 + j, 22), Cells(12 + j, 35)).Value = _
ActiveSheet.Range("V10:AI10").Value

where InsertSpectrum is another procedure in the same project.

The 2nd line does not execute; instead, I get an error "Run-time error
'1004': Method 'Cells' of object '_Global' failed". If I remove the call to
InsertSpectrum, the next line executes properly.

What can I check to correct the problem, so that the second line will run
after the procedure call?