Home |
Search |
Today's Posts |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Problem with copying sheets | Excel Discussion (Misc queries) | |||
Delete Sheets Problem | Excel Programming | |||
Problem copying sheets | Excel Programming | |||
Sheets.copy problem | Excel Programming | |||
Delete Sheets Problem | Excel Programming |