Excel functions - values from input box inserted into free cells

Question

New to VBA and Excel, haven't programmed anything in years. Quick question. We're trying to make an Inventory list in Excel With a button calling an input Box where data can be inserted. The data from the input boxes should eb inserted into the first available row. There are 10 columns per row, and each row represents a new item in the Inventory. Here is my code, which gives me "Run-time error 424: Object required":

Dim iDate, iVessel, iOrder, iCourier, iWaybill, iSender, iPcs, iWeight, iRemark As Variant
Dim col As Integer
Public Function selectFirstBlankCell()
    Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String
    col = 2
    sourceCol = col
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

    While col < 11
        For currentRow = 1 To rowCount
            currentRowValue = Cells(currentRow, sourceCol).Value
            If IsEmpty(currentRowValue) Or currentRowValue = "" Then
                Cells(currentRow, sourceCol).Select
                If sourceCol = 2 Then
                    Cell.Value = iDate
                ElseIf sourceCol = 3 Then
                    Cell.Value = iVessel
                ElseIf sourceCol = 4 Then
                    Cell.Value = iOrder
                ElseIf sourceCol = 5 Then
                    Cell.Value = iCourier
                ElseIf sourceCol = 6 Then
                    Cell.Value = iWaybill
                ElseIf sourceCol = 7 Then
                    Cell.Value = iSender
                ElseIf sourceCol = 8 Then
                    Cell.Value = iPcs
                ElseIf sourceCol = 9 Then
                    Cell.Value = iWeight
                ElseIf sourceCol = 10 Then
                    Cell.Value = iRemark
                End If
            col = col + 1
            End If
        Next
    Wend
    End Function

    Public Sub newParcel_Click()
    Call selectFirstBlankCell
    iDate = InputBox("Tast inn dato, format dd.mm.yyy")
    iVessel = InputBox("Tast inn båtens navn")
    iOrder = InputBox("Tast inn eventuell P.O.")
    iCourier = InputBox("Tast inn courier")
    iWaybill = InputBox("Tast inn waybillnummer")
    iSender = InputBox("Tast inn avsender")
    iPcs = InputBox("Tast inn antall kolli")
    iWeight = InputBox("Tast inn vekt")
    iRemark = InputBox("Tast inn eventuelle anmerkninger")
    End Sub

I pasted your code into a VBA page, and upon first glance, it gets stuck on Cell.Value = iDate (or one of the other choices). iDate has not yet been initialized, because you have already called selectFirstBlankCell. There may be other problems, but that is the first one I encountered. Not quite sure what your intent is, so difficult to suggest a way to fix it yet. — Ric Gaudet, Mar 23 '16 at 14:31
I think instead of `Cell.Value =...` you meant to use `Selection.Value =...`. But you really should read [How to avoid using Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — Kyle, Mar 23 '16 at 14:33

score 0 · Answer 1 · answered Mar 23 '16 at 15:16

Cell isn't an object in vba. You should use range objects instead. For example, if you want to select a value from A1 cell in sheet1 you must reference it like Worksheets("sheet1").Range ("A1"). You could use a different aproach using two range objects, one for cells an one for range; for example to loop in certain cells in column A:

Dim lastrow As Integer
Dim eachcell as range
Dim total_range as range

lastrow = Worksheets("sheet name").Columns("A").Find("", Cells(Rows.Count, "A")).Row

set total_range = Worksheets("sheet name").Range("A1:A" & lastrow)

for each eachcell in total_range
   'do something
next eachcell

Excel functions - values from input box inserted into free cells

1 Answers1