Problem with transfert between Sheets
 

Hi

I have every time the same error on the same instruction, the
instruction is in a module and the error is Run time error 1004
Application-defined or objet defined error

The fonction is :

Public Static Function ABC (Crit)

Dim i As Integer, j As Integer, k As Integer

k = Crit + 2

Instructions...

Problematic Instruction
Worksheets("PrioCrit").Range(Cells(7, "N"), Cells(44, "N")).Value
= Worksheets("Var").Range(Cells(57, k), Cells(94, k)).Value


End Function

What' s wrong ?

Thanks

Problem with transfert between Sheets
 

hi
try this.
Worksheets("PrioCrit").Range("N7",Range("N7").Rang e("N44")).Value = _
Worksheets("Var").Range("K57",Range("K57").Range(" K94")).Value

regards
FSt1

Hi

I have every time the same error on the same instruction, the
instruction is in a module and the error is Run time error 1004
Application-defined or objet defined error

The fonction is :

Public Static Function ABC (Crit)

Dim i As Integer, j As Integer, k As Integer

k = Crit + 2

Instructions...

Problematic Instruction
Worksheets("PrioCrit").Range(Cells(7, "N"), Cells(44, "N")).Value
= Worksheets("Var").Range(Cells(57, k), Cells(94, k)).Value


End Function

What' s wrong ?

Thanks

Problem with transfert between Sheets
 

The way your function is written, it is trying to return the Crit to the
function entry on the worksheet and it can't with the code you have posted.
For the function to work properly, the Crit has to equal something within the
code. I realize that you only posted a partial of the full function, but I
think you need to take another look at what you are trying to do.

"Gief Hell" wrote:

Hi

I have every time the same error on the same instruction, the
instruction is in a module and the error is Run time error 1004
Application-defined or objet defined error

The fonction is :

Public Static Function ABC (Crit)

Dim i As Integer, j As Integer, k As Integer

k = Crit + 2

Instructions...

Problematic Instruction
Worksheets("PrioCrit").Range(Cells(7, "N"), Cells(44, "N")).Value
= Worksheets("Var").Range(Cells(57, k), Cells(94, k)).Value


End Function

What' s wrong ?

Thanks

Problem with transfert between Sheets
 

You have the line:
k = Crit + 2

What value is being passed into the function for Crit? If it happens to be
a character string, then how do you add a number (2) to it?

Also, I see that you are using Cells(7, "N"), for example in your code.
Remember that this will refer to the ActiveSheet, not the "PrioCrit"
worksheet as you may be intending. You must use qualifiers in front of
Range and Cell properties, like so:

With Worksheets("PrioCrit")
.Range(.Cells(7,"N"),.Cells(44, "N")).Value = ...

End With

(The period in front of Cells means that it is an extension of the
Worksheets() portion of code in the With above.)

I would normally declare object variables and set them to the ranges first,
then transfer the value from one to the other. Single-step through the code
and check the locals window to verify that your ranges are set correctly.
Following code is untested:

Dim wsVar as Worksheet
Dim rngFrom as Range
Dim wsPrioCrit as Worksheet
Dim rngTo as Range

Set wsVar = Worksheets("Var")
With wsVar
Set rngFrom = .Range(.Cells(57, k), .Cells(94, k))
End With

Set wsPrioCrit = Worksheets("PrioCrit")
With wsPrioCrit
Set rngTo = .Range(.Cells(7, "N"), .Cells(44, "N"))
End With

rngTo.Value = rngFrom.Value

(As an aside: Normally in a function, ABC should be set to the value that
you want to return, regardless of whether you are calling the function from
a formula in a worksheet cell or from a command macro. Since this routine
has the "side-effect" of transferring data from one cell to a different
range of cells that are not in the call list, then it should probably be a
Sub, not a Function. Also, why is it declared Static?)

--
Regards,
Bill Renaud