VBA Set Named Range

[DRWonoski]

In a workbook I have titles to a table in Row 2. I want to set a named column based on those titles as publicly accessible throughout all of the code in the workbook. I'm currently getting a Compile Error: Type Mismatch with .Match being highlighted. Thoughts?

 Public ColPWSN As Range

Set ColPWSN = WorksheetFunction.Match("PW SN", ActiveWorkbook.ActiveSheet.Range("2:2"), 0)

 

[Eric W]

Try:

    Set colpwsn = Columns(WorksheetFunction.Match("PW SN", ActiveWorkbook.ActiveSheet.Range("2:2"), 0))

 

[DRWonoski]

Now I get a run-time error '13': Type Mismatch

 

[Eric W]

Do you actually have "PW SN" in row 2 of the active sheet? Other than that, the code as shown works for me.

 

[DRWonoski]

I do. I have this being applied to 22 different columns (PW SN, PO, SO etc) so I commented each one out one at a time. All were fine except for the final code

Debug.Print (ColShortName)

gives the error.

 

[DRWonoski]

but I was just using that to validate. A different sub is giving me another mismatch error though, can you take a look at this and help me understand what is wrong and why?

Note: ColShortName is one of the column ranges we just made "Public"

Sub GetRange()
     
    Dim where As Range, ShortName As String
        
    ShortName = "LSFR"
    
    Set where = ColShortName.Find(what:=ShortName, after:=ColShortName(1), searchdirection:=xlPrevious)
    where.Activate
    
End Sub

 

[Eric W]

You use the line of code from post #2 22 times, and just the PW SN fails? Then I'd check to see if the heading matches the sheet exactly, including embedded spaces.

As far as ColShortName, you might be better off with:

Debug.Print ColShortName.Address

You can't "Print" an object, but you can print one of its properties.


On your latest bit of code, try changing:

Set where = ColShortName.Find(what:=ShortName, [COLOR=#ff0000]after:=ColShortName(1)[/COLOR], searchdirection:=xlPrevious)

to:

Set where = ColShortName.Find(what:=ShortName, [COLOR=#ff0000]after:=ColShortName.Cells(1, 1)[/COLOR], searchdirection:=xlPrevious)

I'm not entirely clear what you're trying to do, but if you're trying to reference a particular cell within a range, there are several ways to do it. Using Cells in this way lets you select the row and column offset from the upper left corner of the range.

But note that if you get any kind of run-time error, then any persistent public variables will be cleared, requiring you to rerun the procedure that creates them. For that reason (among others), I'm not a fan of using public variables in this manner.

 

[DRWonoski]

My main objective is to create a button that the user can select to insert a new row where appropriate. For example, Rows 10-24 currently hold "Dogs" (Column AI states "Dogs") and Rows 27-51 currently holds "Cats". When a user clicks the button, I want excel to determine whether this is a Dog or a cat and insert a new row just below the last of its type. My end state involves much more than this but this is where I currently am.

 

[DRWonoski]

Since the "Dogs" and "Cats" can change, I have it separated out into separate modules. My thought process went like this.

First module: Set Column Names
Second Module: Set Range (identify last row where "Cats" exists and set cell to active
Thrd module: Copy active row, insert into row below including all formats, and add order number

 

[Eric W]

While I am a fan of small, easily changed modules, there are also standards for communicating between modules. The way you define those modules, those seem more like different sections within a single module. Hard to say without knowing what other modules you have or plan on. Also hard to say without knowing your level of programming experience in general, and VBA experience in specific. Using some of the built-in functionality of Excel/VBA, all of that functionality can probably be done in 2 or 3 lines. It would probably be easier to help you out if you could show a before and after snapshot of what you're trying to do.