Can't get code to work until the third time it is run? No idea why!

[robertsfd]

I have a spreadsheet with several columns. Half the columns are formulas based on the other half of the columns that contain values input by the user. For the user input cells, I have a macro that requests these input values with an InputBox and puts them in the cell, moves a few columns over, requests another input and places it in the cell, and so on. The next time the macro runs, it goes to the next new row and does the same thing.

The first time I run the macro (i.e., the first time the user inputs any information), it runs and asks for the first input and puts it in the cell but goes no further. If I run it again, it moves down a row as it should and asks for the first input, inputs the value supplied, and stops. The third time on, everything works well - it goes through the whole code and works as it should.

Any ideas where I have gone wrong? Why it works the third time on but not the first two? Thanks in advance, Doug

Sub InputDetail()
'move to beginning of worksheet values
    Range("B8").Select
'go to end of range
    Selection.End(xlDown).Select
'move down one more row
    ActiveCell.Offset(rowOffset:=1).Activate
'get user value and input to current cell
    ActiveCell.FormulaR1C1 = InputBox("Input your fill date")
'move to right four columns
    ActiveCell.Offset(columnOffset:=4).Activate
'get user value and input to current cell
    ActiveCell.FormulaR1C1 = InputBox("Input your ending mileage")
'move to right four columns
    ActiveCell.Offset(columnOffset:=4).Activate
'get user value and input to current cell
    ActiveCell.FormulaR1C1 = InputBox("Input the number of gallons bought")
'move to right four columns
    ActiveCell.Offset(columnOffset:=4).Activate
'get user value and input to current cell
    ActiveCell.FormulaR1C1 = InputBox("Input the price per gallon you bought")
'display ending message
    MsgBox ("Your 'CELL REF' was 'CELL REF'")
'move to beginning of worksheet
    Range("B2").Select
'save worksheet
    ActiveWorkbook.Save
End Sub

 

[robertsfd]

Just to clarify something mentioned above:

It isn't that the macro fails to work the first two times each time the worksheet is open. Rather it doesn't work for the first two rows of the spreadsheet - once the first two rows are filled in, it works as intended.

 

[GlennUK]

Your logic may need adjusting. The start of your code selects cell B8 and then does the equivalent of Ctrl-DownArrow .... what if there are no entries in your sheet from B8 to B65536? You wouldn't want the resulting selection to be the last row of the sheet would you?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[robertsfd]

Thanks for the thought Glenn. I should have been more clear - there are column headings in row 9, thus the first of the code just gets past those (and any other rows that might already be filled up) to add data to the first available row.

The problem has been solved. Apparently my problem was an issue with Excel97 and a function I had created and was using in the spreadsheet. The above works fine in the latest version of Excel where the function works. To get it to work with Excel97, I had to provide a workaround, but it was nothing to do with the code (although I realize the code could be much more efficient).

Thanks to any who thought about this.