Named ranges in VBA

[xyxex]

I am trying to write a loop that will populate a group of named worksheet cells.

my code is

Private Sub Rate_Change()
'update rates when one is changed
Dim i, Text As String, newtext As String, Text2 As String, NewText2 As String, h
Text = "txtrate"
Text2 = "data!rate"
For i = 2 To 12
h = i & "a"
newtext = Text & h
NewText2 = Text2 & h
Me.Controls(newtext).Value = Format(Range(NewText2).Value, "0.00%")
Next i
End Sub

I get errors on:
Me.Controls(newtext).Value = Format(Range(NewText2).Value, "0.00%") :Could not find specified object.

I assume that the syntax is wrong for range(newtext2) but cannot figure out how to phrase correctly.

Any help would be most appreciated

Thanks

 

[PHV]

You really have named ranges "rate2a", "rate3a", .., "rate12a" in a sheet named "dara" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xyxex]

yes but sheet is called data

 

[SkipVought]

 Text2 = "data!rate"

Is not a valid range name

Sheets("data").Range("rate2a")

is


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xyxex]

The problem seems to stem from VBA's requirement that named ranges must be referred to in quotes("").

I am guessing it is something to do with "DIM as" If I Dim as Range newtext2 retuns as =0, if i Dim as String newtext2 returns as "data!rate2a" but Range wont accept as valid.

FYI Ragne("data!rate2a")" is the same as Sheets("data").Range("rate2a"). If written long hand it has same result

 

[mscallisto]

Sheets("data").Range("rate2a") is so easier for the next person to read

 

[SkipVought]

Your problem is not with the ranges. I tested the RANGE syntax and it works!

Your problem is with the CONTROL object.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xyxex]

Any Suggestions? re Control object.

 

[SkipVought]

What are your control objects named?

What kind of control objes are they, Control Toolbox or Form?

Where are your control objects located?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I inserted a UserForm with one textbox named txtrate2a and steped thru the FIRST itteration of the for..next loop.

It loaded the textbox with the appropriate value from the worksheet.

So I suspect your textbox naming convention does not match what happens in the code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xyxex]

The Control is a textbox. each one is named "txtrate" + number eg ""txtrate2"

They are on a userform named "ufCovedet"

They are populated by referencing a shreadheet name "data" with Range named "rate" + number "a" eg "rate2a"

I want to populate via a loop "txtRate2" from data held in Range "data!rate2a" and repeat until this has cycles 12 times.

Hope this clarifies

Thanks

 

[SkipVought]

The Control is a textbox. each one is named "txtrate" + number eg ""txtrate2"

Sorry, you are flat wrong!

               h = i & "a"
               newtext = Text & h

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MiggyD]

Have to agree with Skip. Take a look at the original solution that was posted:

http://tek-tips.com/viewthread.cfm?qid=1503576&page=1

It was based on simply adding a number...not a number AND a letter. You may want to re-evaluate all of your code. You may, inadvertently, propagate an error to other sections.

--MiggyD


"...embrace the world in gray. Sometimes solutions aren't so simple. Sometimes goodbye's the only way." Shadow of the Day, Lincoln Park (2008)