Finding empty cell

[bturner2]

Ok guys I am back. I have another problem I can?t quit get. This is what I am trying to do.



I want to select cell D7 on sheet1, see if this cell is empty, if it is empty I want it to place a formula in the cell. If it is not empty, I want it to drop down two rows and start this procedure over. And I want it to stop when it has entered the formula until the next time I need to add another formula. I also need it to start a new column of formulas, starting in cell I7 after it has entered a formula in cell D49.



So in short find the first empty cell in column D and enter a formula. Keep filling cells as needed over time untill a formula has been entered in D49 then skip to column I in cell 7 and start entering formulas there as they are needed.



Here is what I have so far. What am I doing wrong?

Sub cant_get_it_right()
MyInput = InputBox("Enter the employees name")
Do
    Range("d7").Select
    Start:
    If ActiveCell.CurrentRegion = ("D49") Then
        Range("i7").Select
    ElseIf ActiveCell = "" Then
        ActiveCell.Formula = "=IF(" & MyInput & "!G2>79,""Discharge"",IF(" & MyInput & "!G2>71,""Final""," & _
        "IF(" & MyInput & "!G2>63,""Second"",IF(" & MyInput & "!G2>55,""First""," & _
        "IF(" & MyInput & "!G2>31,""Informational"","""")))))"
        Exit Sub
    Else
        ActiveCell.Offset(2, 0).Select
        GoTo Start
    End If
Loop Until ActiveCell <> ""
End Sub

[mvidas]

Hi bturner2,

Try moving the

Range("d7").Select

Above the

Do

Each time your loop is repeated, it is looking at cell D7. If you move it outside of the loop, it will start with d7, but keep repeating as you desire.

Matt

[mvidas]

Actually, I found a couple other logic errors in the code (if your D7 is a formula that returns "" then it will enter the new formula over the old formula in that cell, changed the check to see if it was at D49, and added a goto start in the first if part). Here was what I ended up with:

Sub cant_get_it_right()
 MyInput = InputBox("Enter the employees name")
 Range("d7").Select
 Do
    Start:
    If ActiveCell.Address(0, 0) = "D49" Then
        Range("i7").Select
        GoTo Start
    ElseIf ActiveCell.Formula = "" Then
        ActiveCell.Formula = "=IF(" & MyInput & "!G2>79,""Discharge"",IF(" & MyInput & "!G2>71,""Final""," & _
        "IF(" & MyInput & "!G2>63,""Second"",IF(" & MyInput & "!G2>55,""First""," & _
        "IF(" & MyInput & "!G2>31,""Informational"","""")))))"
        Exit Sub
    Else
    ActiveCell.Offset(2, 0).Select
    GoTo Start
  End If
 Loop Until ActiveCell <> ""
End Sub

[TonyJollans]

Hi bturner2,

You have several problems here; let's take them one by one ..

Your code is all in a Do Loop - that's fine, but the first thing INSIDE the loop is selecting your initial cell so you will do that every time you go round the loop - you avoid this problem by having a label, "Start" which you go to instead of using the loop. So, the first thing to do is to move the Select of D7 outside the loop.

Now, your loop control only has effect when it is reached, which is after setting the active cell to I7 (all other code paths explicitly go somewhere else). If I7 is not blank you won't go round again so you won't ever fill the cell. To correct this, change the loop control to loop until a blank cell (instead of a non-blank cell) is found, and move the filling of the blank cell to AFTER the loop so that when the loop ends, having found a blank cell, the next thing you do is to fill it.

When you have done this you need to remove the Go To Start statement so that the iteration falls under the loop control condition instead of being unconditional. The Start label is now unused and can be removed.

Next, the check for D49. To check the Address you use the Address property which, by default returns an absolute address, so use .. If ActiveCell.Address = "$D$49" Then

You will now find that your starting cell, D7, is not checked - you use the Offset first time through the loop before checking the cell. This is best caught by putting the loop control at the start of the loop instead of the end.

The next problem is your check for the cell being empty. The default property of a cell is .Value which is not necessarily a string and you should either explicitly use .Text, or probably better, the IsEmpty function.

Your code will now look like this:

Sub cant_get_it_right()
MyInput = InputBox("Enter the employees name")
Range("d7").Select
Do Until IsEmpty(ActiveCell)
    If ActiveCell.Address = "$D$49" Then
        Range("i7").Select
    Else
        ActiveCell.Offset(2, 0).Select
    End If
Loop
ActiveCell.Formula = "=IF(" & MyInput & "!G2>79,""Discharge"",IF(" & MyInput & "!G2>71,""Final""," & _
"IF(" & MyInput & "!G2>63,""Second"",IF(" & MyInput & "!G2>55,""First""," & _
"IF(" & MyInput & "!G2>31,""Informational"","""")))))"
End Sub

.. which should work - but you'll probably find I've made a silly mistake somewhere

[bturner2]

Ok, that fixed my problems. Thanks for help. Man this is a bit hard to learn from just reading the help and looking at other peoples code. Thanks again for all the help.