CODEWITHSUNDEEP
excelvbacountif
user3191114
user3191114

Reputation: 3

Countif code for VBA

I have several column ranges in Excel J10:J111, N10:N111, R10:R111 and so on for which I need to replace blank cells with a drop down list and leave all the other cells in the range as they are (ie. they are all "N/A").

I managed to make it work by using the following code:

    Range("N10:N111").Select
     Selection.SpecialCells(xlCellTypeBlanks).Select
     With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="0,1,2,3,4,5"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With

Unfortunately, if I have no blank cells in the range (as it happens in some cases) the macro shuts down.

Is there a way of telling the code "If there are only "N/A" skip this section of the macro and go on to the next one?

Upvotes: 0

Views: 256

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Try to use following modification of your code:

Sub Test()
   Dim rng As Range

   On Error Resume Next
   Set rng = Nothing
   Set rng = Range("N10:N100").SpecialCells(xlCellTypeBlanks)
   If Not rng Is Nothing Then
       With rng.Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="0,1,2,3,4,5"
          .IgnoreBlank = True
          .InCellDropdown = True
          .InputTitle = ""
          .ErrorTitle = ""
          .InputMessage = ""
          .ErrorMessage = ""
          .ShowInput = True
          .ShowError = True
       End With
   End If
   On Error GoTo 0
End Sub

Upvotes: 1

Related Questions