VBA - Do While loop nested in a For Loop; Application-defined or object-defined errror

Question

Aim:

So I'm brand new to VBA and was working on a quick script which tries to record the hours of work done in a week.

It does this by copying the numbers of hours of work done per day for each day contained in row 27 from column B (Monday) to column G (Saturday) in the This Week sheet and pastes that infomation in the Hours sheet starting at column B and working along to column G.

I want to do this so that I can keep a permanent record of the hours done even after I've changed my This week sheet schedule for the next week.

So I've tried to write this script so that if it sees the row above full of infomation it tries the row below.

This would result in a permanent, chronological (every week starts a new row) record of how many hours done per day every week.

I'd run this at the end of the week before clearing the schedule for the next week.

Code:

Sub Hours()
Dim i As Integer
Dim j As Integer
Dim hoursdone As Long

j = 0
For i = 2 To 7
    hoursdone = Worksheets("This Week").Cells(27, i).Value
    Do While Worksheets("Hours").Cells(j, i).Value <> Empty
        j = j + 1
    Loop
    Worksheets("Hours").Cells(i, j) = hoursdone
    Next i

End Sub

The Error I keep getting:

Application-defined or object-defined error

When I click Debug it takes me to Line 9 and I've no idea what's gone wrong?

Thanks

PS: I want to eventually tie this thing to a button within my This week sheet to press, recording the data, allowing me to clear This week

Empty is a valid condition. Try ?activecell = empty in the Immediate window. — user4039065
– user4039065, Commented Apr 7, 2018 at 18:35
It's a bit odd that you max out your rows at integer but allow someone to work Long hours. — user4039065
– user4039065, Commented Apr 7, 2018 at 18:39

K.Dᴀᴠɪs · Accepted Answer · 2018-04-07 18:31:36Z

3

That would be because j is starting out at the number 0. You can not have Cells(0, 2), rows start at the number 1.

Change the j = 0 to j = 1, and also move that inside your For...Next statement.

For i = 2 To 7

    j = 1
    hoursdone = Worksheets("This Week").Cells(27, i).Value
    Do While Worksheets("Hours").Cells(j, i).Value <> Empty
        j = j + 1
    Loop
    Worksheets("Hours").Cells(i, j) = hoursdone
    Next i

End Sub

If you do not move the j = 1 inside your For statement, then the next iteration j will be what is was where it left off from when it was inside your Do...loop.

answered Apr 7, 2018 at 18:31

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - Do While loop nested in a For Loop; Application-defined or object-defined errror

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related