Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
exceluserforeman
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
problem using Range Names in VLOOKUP Dee Excel Worksheet Functions 6 February 28th 06 07:57 PM
how do i set up a list of names on a sheet frm various sheets in e mcvities_69 Excel Discussion (Misc queries) 1 January 27th 06 03:51 AM
Problem - When couples have different last names lburg801 Excel Discussion (Misc queries) 7 October 31st 05 02:47 PM
MS Dialog 5.0 sheets - Accessing data elements of objects Malriem Excel Worksheet Functions 2 June 4th 05 04:46 PM
Problem accessing Oracle Public Synonyms from Microsoft Excel 2002 Sandeep Excel Discussion (Misc queries) 1 January 5th 05 10:31 AM


All times are GMT +1. The time now is 06:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"