declaring varibles looping probelm
 

IM having trouble implementing the following code

Qty1 = txtQty1.Value
Qty2 = txtQty2.Value
....
....
Qty27 = txtQty27.Value
Qty28= txtQty28.Value

I have tried to use this code and it returns an error

For i = 1 To 28
Me.Controls("Qty" & i) = Me.Controls("txtQty" & i).Value
Next i

just wondering if there is a simplier way to do this

thank

--
Message posted from http://www.ExcelForum.com

declaring varibles looping probelm
 

recommend:

Dim Qty(1 To 28) 'an array
Dim i As Integer
For i = LBound(Qty) To UBound(Qty)
Qty(i) = Me.Controls("txtQty" & Cstr(i)).Value
Next i

'print 'em out
For i = LBound(Qty) To UBound(Qty)
Debug.Print Qty(i)
Next i

"l1075 " wrote in message
...
IM having trouble implementing the following code

Qty1 = txtQty1.Value
Qty2 = txtQty2.Value
...
...
Qty27 = txtQty27.Value
Qty28= txtQty28.Value

I have tried to use this code and it returns an error

For i = 1 To 28
Me.Controls("Qty" & i) = Me.Controls("txtQty" & i).Value
Next i

just wondering if there is a simplier way to do this

thanks


---
Message posted from http://www.ExcelForum.com/

declaring varibles looping probelm
 

l1075 wrote in message ...
IM having trouble implementing the following code

Qty1 = txtQty1.Value
Qty2 = txtQty2.Value
...
...
Qty27 = txtQty27.Value
Qty28= txtQty28.Value

I have tried to use this code and it returns an error

For i = 1 To 28
Me.Controls("Qty" & i) = Me.Controls("txtQty" & i).Value
Next i

just wondering if there is a simplier way to do this

thanks

Simpler on what level? Being a lazy so-and-so, I'd want to do the
synchronization in one line e.g.

m_ControlPairs.SyncAll

Something that simple needs some work up front.

You don't say what your pairs (Qty1 and txtQty1, etc) are. I'd infer
they are both controls on a userform (i.e. of type MSForm.Control) and
Qty1's default property must be able to accept txtQty1's Value
property (say, a Label and a TextBox respectively).

So I'd create a class, CControlPair, as follows:

Option Explicit

Private m_CtrlPrimary As MSForms.Control
Private m_CtrlSecondary As MSForms.Control

Public Function Init( _
ByVal PrimaryControl As MSForms.Control, _
ByVal SecondaryControl As MSForms.Control _
) As Boolean

Dim v As Variant

Set m_CtrlPrimary = PrimaryControl
Set m_CtrlSecondary = SecondaryControl

On Error Resume Next
' Test CtrlSecondary has a Value property
v = PrimaryControl.Value

' Test CtrlSecondary has default property that
' can accept a value
v = SecondaryControl

Init = CBool(Err.Number = 0)

End Function

Public Function Sync() As Boolean
m_CtrlSecondary = m_CtrlPrimary.Value
End Function

Now in my form's code module I'd have to create, initialize and
persist many objects of type CControlPair and call all their
individual Sync methods. But because I'm lazy I'd make a collection
class, CControlPairs, which would look after all the individual
objects for me. All I'd then have to do is create, initialize and
persist the collection class object, pass it all the pairs and call
one SyncAll method. My CControlPairs class would look like this:

Option Explicit

Private m_colControlPairs As Collection

Public Property Get Item(ByVal Index As Variant) As CControlPair
Set Item = m_colControlPairs.Item(Index)
End Property

Public Property Get NewEnum() As IUnknown
Set NewEnum = m_colControlPairs.[_NewEnum]
End Property

Friend Function Add( _
ByVal PrimaryControl As MSForms.Control, _
ByVal SecondaryControl As MSForms.Control _
) As CControlPair
Dim oControlPair As CControlPair
Set oControlPair = New CControlPair
m_colControlPairs.Add oControlPair, PrimaryControl.Name
oControlPair.Init PrimaryControl, SecondaryControl
Set Add = oControlPair
End Function

Friend Property Get Count() As Long
Count = m_colControlPairs.Count
End Property

Friend Function Remove(ByVal Index As Variant) As Boolean
On Error Resume Next
m_colControlPairs.Remove Index
Remove = CBool(Err.Number = 0)
On Error GoTo 0
End Function

Private Sub Class_Initialize()
Set m_colControlPairs = New Collection
End Sub

Private Sub Class_Terminate()
Set m_colControlPairs = Nothing
End Sub

Public Function SyncAll() As Boolean
Dim oControlPair As CControlPair
For Each oControlPair In m_colControlPairs
oControlPair.Sync
Next
End Function

The code in my form's code module would look something like this:

Option Explicit

Dim m_ControlPairs As CControlPairs

Private Sub UserForm_Activate()
Set m_ControlPairs = New CControlPairs
With m_ControlPairs
.Add TextBox1, Label1
.Add TextBox2, Label2
End With
End Sub

Private Sub btnSyncAll_Click()
m_ControlPairs.SyncAll
End Sub

But I'm too lazy to do any of that of course.

--

declaring varibles looping probelm
 

I don't need to go into that much depth to simplify my code. I jus
can't get a for loop to work for this code below.

shtInvoice.Range("Qty1").Value = txtQty1.Value
shtInvoice.Range("Qty2").Value = txtQty2.Value
.....
.....
shtInvoice.Range("Qty34").Value = txtQty34.Value
shtInvoice.Range("Qty35").Value = txtQty35.Value

i have tried

For i = 1 to 35
Me.Controls("txtQty" & Cstr(i)).Value = Me.Controls("txtQty"
Cstr(i)).Value
Next i

with shtInvoice.Range in front of the Me.Controls

I don't know where to put the shtInvoice.Range at thoug

--
Message posted from http://www.ExcelForum.com

declaring varibles looping probelm
 

l1075 wrote in message ...

I don't need to go into that much depth to simplify my code.

This laziness is catching, isn't it?

I just can't get a for loop to work for this code <snip.

It works for me but then I expect that of code written by the esteemed
Tom Ogilvy:

http://groups.google.com/groups?selm....supernews.com

You should've attached to the original thread, stating what you new
requirements are.

with shtInvoice.Range in front of the Me.Controls

That doesn't adequately describe your requirements. Are your controls
now on a worksheet? If so, there is no Controls collection for a
worksheet so you have to create your own collection... oops, I forgot,
you don't like writing code to simplify code!

--