Excel VBA Object required error on VBA Script do duplicate lines while counter

Question

I'm trying to make a VBA script in an Excel document that loops through each line for an amount of time (column 3) and writes each of this line to the columns E, F, and G looking exactly like the line does in the Original input. In the end giving a total of 1050 lines with the example below.

The input looks like this, in Cell A, B and C, where column 1 is CompanyName, Column 2 is a CompanyNumber and column 3 is the amount.

CompanyName1 9910483 300 CompanyName2 9910477 250 CompanyName3 9910620 500

result:

CompanyName1 9910483 300 CompanyName1 9910483 300 CompanyName1 9910483 300 CompanyName1 9910483 300 CompanyName1 9910483 300 CompanyName1 9910483 300 etc to 300 lines, then next line item for amount times

The VBA code I made looks like this:

Sub DoWhileItemsAndAmount()

    Dim counter As Integer
    Dim rowCounter As Integer
    Dim column1 As String
    Dim column2 As String
    Dim column3 As Integer

    counter = 1
    rowCounter = 1


    Do While Cells(rowCounter, "C") Is Not Null

        column1 = Cells(rowCounter, "A").Value
        column2 = Cells(rowCounter, "B").Value
        column3 = Cells(rowCounter, "C").Value

        Do While counter < column3
            Cells(counter, "E").Value = column1
            Cells(counter, "F").Value = column2
            Cells(counter, "G").Value = column3
            counter = counter + 1
        Loop

        rowCounter = rowCounter + 1
    Loop
End Sub

This results in error "Object required" running the macro. Telling nothing else like error codes etc. I like to believe that the script is in the right direction.

I know it doesn't sound logical, but a program we use expects this output as input.

Many thanks in advance.

Edit: i changed the code to this and now it works

`Sub DoWhileItemsAndAmount()

' Declare vars
Dim counter As Integer
Dim rowTeller As Integer
Dim savePos As Integer
Dim column1 As String
Dim column2 As String
Dim column3 As Integer

' Set vars
counter = 0
savePos = 1
rowCounter = 1

' set errorcatch
On Error GoTo Errorcatch

' do while cell C is Not Empty
Do While ActiveWorkbook.Sheets("Blad1").Cells(rowCounter, "C").Value <> ""
    column1 = ActiveWorkbook.Sheets("Blad1").Cells(rowCounter, "A").Value
    column2 = ActiveWorkbook.Sheets("Blad1").Cells(rowCounter, "B").Value
    column3 = ActiveWorkbook.Sheets("Blad1").Cells(rowCounter, "C").Value
    counter = 0
    ' do while teller smaller then value in field
    Do While counter < column3

        ' add values to other fields incremental
        ActiveWorkbook.Sheets("Blad1").Cells(savePos, "E").Value = column1
        ActiveWorkbook.Sheets("Blad1").Cells(savePos, "F").Value = column2
        ActiveWorkbook.Sheets("Blad1").Cells(savePos, "G").Value = column3
        counter = counter + 1
        savePos = savePos + 1
    Loop
    rowCounter = rowCounter + 1
Loop

Exit Sub ' show msgbox on error Errorcatch: MsgBox Err.Number & ": " & Err.Description End Sub `

This seems to work, had to build in a savePos to have it remember the line its at. the answer of iDevlop fixed the issue of the script not running.

@iDevlop I am sure it's the: Do While Cells(rowCounter, "C") Is Not Null. Possibly the OP is thinking Cells() returns an Object rather than a Variant. Probably a Do While Not isEmpty(Cells(rowCounter, "C")) would do — user2140173
.@iDevlop: The line was not showing. @vba4all: This removed the previous error, thanks, now however i get a messagebox showing the error sign and the value 400, not even a messagebox title. Working with Excel 2007. — Ignotus

Patrick Honorez Patrick Honorez · Accepted Answer · 2014-12-09T15:22:28

0

votes

To test if a cell is empty, you can use Range(xx) <> "" or cell(x,y)<>""
See this SO answer.

Excel VBA Object required error on VBA Script do duplicate lines while counter

1 Answers