CODEWITHSUNDEEP
excelvbamacos
mcflame
mcflame

Reputation: 48

Setting string variable to wildcard based on cell value

I am creating a spreadsheet where a macro can be restricted based on the "data creator"-- a column with a name for every line of data based on who entered the data.

For example if "Dan" entered the line of data, it is tagged as "Dan" in column Z.

You can then choose which "data creator" to run through the macro based on a dropdown in a cell, with a complete list of names.
Here you can also select an asterisk to see all records, which would ideally act as a wildcard in VBA.
The raw data is on sheet one, and the dropdown is in cell "D29" on sheet 2.

If a name is selected, only that data is handled in the code.

When the asterisk is selected, it does not act as a wildcard, NO data is handled.

Dim Record as Variant, cell As Range
Record = Sheets("Sheet 2").Range("D29") 'Dropdown cell that can be a data creator or an asterisk
    
For Each cell In Sheets("Sheet 1").Range("Z3:Z100")
    If cell = Record Then
        'Execute Code
    Else
    End If
Next cell

I could deal with this through another if statement of a select case statement, but I was hoping that using the asterisk would slim the code down.

I am running Excel 2017 for Mac.

Upvotes: 0

Views: 1476

Answers (1)

Slai
Slai

Reputation: 22876

A bit safer, and probably a bit more efficient alternative:

Dim Record As String, cell As Range
Record = Sheets("Sheet 2").Range("D29") 

For Each cell In Sheets("Sheet 1").Range("Z3:Z100")
    If cell = Record Or Record = "*" Then
        'Execute Code
    End If
Next cell

Upvotes: 1

Related Questions