VBA Replace Cell Value Function

[Makpo]

Hi,

This is like a puzzle for me. I am trying to replace a value in a cell with an update which will add up the old value to the new value. The cell in question is determined by a VLookUp so we cant tell what it will be. Everything else looks fine except this replacement.

This how far I have gone. Can anyone please help with this.

Private Sub cmdAdd_Click()

Dim ustock As Integer, pstock As Integer
Dim cboPro As Variant

With Worksheets("Sheet5").Range("TS")
pstock = Application.WorksheetFunction.VLookup(Me.cboPro.Value, Worksheets("Sheet5").Range("TS"), 2, False)
End With

ustock = pstock + Val(txtsto)

'I need to replace the value of pstock on my table with an updated stock value ustock which is an addition of the value from a textbox on this userform

End Sub

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdAdd_Click()<br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> Stock  <SPAN style="color:#00007F">As</SPAN> Range<br><br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Sheet5").Range("TS")<br>        <SPAN style="color:#00007F">Set</SPAN> Stock = .Find(What:=Me.cboPro.Value, _<br>                          LookIn:=xlValues, _<br>                          LookAt:=xlWhole, _<br>                          SearchOrder:=xlByRows, _<br>                          SearchDirection:=xlNext, _<br>                          MatchCase:=False)<br><br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Stock <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Stock.Offset(, 1).Value = Stock.Offset(, 1).Value + Val(txtsto)<br>        <SPAN style="color:#00007F">Else</SPAN><br>            MsgBox "No match found for " & Me.cboPro.Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Makpo]

Thanks, it worked!

Am grateful.