Stop routine when blank is found

[Subbie]

I have completed my first project with VBA and grateful for the help for those members who responded. It has been a of great help in my learning journey.

The one annoying thing on one of the routines is that the routine continues and includes '0.00' in cells that should be left empty when the routine finishes. I would like it not to do that and thought a 'do' statement might cure it.
The routine concerned works and does the job. This is the working code:

Worksheets("BankRec").Range("F34:F73").ClearContents


Range("I2").Select
colselrec = "H"
colselamount = "I"
For counter = 2 To 40 Step 1
rec = Cells(counter, colselrec).Value
valuesrec(counter) = rec
amount = Cells(counter, colselamount).Value
valuesCur(counter) = amount
'Debug.Print valuesrec(counter), valuesCur(counter)


Next counter
Dim newcounter As Integer
Dim updaterow As String
Dim updatecolumn As String
updatecolumn = "F"
updaterow = 34
For newcounter = 1 To 40 Step 1
If valuesrec(newcounter) = "" Then
Cells(updaterow, updatecolumn).Select
ActiveCell.Value = valuesCur(newcounter)
updaterow = updaterow + 1
Else
End If
Next newcounter
' end of new code


MsgBox ("Data has been added successfully.")
ComboBox1.Value = ""
ComboBox2.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
ComboBox5.Value = ""
Worksheets("Master").Activate
Worksheets("Master").Cells(1, 1).Select
End Sub

I used -
IsEmpty(Range("I1")) (Where I want to look for the blank cell
do while isempty(range("I1"))

at the beginning of the routine and a Loop at the end. But the routine functions without any error but the 0.00's remain.
My two questions are: Is the 'do' code right and if so where do I put it?
As always grateful for any advice.
Subbie

 

[Subbie]

Try replacing this

If valuesrec(newcounter) = "" Then
    Cells(updaterow, updatecolumn).Select
    ActiveCell.Value = valuesCur(newcounter)
    updaterow = updaterow + 1
Else
End If

With this

If valuesrec(newcounter) = "" Then
    If Cells(updaterow, "H") <> "" Or Cells(updaterow, "I") <> "" Then
        Cells(updaterow, updatecolumn).Select
        ActiveCell.Value = valuesCur(newcounter)
    End If
updaterow = updaterow + 1
End If

Hi again

I really appreciate you taking the time to help.
I remmed out the old section and used your suggestion as follows:

Next counterDim newcounter As Integer
Dim updaterow As String
Dim updatecolumn As String
updatecolumn = "F"
updaterow = 34
For newcounter = 1 To 40 Step 1
'If valuesrec(newcounter) = "" Then Rem Out
'Cells(updaterow, updatecolumn).Select Rem Out
'ActiveCell.Value = valuesCur(newcounter) Rem Out
'updaterow = updaterow + 1 Rem Out
If valuesrec(newcounter) = "" Then
If Cells(updaterow, "I") = "" Then
Cells(updaterow, updatecolumn).Select
ActiveCell.Value = valuesCur(newcounter)
End If
updaterow = updaterow + 1
End If
Else
End If


Next newcounter

I get an 'Else' without error. I remmed out the Else statement and then got an 'End if' error - remmed that out and the routine ran but no data in F column.

Regards

Subbie

 

[JLGWhiz]

I think this is what you want your For loop to look like.

For newcounter = 1 To 40 Step 1
    If valuesrec(newcounter) = "" Then 
        Cells(updaterow, updatecolumn).Select
        ActiveCell.Value = valuesCur(newcounter)
        updaterow = updaterow + 1 
        If valuesrec(newcounter) = "" Then
            If Cells(updaterow, "I") = "" Then
                Cells(updaterow, updatecolumn).Select
                ActiveCell.Value = valuesCur(newcounter)
            End If
            updaterow = updaterow + 1
        End If
    End If
Next newcounter

 

[Subbie]

[JLGWhiz

Thanks for trying to sort this but I get the same errors as before.
I have also tried other things and they too have not worked. I think I will have to love with them and work around it.

Regards

Subbie

 

[JLGWhiz]

Yes, without seeing the actual sheet, I have exhausted my ideas.
Regards, JLG