CODEWITHSUNDEEP
excelvba
wenzel267
wenzel267

Reputation: 93

How can I handle empty Cells with my macro in VBA?

I want to remove the first 4 characters from every cell. In my code I first look what the last row is and then tell the program to run until this point. It only works if in the selected column are no empty cells. if there are empty ones the program stops at the first empty cell. I tried some loops but the never worked...

Here is my code:

Sub datei_format_prefix()

Dim Zelle As Range
Dim bCell As Range

find_last_row

For Each bCell In Selection

    Range(bCell.Address).Select
    Range(bCell.Address).Offset(1, 0).Select


   Range(Selection, Selection.End(xlDown)).Select


   For Each Zelle In Selection
       If Zelle.Value Like "*_*" Then
           Zelle.Value = Right(Zelle, Len(Zelle) - 4)
       End If

       If Zelle.Value Like "?########" Then
           Zelle.Value = Right(Zelle, Len(Zelle) - 1)
       End If
   Next
Next

End Sub
Sub find_last_row()
Dim rownum As Integer
Dim SelRange As Range

Set SelRange = Selection

Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
rownum = Selection.Row + Selection.rows.Count - 1
MsgBox rownum


SelRange.Select

End Sub

Some Data:

|This is Row 1 of Excel Sheet with some Buttons in it|
|    Heder1    |  Header2   |   Header3    |
|--------------|------------|--------------|
| JFG_12345678 | Production | ABC_12345678 |
| JFG_12345678 | Production | ABC_12345678 |
| JFG_12345678 | Production | ABC_12345678 |
| JFG_12345678 | Production |              |
|              | Production | ABC_12345678 |
|              | Production |              |
| JFG_12345678 | Production | ABC_12345678 |

Upvotes: 0

Views: 554

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

That should be easy. the problem appears to be that you are trying to return a value <= 0 when using Right. There are a couple of approaches you could take:

For Each Zelle In Selection
    Dim ZelleValue As Variant
    ZelleValue = Zelle.Value

    'If ZelleValue  <> vbNullString Then ' If Cell Isnt Blank.
    'This is what you're asking for, but will still allow errors.
    If Not IsError(ZelleValue) Then
        If Len(ZelleValue > 1) Then
            If Len(ZelleValue) > 4 Then
                If ZelleValue Like "*_*" Then
                    Zelle.Value = Right(ZelleValue, Len(ZelleValue) - 4)
                End If
            End If

            If ZelleValue Like "?########" Then
                Zelle.Value = Right(ZelleValue, Len(ZelleValue) - 1)
            End If
        End If
    End If
Next

Note that I added the conditional for checking for a blank cell, but commented it out. The reason is that you really should be checking that the length of your cell meets expectations. If, for example, you try to use a value less than zero as an argument to right or left, you will receive an error. To avoid this, we make sure that we can return Len(ZelleValue) - 4) and Len(ZelleValue) - 1 before calling these conditionals. In turn, we will skip blanks as well as cells not meeting the expectation (such as "f_o" which is Like "*_*" but Len("f_o") - 4 equals -1).

Upvotes: 2

Related Questions