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

 

[JLGWhiz]

First of all, the IsEmpty() function in VBA is not intended to check cell values. It is intended to check variant variables to see if they are initialized. To check for empty cells in VBA use

 If Range("A1") = "" Then

syntax. And to check for 0 or 0.00 values use

 If Range("A1") = 0 Then

 

[Subbie]

Thanks JLGWhizz

I am new to VBA and am not sure how I would use the 'If Range("A1") = 0 Then' statement.

Could you be good enough to explain that procedure to assist my learning.

Thanks

Subbie

 

[Ark68]

Hi Subbie, let me see if this helps you out ...

If range("A1") = 0 then
    '... do this code when the value in cell A1 = 0
End if

This loosely translates to :
- check the contents of cell A1 in whatever worksheet you're working with.
- if the value equals 0 then ...
- do whatever code you need doing when that condition is met.
- endif is needed to close the if statement. Every if needs an end if to close up the routine.

For example ...

With worksheet("sheet1")                                                  'Referring to worksheet sheet1
     if .range("A1") = 0 then                                                'if cell A1 = 0
           Msgbox "The value in cell A1 is 0"                            'Display a message box stating value in cell A1 is zero
           Msgbox "The value in cell A1 is " & .range("A1")        'Another way of saying that cell A1 is equal to zero by referring to the actual value in the cell.
     Else                                                                             'Now if the value in cell A1 isn't zero
           Msgbox "The value in cell A1 is NOT 0"                     'A message to tell you simply its not equal to zero
           Msgbox "The value in cell A1 is NOT 0, but rather " & .range("A1")    'A message stating the actual value in A1
     End If                                                                            'CLoses the If / Else validation
End With                                                                           'Takes focus away from sheet1

 

[Subbie]

Thanks Ark68
Thank you for that. It was a very clear explanation and really helped my growing knowledge.
I used the code in three different places. There were no error and the routine carried out its function. However, the 0.00 still appear in Column I as below:

[TABLE="width: 197"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]£2.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£5.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]£7.00[/TD]
[/TR]
</tbody>[/TABLE]
What I am trying to do is stop the routine returning the 0.00 if there is no further values in column I. See my code in first post.
It searches for the amount in Column I and then compares it to the adjacent column H to find any blanks in the column ignoring any H cells that have the text YES. It then returns the amount in column I to Cells F35:70. It does it exactly right but then there are the 0.00's added, where there is no amount found in I and no Text in H. I hope i have been able to explain the routine OK.
Thanks

 

[JLGWhiz]

Let me see if we can be clear on what your sheet shows and where youe want the 0.00 to disappear from.
1. Which column does the 0.00 value appear in?
2. Whch column do you want to evaluate to determine if the 0.00 needs to go away?
3. Are the values in the column with the 0.00 derived from formulas or are the constant values?
4. Do you want just the cell with the value 0.00 to equal blank or do you want to clear the entire row of data?
5. And finally, do you want to just clear contents or do you want to delete and shift cells?

the reason for these questions is that the code in the OP identifies columns F, H and I as columns that affect the outcome of the code.

 

[Subbie]

Let me see if we can be clear on what your sheet shows and where youe want the 0.00 to disappear from.
1. Which column does the 0.00 value appear in?
2. Whch column do you want to evaluate to determine if the 0.00 needs to go away?
3. Are the values in the column with the 0.00 derived from formulas or are the constant values?
4. Do you want just the cell with the value 0.00 to equal blank or do you want to clear the entire row of data?
5. And finally, do you want to just clear contents or do you want to delete and shift cells?

the reason for these questions is that the code in the OP identifies columns F, H and I as columns that affect the outcome of the code.

Hi again

1. The 0.00 are appearing in column F35:70
2. The column I is searched for a currency value IF the adjacent cell column H is blank then the value of I is put into column F35:70
3. The F column is cleared each time the routine is run and replaced with new values
4. I want the cells in F34:70 to only have the currency values and be blank if there is no value found in I.
5. See above comment.
The code at the beginning of the thread is the code that works to do the job except for these 0.00's!
So in summary:
In column H the word 'Yes' appears if the amount in I has been reconciled. (A different routine does this)
The routine causing the 0.00's finds the cells with values in I that are not reconciled and therefore the adjacent H cell is blank.
It puts these values in F34:70 but if the I cell and adjacent H cell are both blank it posts the 0.00.

 

[JLGWhiz]

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

 

[Subbie]

Hi Thanks for replying

I implemented your suggestion the routine runs without errors but it does not fill in the F column.

Subbie

 

[JLGWhiz]

Maybe it only needs to evaluate column I. Try this instead.

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