Emoncada
02-21-2008, 02:05 PM
I have this script that worked great with form created on a spreadsheet. Now I would like to use the same script but for a userform.
Sub Button17()
Dim y, x As Integer
y = InputBox("How Many?")
If y = "" Then
Exit Sub
Else
x = 0
Do Until x = y
myList = "HP 17IN. TFT L1706"
Call Bundle_Main
x = x + 1
Loop
End If
End Sub
Sub Bundle_Main()
Dim r As Range, rngData As Range, t As Range, rngAmount As Range
Dim myNum As Long, i As Long, p As Long
'split the myList variable into an array--comma delimited
'and then you can loop through it.
myList = Split(myList, ",")
Set rngData = Range("C242:C331")
Set r = rngData.Find("*", SearchDirection:=xlPrevious)
If r Is Nothing Then
Set r = rngData(1)
ElseIf r.Row >= 331 Then
MsgBox "Not enough room"
Exit Sub
Else
Set r = r.Offset(1)
End If
myNum = WorksheetFunction.CountIf(rngData, myList(0))
If myNum > 0 Then
With rngData
Set r = .Find(what:=myList(0), After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(1)
End With
If r.Row >= 331 Then
MsgBox "Not enough room"
Exit Sub
End If
End If
For i = LBound(myList) To UBound(myList)
With r
.Offset(i, 0) = myList(i)
.Offset(i, 1) = myNum + 1
End With
Next i
r.Offset(, 1) = 1
End Sub
Basically what I think would need to be changed is the "Range"
Instead of cells ("C242:C331") I need "CmbBoxDesc1 - CmbBoxDesc54"
How can I make this work?
Sub Button17()
Dim y, x As Integer
y = InputBox("How Many?")
If y = "" Then
Exit Sub
Else
x = 0
Do Until x = y
myList = "HP 17IN. TFT L1706"
Call Bundle_Main
x = x + 1
Loop
End If
End Sub
Sub Bundle_Main()
Dim r As Range, rngData As Range, t As Range, rngAmount As Range
Dim myNum As Long, i As Long, p As Long
'split the myList variable into an array--comma delimited
'and then you can loop through it.
myList = Split(myList, ",")
Set rngData = Range("C242:C331")
Set r = rngData.Find("*", SearchDirection:=xlPrevious)
If r Is Nothing Then
Set r = rngData(1)
ElseIf r.Row >= 331 Then
MsgBox "Not enough room"
Exit Sub
Else
Set r = r.Offset(1)
End If
myNum = WorksheetFunction.CountIf(rngData, myList(0))
If myNum > 0 Then
With rngData
Set r = .Find(what:=myList(0), After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchDirection:=xlPrevious).Offset(1)
End With
If r.Row >= 331 Then
MsgBox "Not enough room"
Exit Sub
End If
End If
For i = LBound(myList) To UBound(myList)
With r
.Offset(i, 0) = myList(i)
.Offset(i, 1) = myNum + 1
End With
Next i
r.Offset(, 1) = 1
End Sub
Basically what I think would need to be changed is the "Range"
Instead of cells ("C242:C331") I need "CmbBoxDesc1 - CmbBoxDesc54"
How can I make this work?