user input column and then search row for other input

rajm11

New Member
Joined
Feb 18, 2016
Messages
22
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
VBA Code:
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

rajm11

New Member
Joined
Feb 18, 2016
Messages
22
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

VBA Code:
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

New Member
Joined
Feb 18, 2016
Messages
22
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 ?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,127
Messages
5,640,275
Members
417,133
Latest member
caaronh85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top