VBA searching for a specific value using imputbox

miisstina

New Member
Joined
Sep 27, 2014
Messages
10
Hi all,

I have just started learning Vba.

I am trying to create search for a value using an input box which goes through a range and determines the first value that exceeds the value chosen via input box. I have absolutely no idea where to begin. (Also I hope I make sense)

Any help to steer me in the correct direction would be greatly appreciated
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
try something like this

Code:
Sub test()
Dim cfind As Range, x, c As Range
Worksheets("sheet1").Activate
x = InputBox("type the number you want as input e.g. 2")
x = CLng(x)
Set cfind = Columns("a:a").Cells.Find(what:=x, lookat:=xlWhole)
'MsgBox cfind.Address
For Each c In Range(cfind.Offset(1, 0), cfind.End(xlDown))
MsgBox c.Address
If c.Value > x Then
MsgBox c.Address
Exit Sub
End If
Next c








End Sub
 

miisstina

New Member
Joined
Sep 27, 2014
Messages
10
try something like this

Code:
Sub test()
Dim cfind As Range, x, c As Range
Worksheets("sheet1").Activate
x = InputBox("type the number you want as input e.g. 2")
x = CLng(x)
Set cfind = Columns("a:a").Cells.Find(what:=x, lookat:=xlWhole)
'MsgBox cfind.Address
For Each c In Range(cfind.Offset(1, 0), cfind.End(xlDown))
MsgBox c.Address
If c.Value > x Then
MsgBox c.Address
Exit Sub
End If



Next c








End Sub
Thank you very much!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,377
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top