VBA InputBox to generate list used for VLookup

[phillipjpalmer1983]

Hello

I need some help with an input box.
The box is a simple user form with 2 fields, Job and Comment and a submit button.
When the user fills in the form and hits submit, the values are written to a sheet in columns, labelled Job and Comment.
It works fine, however, i want the form to first check if the job value already exists and if so, overwrite the comment with the new comment.
Logically, i think when the user submits, it should search based on the job.value and if it find it then overwrite the adjacent comment otherwise create a new job and comment row. I just cant work out how do this in VBA.
Help please.

Here is my code so far:

Private Sub Submit_Click()


Sheets("Comments").Select
Range("A2").Select


Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 0).Value = Me.Job.Value
ActiveCell.Offset(0, 1).Value = Me.Comments.Value


Me.Hide
Worksheets("WIP").Activate
End Sub

 

[bellman101]

Hi Phillip,

You need something like this:

If Evaluate("=COUNTIF(Comments!A:A," & Me.Job.Value) & ")") > 0 Then
    Range("A:A").find(Me.Job.Value).offset(0,1) = Me.Comments.Value
Else
    'Original Code Here'
End If

Also your original can be tidied as follows, negating the need for messy sheet and cells selections:

varROW = Sheets("Comments").Range("A" & rows.count).end(xlUp).row + 1
Sheets("Comments").Cells(varROW,1) = Me.Job.Value
Sheets("Comments").Cells(varROW,2) = Me.Comment.Value

So the final thing should be along these lines:

[COLOR=#333333]Private Sub Submit_Click()[/COLOR]

If Evaluate("=COUNTIF(Comments!A:A," & Me.Job.Value) & ")") > 0 Then
       Sheets("Comments").Range("A:A").find(Me.Job.Value).offset(0,1) = Me.Job.Comment
Else
       varROW = Sheets("Comments").Range("A" & rows.count).end(xlUp).row + 1
       Sheets("Comments").Cells(varROW,1) = Me.Job.Value
       Sheets("Comments").Cells(varROW,2) = Me.Comment.Value
End If

[COLOR=#333333]Me.Hide[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

EDIT: Due to no longer selecting 'Comments' sheet, just had to edit a line, where i'd missed the reference to the sheet.

Cheers
JB

 

[phillipjpalmer1983]

Thank you for the prompt reply.
It makes sense what you have done, but i am getting an error with the following line

If Evaluate("=COUNTIF(Comments!A:A," & Me.Job.Value) & ")") > 0 Then

 

[bellman101]

Yup, sorry - my bad. That was free-typed. Remove the bracket immediately after the word Value...

 

[phillipjpalmer1983]

Thank you for your great help with this.

 

[bellman101]

More than welcome sir,

Glad to help