Emoncada
05-04-2007, 02:14 PM
I have this code
'dim the myList variable outside of the subs so it is a public variable.
Dim myList As Variant
'------------------------------
Sub Button3600()
myList = "HP LASERJET 3600N,10FT. USB PRINTER CABLE"
Call Bundle_Main
End Sub
'------------------------------
Sub Button4250()
myList = "HP LASERJET 4250TN,14FT. PATCH CABLE"
Call Bundle_Main
End Sub
'------------------------------
Sub Button4700()
myList = "HP LASERJET 4700DTN,14FT. PATCH CABLE,HP 500 SHEET TRAY"
Call Bundle_Main
End Sub
'------------------------------
Sub Button2015()
myList = "HP LASERJET 2015,10FT. USB PRINTER CABLE"
Call Bundle_Main
End Sub
Sub Bundle_Main()
Dim r As Range, rngData As Range
Dim myNum As Long, i 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:C260")
Set r = rngData.Find("*", SearchDirection:=xlPrevious)
If r Is Nothing Then
Set r = rngData(1)
ElseIf r.Row >= 260 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 >= 256 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
I need this to work with a form I created.
Basically what this does is when a button is pressed it imported the list in it's appropriate location. I need to do the same only difference it's a form not cells it would go into Txtboxes.
Can I do that. Maybe there is a way to simplify this.
'dim the myList variable outside of the subs so it is a public variable.
Dim myList As Variant
'------------------------------
Sub Button3600()
myList = "HP LASERJET 3600N,10FT. USB PRINTER CABLE"
Call Bundle_Main
End Sub
'------------------------------
Sub Button4250()
myList = "HP LASERJET 4250TN,14FT. PATCH CABLE"
Call Bundle_Main
End Sub
'------------------------------
Sub Button4700()
myList = "HP LASERJET 4700DTN,14FT. PATCH CABLE,HP 500 SHEET TRAY"
Call Bundle_Main
End Sub
'------------------------------
Sub Button2015()
myList = "HP LASERJET 2015,10FT. USB PRINTER CABLE"
Call Bundle_Main
End Sub
Sub Bundle_Main()
Dim r As Range, rngData As Range
Dim myNum As Long, i 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:C260")
Set r = rngData.Find("*", SearchDirection:=xlPrevious)
If r Is Nothing Then
Set r = rngData(1)
ElseIf r.Row >= 260 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 >= 256 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
I need this to work with a form I created.
Basically what this does is when a button is pressed it imported the list in it's appropriate location. I need to do the same only difference it's a form not cells it would go into Txtboxes.
Can I do that. Maybe there is a way to simplify this.