Using user form to change cell value on worksheet

[gregork]

Hi,
I have a user form with the following code for looking up a cell value on my
worksheet:

TextBox2.Text = Application.VLookup(CDbl(ComboBox1.Text), _
Worksheets("Blending Details").Range("A2:Z500"), 2, False)

Now if I was to type in a different value in text box 2 how would I write a
code that would send the updated value back to the worksheet(("Blending
Details").Range("A2:Z500"), 2,)?


Regards
gregork

 

[Dave Peterson]

Untested, so watch for typos:


Dim res As Variant
Dim myRng As Range

Set myRng = Worksheets("blending details").Range("a2:A500")

res = Application.Match(CDbl(combobox1.Text), myRng, 0)

If IsError(res) Then
MsgBox "no match!"
Else
myRng(res).Offset(0, 1).Value = textbox2.text
end if

 

[Tom Ogilvy]

Dim rng as range, res as Variant, rng1 as Range
Set rng = Worksheets("Blending Details").Range("A2:A500")
res = Application.Match(cDbl(combobox1.Text),rng,0)
if not iserror(res) then
set rng1 = rng(res)
rng1.offset(0,1).Value = Textbox2.Text
Else
msgbox "No Match for " & Combobox1.Text
End if

 

[gregork]

Many thanks guys that was exactly what I was looking for.

Kind Regards
gregork