Dispersing a value over multiple cells using a macro

[Chip Smith]

The macro I've written, basically adds shipping to a parts ordered. I've got
the user form to locae a particular order number and add the shipping
accordingly, however, it only adds to the first value located, if i have
multiple parts listed under the same order number, it doesn't find them.
heres the macro as it stands now.

Private Sub cmdAdd_Click()
Dim Order As String
Dim Index As Variant
Dim nextrow As Long
Dim myRng As Range

Order = Me.txtOrder.Value

' Only looking in column K, so only look in column k
Set myRng = Range("k:k")

nextrow = Range("k65536").Row + 1

Index = Application.Match(CLng(Order), myRng, 0)

If IsError(Index) Then
MsgBox "Not Found, check Order No. and try again"
Else
myRng(Index).Select

ActiveCell.Offset(0, -9).Value = ActiveCell.Offset(0, -9).Value +
(Me.txtShip.Value / Me.txtPart.Value)
End If

Me.txtOrder.Value = ""
Me.txtShip.Value = ""
Me.txtPart.Value = ""
Me.txtOrder.SetFocus

End Sub

 

[stevebriz]

chip ,
Can you tell me few things...

1/ As I read this am I right in assuming you are looking in column K
for the order number then you from this you know wehre txtship.value
and txtPart value are are?

is it something like col K order, col L txtship.value etc?
I might be able to help you if I knew this.

 

[Chip Smith]

yes i am searching in column k. i have a user form, where in the user puts in
the order number (txtorder.value), the number of parts (txtPart.value), and
shipping (txtShip.value). The macro then takes the shipping in USD and
divides it by the number of parts. After this, it is suppose to adjust the
price of the parts by adding the apporiate shipping. however when inputting
the order number, the macro only adjust the first part as opposed to the
others in the same order number below it.

i.e.

a b c d e f g h i j k
1 p $8.20 x x x x x x x x 234

2 r $9.00 x x x x x x x x 234

3 s $8.13 x x x x x x x x 234

4 t $4.11 x x x x x x x x 554

so the part in A1 is $8.20 on order number 234, the shipping for example
would add $1, which should also be added to the part in A2, and A3, however
it only does the part in A1...i don't know if that was confusing or
not...thanks!

 

[stevebriz]

try this out and see it works for you

Private Sub CommandButton1_Click()
Dim Order As String
Dim Index As Variant
Dim nextrow As Long
Order = txtorder.Value


With Worksheets(1).Range("K:K")
Set c = .Find(Order, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Cells(c.Row, 2).Value = Cells(c.Row, 2).Value +
(Me.txtShip.Value / Me.txtPart.Value)

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub

 

[Chip Smith]

thank you...that worked great!