If a cell is blank in a range
|
Macro Title:
|
|
Worksheet Variable:
|
|
Row Reference Variable:
|
|
Worksheet Name:
|
|
Test From Row Number:
enter the first row number of a cell from which you want to begin testing |
|
Test To Row Number:
enter the last row number of a cell which you want to test |
|
Test from Column Number:
enter the first column number of a cell from which you want to begin testing |
|
Test to Column Number:
enter the last column number of a cell which you want to test |
|
Output Column Number:
enter the column number of the column in which you want to return the results |
|
Value if test is True:
enter the value that you want returned if at least one cell in a range is blank |
|
Value if test is False:
enter the value that you want returned if none of the cells in a range are blank |
|
First Comment:
' |
|
Second Comment:
' |
Sub If_cell_is_blank_in_a_range()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank in a range by looping through each cell in the specified range
For x = 5 To 11
If ws.Application.WorksheetFunction.CountBlank(ws.Range(ws.Cells(x, 3, ws.Cells(x, 5))) > 0 Then
ws.Cells(x, 6) = "Need Stock"
Else
ws.Cells(x, 6) = "Stocked"
End If
Next x
Next x
End Sub
Sub If_cell_is_blank_in_a_range()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell is blank in a range by looping through each cell in the specified range
For x = 5 To 11
If ws.Application.WorksheetFunction.CountBlank(ws.Range(ws.Cells(x, 3), ws.Cells(x, 5))) > 0 Then
ws.Cells(x, 6) = "Need Stock"
Else
ws.Cells(x, 6) = "Stocked"
End If
Next x
Next x
End Sub