I am trying to create a form for easy input. Since a lot of the input is
fairly similar, i prefer using a loop. the problem is inputting a change of
string so i can actually achive data from the text boxes:

Private Sub add_day_Click()

Dim r, c As Integer
Dim SOLD As Integer
Dim Cash, Credit, Check As Currency
Dim ws As Worksheet
Dim Mall As String
Dim day As Integer
Dim days As String
Dim Response
Dim Product


Mall = ""
day = 0
If Me.txt_mall.Value < "" Then Mall = Me.txt_mall.Value
If Me.txt_day.Value < "" Then day = Me.txt_day.Value
If Mall < "" And day < 0 Then
Select Case day
Case 1 To 7
days = " 1-7"
Case 8 To 14
days = " 8-14"
Case 15 To 21
days = " 15-21"
Case 22 To 28
days = " 22-28"
Case 29 To 31
days = " 29-31"
End Select

Mall = Mall + days

Set ws = Worksheets(Mall)


'copy the data to the database
For j = 1 To 3
For i = 2 To 74 Step 6
Select Case i
Case 2
Product = Worksheets("Intro").Cells(11, 1).Value
Case 8
Product = Worksheets("Intro").Cells(12, 1).Value
Case 14
Product = Worksheets("Intro").Cells(13, 1).Value
Case 20
Product = Worksheets("Intro").Cells(14, 1).Value
Case 26
Product = Worksheets("Intro").Cells(15, 1).Value
Case 32
Product = Worksheets("Intro").Cells(16, 1).Value
Case 38
Product = Worksheets("Intro").Cells(17, 1).Value
Case 44
Product = Worksheets("Intro").Cells(18, 1).Value
Case 50
Product = Worksheets("Intro").Cells(19, 1).Value
Case 56
Product = Worksheets("Intro").Cells(20, 1).Value
Case 62
Product = Worksheets("Intro").Cells(21, 1).Value
Case 68
Product = Worksheets("Intro").Cells(22, 1).Value
Case 74
Product = Worksheets("Intro").Cells(23, 1).Value
End Select

Select Case j
Case 1
Product = Product + "_add"
ws.Cells(i + j, c).Value = Me.Product.Value <---- This is
where i get the error. It will not allow me to use "Product" since it's not
one of the text boxes

Me.Product.ClearContents
Case 2
Product = Product + "_sold"
ws.Cells(i + j, c).Value = Me.Product.Value
Me.Product.ClearContents
End If
Case 3
' Product = Product + "_damage"
' If Not IsEmpty(Me.Product) Then
' ws.Cells(i + j, c).Value = Me.Product.Value
' Me.Product.ClearContents
' End If
End Select
Next i
Next j
Else
Response = MsgBox("You Did Not Enter The Mall / Date, Please Enter
Again", vbExclamation, "Missing Information")
End If
End Sub

Help Thanks

Product is just your variable, correct? Try removing the me keyword
that prefaces it.

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

Jared wrote:
I am trying to create a form for easy input. Since a lot of the input is
fairly similar, i prefer using a loop. the problem is inputting a change of
string so i can actually achive data from the text boxes:

Private Sub add_day_Click()

Dim r, c As Integer
Dim SOLD As Integer
Dim Cash, Credit, Check As Currency
Dim ws As Worksheet
Dim Mall As String
Dim day As Integer
Dim days As String
Dim Response
Dim Product


Mall = ""
day = 0
If Me.txt_mall.Value < "" Then Mall = Me.txt_mall.Value
If Me.txt_day.Value < "" Then day = Me.txt_day.Value
If Mall < "" And day < 0 Then
Select Case day
Case 1 To 7
days = " 1-7"
Case 8 To 14
days = " 8-14"
Case 15 To 21
days = " 15-21"
Case 22 To 28
days = " 22-28"
Case 29 To 31
days = " 29-31"
End Select

Mall = Mall + days

Set ws = Worksheets(Mall)


'copy the data to the database
For j = 1 To 3
For i = 2 To 74 Step 6
Select Case i
Case 2
Product = Worksheets("Intro").Cells(11, 1).Value
Case 8
Product = Worksheets("Intro").Cells(12, 1).Value
Case 14
Product = Worksheets("Intro").Cells(13, 1).Value
Case 20
Product = Worksheets("Intro").Cells(14, 1).Value
Case 26
Product = Worksheets("Intro").Cells(15, 1).Value
Case 32
Product = Worksheets("Intro").Cells(16, 1).Value
Case 38
Product = Worksheets("Intro").Cells(17, 1).Value
Case 44
Product = Worksheets("Intro").Cells(18, 1).Value
Case 50
Product = Worksheets("Intro").Cells(19, 1).Value
Case 56
Product = Worksheets("Intro").Cells(20, 1).Value
Case 62
Product = Worksheets("Intro").Cells(21, 1).Value
Case 68
Product = Worksheets("Intro").Cells(22, 1).Value
Case 74
Product = Worksheets("Intro").Cells(23, 1).Value
End Select

Select Case j
Case 1
Product = Product + "_add"
ws.Cells(i + j, c).Value = Me.Product.Value <---- This is
where i get the error. It will not allow me to use "Product" since it's not
one of the text boxes

Me.Product.ClearContents
Case 2
Product = Product + "_sold"
ws.Cells(i + j, c).Value = Me.Product.Value
Me.Product.ClearContents
End If
Case 3
' Product = Product + "_damage"
' If Not IsEmpty(Me.Product) Then
' ws.Cells(i + j, c).Value = Me.Product.Value
' Me.Product.ClearContents
' End If
End Select
Next i
Next j
Else
Response = MsgBox("You Did Not Enter The Mall / Date, Please Enter
Again", vbExclamation, "Missing Information")
End If
End Sub

Help Thanks