Macro Conditions

Question

I a creating a function in excel using VBA Macro, where it will check if there is a empty cell in a certain column. For example I need to check column A to E and each columns needs to check if there are empty cells, it its empty it will be filled with color red and then add an error comment in column J like " Column A is empty. Please check." (The error is the empty cell) I already have a code for filling the empty cell with color red, what I am having trouble is the error comment because if there are empty cells in different columns but in the same row like column A and column B is empty the Error comment must be "Column A, B is empty. Please check" I just need to insert ", B" in the sentence. And I am having trouble in that condition. please help me

I will attach the code for error comment I just need the condition for inserting ",B" inside the existing comment. Thank you in advance (Note: there are other comments for other functions that I created.)

'Add Error Comment to Column J

cellAddress= "J" & i
tgtValue = "column A is empty. Please check" 

If Range(cellAddress).Value = "" Then 
   Range(celladdress).Value = "->" + tgtValue

Elself Range(cellAddress).Value <> "" And InStr(1,Range(cellAddress).Value, tgtValue, vbTextCompare) = 0 Then
     Range(cellAddress).Value = Range(cellAddress).Value + " " + tgtvalue

End If

End If

None at the moment as I wasn't able to figure it out. I got this error comment from other functions that I created

Answer 1

First, your Elself statement has a lowercase l instead of an uppercase I (in uppercase it says ELSELF).

You might get a better answer if you share more of your code, but based on what you have, to add ",B", try assigning the new value like this:

Range(cellAddress).Value = Replace(Range(cellAddress).Value, " is empty", ",B is empty")

Answer 2

Something like this:

Sub CheckEmpty()

    Dim rw As Range, msg, sep, c As Range, ws As Worksheet
    
    Set ws = ActiveSheet                     'for example
    Application.ScreenUpdating = False
    For Each rw In ws.Range("A2:E10").rows   'loop over rows in your range
        msg = ""                             'clear message and separator
        sep = ""
        For Each c In rw.Cells               'loop cells in row
            If Len(c.Value) = 0 Then         'empty?
                c.Interior.Color = vbRed
                'collect the column letter
                msg = msg & sep & Replace(c.Address(False, False), c.Row, "")
                sep = ", "         'populate separator after first value
            Else
                c.Interior.ColorIndex = xlNone 'OK - clear any fill
            End If
        Next c
        'add/clear message
        With ws.Cells(rw.Row, "J")
            .Value = IIf(Len(msg) > 0, "Column(s) " & msg & " should be populated", "")
        End With
    Next rw

End Sub

Answer 3

• A1:E10 is for demo purpose. You can add more code lines to determine the source data range.

Option Explicit
Sub DEMO()
    Dim rRow As Range, rCell As Range, rBlank As Range
    Dim sCol As String
    Const DATA_RNG = "A1:E10"  ' data range, modify as needed
    Const CMT_COL = "I" ' comment column
    Const CMT = "Column @ is empty. Please check" ' @ is a placeholder for columns
    Set rBlank = Range(DATA_RNG).SpecialCells(xlCellTypeBlanks) ' get the blank cells
    If Not rBlank Is Nothing Then
        For Each rRow In rBlank.Rows
            sCol = ""
            For Each rCell In rRow.Cells
                rCell.Interior.Color = vbRed
                sCol = sCol & "," & Split(rCell.Address, "$")(1)
            Next
            ' Update comment
            Cells(rRow.Cells(1).Row, CMT_COL) = Replace(CMT, "@", Mid(sCol, 2))
        Next
    End If
End Sub

Microsoft documentation:

Replace function

Split function

Range.SpecialCells method (Excel)