How do you find the row a value entered from inputbox is in, and then use it to define .select data range?


I am having trouble getting the following block of code to perform. I keep getting the 400 error and am very new to this. Any suggestions based on the code below would be highly appreciated. I have a feeling I may be using the incorrect form of block functions and subs. Again thank you for any input you may have! – Regards

Sub InputTestParameters1()
'   Ask user for the test start time and test length, return values
'   Use the returned values to calculate the End time.   
'   Using the start and end times, find them in data contained in column B, and return the Row Values.  
'   Use the Row Values to select data to be copied and pasted in other columns. 
    Dim test_length As Integer
    Dim Time_start As Integer
    Dim Time_end As Integer
    Dim row_start As Integer
    Dim test_time As Integer
    Dim row_end As Integer
'   Calculate the end time based on user input of start time and length of test.
    Time_start = InputBox("Enter the time in seconds from the" & Chr(10) _
    & "start of the test that you would" & Chr(10) _
    & "like to be the test start.", "Test Start")
    test_length = InputBox("Enter the Length of the test in hours", "Test Length")
    Time_end = (test_length * 3600) + Time_start

'   Find the row of the test time

    row_start = Application.WorksheetFunction.Match(Time_start, Range("B:B"), 0)
    row_end = Application.WorksheetFunction.Match(Time_end, B, 0)

'   Select the temperature data range and copy and paste it into the temps worksheet
    Sheets("Data Import").Range(Cells((row_start), 7), Cells((row_end), 11)).Select

'   Copy and paste the Temps Data from the selected Start and End times.

    Sheets("Temperatures UNIVERSAL").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-15
    row_num = 0
    test_time = 0
    row_end = 0
End Sub


Here is a quick rewrite of the lower half of your code. It is not clear which sheet your are using to find the match. Keep in mind that when you do not specify the sheet, it will use the active sheet, which may or may be intended. One other note, when searching rows it is best to use Long instead of integer. There are over 1 million rows and Integer caps out at 32,767.

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ThisWorkbook.Worksheets("Data Import")
Set ws2 = ThisWorkbook.Worksheets("Temperatures UNIVERSAL")

ws1.Range(ws1.Cells((row_start), 7), ws1.Cells((row_end), 11)).Copy
ws2.Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Answered By – Darrell H

Answer Checked By – David Marino (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.