Data validation (Lists) in VBA

0

I put them a bit in context. As an example:

I have a table with data and in column A there are a number of countries but in some cells those countries can come empty (and the entry of data must be filled with a list validation) and in column B these are the cities which, depending on the country you select, should be shown in the validation, only those that belong to that country.

So far everything is correct, if I apply the list using a macro in VBA it is something like this:

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=OFFSET('COMBINATIONS'!" & AOrigin & ", MATCH(" & ACell & "," & AList & ",0)-1,1,COUNTIF(" & AList & "," & ACell & "),1) "
        .IgnoreBlank = True
        .InCellDropdown = True
        .ErrorTitle = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

The detail arises when, for example, in column A the cell is empty. When wanting to assign the data qualification the VBA compiler throws me " Error defined by function or object ".

If this process is done manually, it sends me an error message which I can omit and the validation is carried out, and once the person selects a country the validation for the cities works. My question here is:

Is there a possibility that within the VBA code you can assign this validation independently of whether the cell corresponding to the country is empty?

Thank you very much in advance.

    
asked by PoorChristmas 28.03.2018 в 17:44
source

0 answers