Need help with Application-defined or object-defined error!

r4ymond88

New Member
Joined
Jul 26, 2013
Messages
26
Hey guys. I am super new to this whole VBA thing and am trying to put together something for work. However I'm getting an application-defined or object-defined error message on the code that is highlighted is Set input_range = Selection.Offset(1, 0) and I am not sure of what to do at this point.

Sub guardar() 'seleccionar hoja de UI para copiar los datos
Worksheets("UI").Activate

'copiar los valores del UI a variables
Dim data_values(16) As Variant

data_values(0) = Range("analista").Value
data_values(1) = Range("prefijo").Value
data_values(2) = Range("num_awb").Value
data_values(3) = Range("flight_num").Value
data_values(4) = Range("routing").Value
data_values(5) = Range("final_destination").Value
data_values(6) = Range("date").Value
data_values(7) = Range("tons").Value
data_values(8) = Range("posiciones").Value
data_values(9) = Range("free").Value
data_values(10) = Range("allotment").Value
data_values(11) = Range("shsh").Value
data_values(12) = Range("cantidad_shsh").Value
data_values(13) = Range("conexion").Value
data_values(14) = Range("fecha_cnx").Value
data_values(15) = Range("comentarios").Value

'warning message for empty cells

Select Case True
Case Len(Range("Analista")) = 0
MsgBox "Missing Analista. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Prefijo")) = 0
MsgBox "Missing Prefijo. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Num_awb")) = 0
MsgBox "Missing AWB #. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Flight_num")) = 0
MsgBox "Missing Flight #. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Final_Destination")) = 0
MsgBox "Missing Destino Final. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Date")) = 0
MsgBox "Missing Fecha. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Tons")) = 0
MsgBox "Missing Tons. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Posiciones")) = 0
MsgBox "Missing Total de Posiciones. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Free")) = 0
MsgBox "Missing Free. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Allotment")) = 0
MsgBox "Missing Allotment. ", vbExclamation, "Process Canceled"
Exit Sub
Case Len(Range("Comentarios")) = 0
MsgBox "Missing Comentarios. ", vbExclamation, "Process Canceled"
Exit Sub
End Select

If Range("SHSH").Value = "Yes" Or Range("Conexion").Value = "Yes" Then
Select Case True
Case Len(Range("comentarios")) = 0
MsgBox "Por favor insertar Cantidad de SHSH o Fecha de CNX. ", vbExclamation, "Process Canceled"
Exit Sub
End Select

End If

'seleccionar hoja de datos
Worksheets("Confirmaciones").Activate

'seleccionar la celda donde vamos a pegar los valores
Range("table_start").Select
Selection.End(xlDown).Select
'create range for data values
Dim input_range As Range
'set the correct cell to the range
Set input_range = Selection.Offset(1, 0)


'copiar los datos en la hoja correspondiente
For i = 1 To 16
input_range.Offset(0, i - 1).Value = data_values(i - 1)
input_range.Offset(0, 2 - 1).Value = data_values(2 - 1)
Next i

'copiar los valores del Data No Show a variables


'regresar a la hoja inicial
Worksheets("UI").Activate

'limpiar las celdas
Range("Prefijo").ClearContents
Range("Num_AWB").ClearContents
Range("flight_num").ClearContents
Range("final_destination").ClearContents
Range("date").ClearContents
Range("tons").ClearContents
Range("posiciones").ClearContents
Range("free").Select
Range("allotment").ClearContents
Range("shsh").ClearContents
Range("cantidad_shsh").ClearContents
Range("conexion").ClearContents
Range("fecha_cnx").Select
Range("comentarios").Select
Selection.ClearContents

'regresar a celda
Range("Prefijo").Select




End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,961
Selection.End(xlDown).Select is selecting the last row on the worksheet.
Selection.Offset(1,0).Select is (trying to) select the row below the last row, hence the error.
 

r4ymond88

New Member
Joined
Jul 26, 2013
Messages
26
Never mind guys, it was actually a simple thing. My table_start was placed on the wrong range.

This macro will be saving data that is written down on a more friendly UI and then transferring it to a separate sheet.

Before this happens, I need it to somehow cross-check what is written on the UI with what is on the sheet, so that if I already have a certain flight # and date written down on the separate sheet, the UI macro won't run unless there's a comment filled in. Dunno if that makes any sense?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,536
Messages
5,625,390
Members
416,099
Latest member
sudarsan23

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