0

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.


    'Range("G3:K68").Select
    Selection.Copy
    Sheets("Temperatures UNIVERSAL").Select
    Range("C7").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
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
ItryandItry
  • 15
  • 3
  • 1
    On your row_end= line you are searching "B". That is not a valid range. – Darrell H Apr 21 '22 at 15:31
  • Thanks Darrell! I can't believe I missed that. – ItryandItry Apr 21 '22 at 15:40
  • 1
    You should also look into avoiding "Select" and "Selection". Also note that when you declare your range, you also need to specify the sheet that the cells belong to, i.e., "Sheets("Data Import").Range(Sheets("Data Import").Cells...." – Darrell H Apr 21 '22 at 15:44
  • Thank you so much. I can see how specifying the sheet will avoid it pulling from the wrong sheet. I will definitely look into other options outside of select. I honestly wasn't sure there was any. I really appreciate your help! I was able to get it to work and can now work on improving it. – ItryandItry Apr 21 '22 at 16:03

1 Answers1

0

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
Darrell H
  • 1,876
  • 1
  • 9
  • 14