help with loop

[pooky]

Wanting the input box repeat four times, that part of code works. However, I want the new values to be entered on the next empty row of worksheet of column 5 or ("E").
Ex:
Enter Weight of cavity #1

Answer: 325

Enter Weight of cavity #2

Answer: 328

Enter Weight of cavity #3

Answer: 563

Enter Weight of cavity #4

Answer: 569

Each answer should advance down in column 5.

Sub GetWeight()
    i = 0
    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    For i = 1 To 4
        Weight = InputBox("Enter Weight of cavity #" & i)
        Cells(emptyRow, 5).Value = Weight.Value (Runtime error 424 occurs here) 
        If TieWeight = "" Then Exit Sub
    Next i
End Sub

Hope that make sense. Thanks to all for help.

[Imar]

Hi there,

You posted this in a general forum feedback category. To attract relevant viewers, consider posting this in one of the categories that matches your technology. Currently, it's hard to tell what this is about.

Cheers.

Imar

[shinpaj]

Hello. I don't see that you posted you question in another section, perhaps you got your answer, but you don't need the .value bit following user input in your code - that is the reason for the error. However, an unexpected result occurs because the value entered is overwritten each time the user enters the weight.

Reason: the code Cells(emptyRow, 5).Value = Weight references the variable emptyRow, the empty row in column A never changes because the user needs to go through 4 iterations, causing the input to be overwritten each input...and that is not what you want. The following fixes that (with offset(i,0)). You still have undfined TieWeight, the sub exits, so it is commented out.

Hope this helps:

Sub GetWeight()

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

i = 0

For i = 1 To 4
Weight = InputBox("Enter Weight of cavity #" & i)
Cells(emptyRow, 5).Offset(i, 0).Value = Weight
'If TieWeight = "" Then Exit Sub
Next i

End Sub

Regards
Sean

[cindyjohn]

thanks for your information