Excel/VBA Rows and column if statement

Question

i'd like to make some function which allow me check if any of field in row 149 is text "Mandatory" then if it's, check if rows below are empty if is then do sth. So I tried sth like this:

If ws.Rows("149") = "Mandatory" Then
If ws.Range("C" & chk.TopLeftCell.Row).Value

But I don't have idea how to write second to check value in each column

Help guys! Thanks!

My vba script for now:

Sub CheckBoxDate()
Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD As Long
Dim lColChk As Long
Dim lRow As Long

Dim rngD As Range
lColD = 0 'number of columns to the right for date

Set ws = Sheets("MA Template_VBack-End")
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)


Select Case chk.Value
   Case 1   'box is checked
  For Each chk In ws.CheckBoxes

    If ws.Range("C" & chk.TopLeftCell.Row).Value = vbNullString Then

 chk.Enabled = False
        rngD.EntireRow.Interior.Color = vbGreen

    End If
Next chk

   Case Else   'box is not checked
      rngD.ClearContents
      rngD.EntireRow.Interior.ColorIndex = xlColorIndexNone
End Select

End Sub

Now, one of the main problem is how to write this statement:

If ws.Range("C" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("D" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("E" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("f" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("g" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("i" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("t" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("u" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("z" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ab" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ac" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ap" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("at" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bs" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bt" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bu" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bv" & chk.TopLeftCell.Row).Value = vbNullSt
ring Or ws.Range("bx" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("bz" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ca" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cc" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cd" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ce" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ci" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ck" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cl" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cm" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cn" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("co" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cp" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cq" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("cs" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ea" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ed" & chk.TopLeftCell.Row).Va
lue = vbNullString Or ws.Range("ee" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("eg" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("eh" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ei" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("ej" & chk.TopLeftCell.Row).Value = vbNullString Then

Because this will be based on first if

Shai Rado · Accepted Answer · 2017-04-14 12:52:36Z

2

To find if "Mandatory" appears in row 149 in ws worksheet, use the Application.Match function.

see code below:

If Not IsError(Application.Match("Mandatory", ws.Rows(149), 0)) Then ' <-- successful match
    ' rest of your code goes here

End If

answered Apr 14, 2017 at 12:52

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ttrx Over a year ago

Thanks! Do you know how to write this in way which will be accept by VBA? If ws.Range("C" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("D" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("E" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("f" & chk.TopLeftCell.Row).Value = vbNullString Or ws.Range("g" & chk.TopLeftCell.Row).Value = vbNullString Or etc....

Shai Rado Over a year ago

@Ttrx what exactly you want to achieve with this line?

Ttrx Over a year ago

I have to check if cells in column are mandatory (base on 139 row) and then if this column is mandatory for each one check if its empty or not.

Ttrx Over a year ago

So to be clear, if in row 139 for column A, B, E and Y value is Mandatory then IF cells in this column are empty then disable checkbox. The last one disable checkbox works but i don't have any idea how to write if statements

Shai Rado Over a year ago

@Ralph LOL, more than a few around here lately ;)

kolcinx · Accepted Answer · 2017-04-14 12:59:32Z

1. Determine the range to be checked

It is better practice to specify exactly the range, you want to check for values. Otherwise, you can make your VBA inefficient. So maybe you only need to check range ("A149:Z149"), from now on reffered to as SearchRange

2. Get the range into VBA array (optional, recommended)

In VBA you can transfer the SearchedRange to Variant array. Like so:

Dim SearchedArray as Variant
SearchedArray  = sheetXY.range("A149:Z149").value2

3. Check each member of array against criteria

You can then loop through all the values in Variant array. Like so:

Dim Member as Variant
For Each Member In SearchedArray
 If Member = "Mandatory" Then
  Msgbox "Found it!"
  Exit For
 End If
Next

Collectives™ on Stack Overflow

Excel/VBA Rows and column if statement

2 Answers 2

5 Comments

1. Determine the range to be checked

2. Get the range into VBA array (optional, recommended)

3. Check each member of array against criteria

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1. Determine the range to be checked

2. Get the range into VBA array (optional, recommended)

3. Check each member of array against criteria

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related