PDA

View Full Version : Change value of adjacent cell if field in form matches



dirtychinch
06-24-2014, 06:13 AM
Hi all,

I want to create a method of quickly updating a series of prices based on their product code. I have a form which pops up and asks for the product code and new price. This is the code I've written to do it, but no prices ever change.



Sub PriceChange()


Dim A As Integer, B As Variant, Cell As Range, TextBox1, TextBox2 As TextBox

For Each Cell In Range("ProductCodes")
If Cell.Value = UserForm1.TextBox1.Value Then
Cell.Offset(0, 1).Value = UserForm1.TextBox2
End If
Next

UserForm1.Hide
MsgBox ("The pricing update has been carried out.")


End Sub

Could anyone enlighten me?

Thanks,

Jonathon

Bob Phillips
06-24-2014, 08:15 AM
Try changing


For Each Cell In Range("ProductCodes")

to


For Each Cell In Worksheets("whatever-the_sheet_is_called").Range("ProductCodes")

and get rid of


, TextBox1, TextBox2 As TextBox

in the Dim statement

dirtychinch
06-24-2014, 08:25 AM
Hi xld, unfortunately those changes didn't do anything. I don't think that the IF statement actually acts at all :(

Edit: just tested it with some mock values in the macro (as opposed to in the form) and it does work, but it doesn't seem to be reading what's in the form.

Bob Phillips
06-25-2014, 02:44 AM
Can you post the workbook?

dirtychinch
06-25-2014, 02:54 AM
Not really, sorry! It would take me ages to strip it. I modified the macro so it didn't require a form, which I'm happy with now. So, not to worry!