user input column and then search row for other input

[rajm11]

Hello all,

Here is the problem I am trying to sort out.

As soon as workbook opens user is asked for an input from column C. The VBA script locates it, rthen user inputs number , which should be exactly agsinst the earlier input i.e.
if user has input Article12 , which is in column C5, the next input should be comapred to column D where the numbers of articles present .
next step based on user input for article number is to minus that number from column D ( if is it les than or equal to Number present) and then save sheets and stop user process any more/close

so far here is the the scipt I have

Private Sub Workbook_Open()
Dim Arnum As Variant
Dim Count As Variant
Dim Sheetname As String

Arnum = InputBox("Please input article number :", "Input")
Count = InputBox(" Please input number of articles wanted", "Input")
Sheetname = "list1"

Dim Cell As Variant

Set Cell = Sheets(Sheetname).Columns("C:C").Find(What:=Arnum, After:=Sheets(Sheetname).Range("C1"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not Cell Is Nothing Then

       Sheets(Sheetname).Range(Cell.Address).Interior.ColorIndex = 4
Else

    'Value Not found
    MsgBox "Wrong article number"
End If


Set Cell = Sheets(Sheetname).Columns("F:F").Find(What:=Count, After:=Sheets(Sheetname).Range("F1"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False)
If Not Cell Is Nothing Then

    'Value is found, Highlight Cell
    Sheets(Sheetname).Range(Cell.Address).Interior.ColorIndex = 5
Else

    'Value Not found
    MsgBox "This is more than what we have "
End If


End Sub

The code from second set cell is what am trying to improve upon.

 

[rajm11]

Okay afer deliberation I found out what i need

I need to have vlookup in code for the user input. I have now modified code a bit as follows, bu it throws me a error saying " Run time error Object required at vlookup line

Private Sub Workbook_Open()
Dim Arnum As Variant
Dim Count As Variant
Dim Sheetname As String


Arnum = InputBox("Please input article number :", "Input")
'Antal = InputBox(" Please input number of articles wanted", Input")
Sheetname = "list"


Dim Cell As Variant


Set Cell = Sheets(Sheetname).Columns("C:C").Find(What:=Arnum, After:=Sheets(Sheetname).Range("C1"), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not Cell Is Nothing Then

    'Value is found, Highlight Cell
    Sheets(Sheetname).Range(Cell.Address).Interior.ColorIndex = 4
Else

    'Value Not found
    MsgBox "Wrong article number"
End If


   count  = Application.VLookup(Arnum, listan.Range("C2:F300"), 3, False)
MsgBox "count for $ is $ " & Arnum & count 
    
End Sub

 

[rajm11]

Okay i improved a lot upon the code and tried to gt my output
but the DONOTCONTINUE TRUE condition wont fullfill. what gives does any body explain to me please ?

Private Sub Workbook_Open()

Dim Arnum As Variant

Dim Count As Integer

Dim Sheetname As String

Dim count As Integer

Dim Count1 As Integer

Dim Name As String





Arnum = InputBox("Please input Artikel number you are looking for :", "Input")

Sheetname = "list"

'Sheets(Sheetname).Columns("D:D").Select

Dim Cell As Variant



'Search in Column D | MatchCase True = case sensitive

Set Cell = Sheets(Sheetname).Columns("C:C").Find(What:=Arnum, After:=Sheets(Sheetname).Range("C1"), LookIn:=xlFormulas, _

LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _

MatchCase:=False, SearchFormat:=False)



If Not Cell Is Nothing Then



'Value is found, Highlight Cell

'Sheets(Sheetname).Range(Cell.Address).Interior.ColorIndex = 4

Cell.Select



Else

'Value Not found

MsgBox "Wrong article number"

DoNotContinue = True

End If



'MsgBox " Det finns & Count " & ActiveCell.Offset(0, 3).Value

Count = InputBox("Please input Countet  that will be taken out :", "Input")

'ActiveCell.Offset(0, 3).Value = count

If ActiveCell.Offset(0, 3).Value < Count Then



MsgBox " Cant take more than " & ActiveCell.Offset(0, 3).Value

DoNotContinue = True

End If



'If ActiveCell.Offset(0, 3).Value > Count Then



ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value - Count



MsgBox "DONE!!!"

'End If



ActiveCell.Offset(0, 7).Value = Date

Name = InputBox("Please input your name ""Input")

ActiveCell.Offset(0, 8).Value = Name



End Sub