Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with accessing names across sheets
Hi-
Having a problem with accessing the "alpha1" name below (defined in sheet "Data") while running this simple macro in sheet "Calc" - excel gives me a runtime 1004 error: (fyi - never used VB before this, so that may be the problem ;) Essentially, there is no problem accessing both "step_inp" or "stat1" since the macro is run from the "Calc" worksheet, but it breaks on the line marked with the ** - how do I fix this? Begin Code: ----------------- Sub Permute() Dim Step As Double Dim Inpt As Range Dim Loc As Range Sheets("Calc").Range("step_inp").Select Step = ActiveCell.Value Set Inpt = Sheets("Data").Range("alpha1") Set Loc = Sheets("Calc").Range("stat1") **Inpt.Select** ActiveCell.Value = -1 * Step For i = 0 To Round(80 / (Step * 100)) Inpt.Select ActiveCell.Value = ActiveCell.Value + Step Loc.Select Selection.Copy Loc.Offset(i + 1, 0).Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next i End Sub ________ End Code Any help is much appreciated. Thanks, -Alex |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with accessing names across sheets
I would assign an integer to be the step value. "Step" is an Excel VBA/VB
Type (word) and a conflict may occur. dim intStep as integer intStep =activecell.value Assign an a variant to: Round(80 / (Step * 100)) dim varRounder varRounder= Round(80 / (Step * 100)) Other people may help with the rest .............................................. " wrote: Hi- Having a problem with accessing the "alpha1" name below (defined in sheet "Data") while running this simple macro in sheet "Calc" - excel gives me a runtime 1004 error: (fyi - never used VB before this, so that may be the problem ;) Essentially, there is no problem accessing both "step_inp" or "stat1" since the macro is run from the "Calc" worksheet, but it breaks on the line marked with the ** - how do I fix this? Begin Code: ----------------- Sub Permute() Dim Step As Double Dim Inpt As Range Dim Loc As Range Sheets("Calc").Range("step_inp").Select Step = ActiveCell.Value Set Inpt = Sheets("Data").Range("alpha1") Set Loc = Sheets("Calc").Range("stat1") **Inpt.Select** ActiveCell.Value = -1 * Step For i = 0 To Round(80 / (Step * 100)) Inpt.Select ActiveCell.Value = ActiveCell.Value + Step Loc.Select Selection.Copy Loc.Offset(i + 1, 0).Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Next i End Sub ________ End Code Any help is much appreciated. Thanks, -Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem using Range Names in VLOOKUP | Excel Worksheet Functions | |||
how do i set up a list of names on a sheet frm various sheets in e | Excel Discussion (Misc queries) | |||
Problem - When couples have different last names | Excel Discussion (Misc queries) | |||
MS Dialog 5.0 sheets - Accessing data elements of objects | Excel Worksheet Functions | |||
Problem accessing Oracle Public Synonyms from Microsoft Excel 2002 | Excel Discussion (Misc queries) |