Using enumeration with controls
Something like this should do the trick
Dim ctl as control
For I is 1 to 9
set ctl = me.controls("m"& I)
s1 = Me.ctl
set ctl = me.controls("d"& I)
s2 = Me.ctl
set ctl = me.controls("y"& I)
s1 = Me.ctl
MonthCol = Sheets(1).Range("B1000").End(xlUp).Row + 1
DayCol = Sheets(1).Range("C1000").End(xlUp).Row + 1
YearCol = Sheets(1).Range("D1000").End(xlUp).Row + 1
Cells(MonthCol, 2).Value = S1 'Me.M1.Value
Cells(DayCol, 3).Value = S2 'Me.D1.Value
Cells(YearCol, 4).Value = S3 'Me.Y1.Value
Loop
regards
KM
-----Original Message-----
I have some code here in a for next loop.
I want to build a string that uses the enumeration to
take values from 9 rows
of three comboboxes: M1, D1 and Y1 (Month, Day and Year)
Then I want it to pull from M2, D2 and Y2
Unfortunately its printing the actual
string "Me.Combobox1.Value" into the cell
ranges as opposed to the value in the comboboxes.
This is what the actual cells look like:
Me.M1.value Me.D1.value Me.Y1.value
Me.M2.value Me.D2.value Me.Y2.value
Me.M3.value Me.D3.value Me.Y3.value
Me.M4.value Me.D4.value Me.Y4.value
Anyone know a way around this? It could really come in
handy:
Dim i As Integer
Dim S1 As String, S2 As String, S3 As String
For i = 1 To 9
S1 = "Me.M" & i & ".value"
S2 = "Me.D" & i & ".value"
S3 = "Me.Y" & i & ".value"
MonthCol = Sheets(1).Range("B1000").End(xlUp).Row + 1
DayCol = Sheets(1).Range("C1000").End(xlUp).Row + 1
YearCol = Sheets(1).Range("D1000").End(xlUp).Row + 1
Cells(MonthCol, 2).Value = S1 'Me.M1.Value
Cells(DayCol, 3).Value = S2 'Me.D1.Value
Cells(YearCol, 4).Value = S3 'Me.Y1.Value
Next i
Jim Stiene
.
|