InStr()/Select Case [Excel VBA 2010]

**lloydgodin** · 10-08-2012, 12:29 PM

I have the following code:

    If InStr(ActiveWorkbook.Name, "xxx") > 0 Then
        ActiveSheet.Range("J1").Value = "yyy"
        ActiveSheet.Range("J1").Select
    Else
        If InStr(ActiveWorkbook.Name, "aaa") > 0 Then
            ActiveSheet.Range("F1").Value = "bbb"
            ActiveSheet.Range("F1").Select
        Else
            If InStr(ActiveWorkbook.Name, "ccc") > 0 Then
                ActiveSheet.Range("F1").Value = "ddd"
                ActiveSheet.Range("F1").Select
            Else
                If InStr(ActiveWorkbook.Name, "eee") > 0 Then
                    ActiveSheet.Range("F1").Value = "ffff"
                    ActiveSheet.Range("F1").Select
                Else
                    If InStr(ActiveWorkbook.Name, "ggg") > 0 Then
                        ActiveSheet.Range("F1").Value = "hhh#"
                        ActiveSheet.Range("F1").Select
                    Else
                        If InStr(ActiveWorkbook.Name, "iii") > 0 Then
                            ActiveSheet.Range("F1").Value = "jjj"
                            ActiveSheet.Range("F1").Select
                        Else
                            If InStr(ActiveWorkbook.Name, "lll") > 0 Then
                                ActiveSheet.Range("F1").Value = "mmm"
                                ActiveSheet.Range("F1").Select
                            Else
                                ActiveSheet.Range("G1").Value = "nnn"
                                ActiveSheet.Range("G1").Select
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If

I'd like to use Select Case but am unsure how to set it up using InStr(). Any help is appreciated.

**JosephP** · 10-08-2012, 01:07 PM

why do you want to use select case? I can't see any benefit from it but you would need to use

select case true
Case ActiveWorkbook.Name like "*xxx*"

**lloydgodin** · 10-08-2012, 01:16 PM

There are several workbooks that will be created and I have to keep adding to this list. The location of the specific cell changes periodically due to formatting issues. Trying to clean up the code and not have a bazillion nested If/Thens.

**JosephP** · 10-08-2012, 01:19 PM

then you ought to use a reference table that you can loop through in my opinion. select case will be just as untidy as if..elseif

**lloydgodin** · 10-08-2012, 01:22 PM

Fair Enough.

Can you provide an example of a reference table & loop?

I am not a programmer by nature. Taught myself a few things, learn stuff from people on here, but not very good at it. Nor is it consistent. Any help is appreciated.

**JosephP** · 10-08-2012, 01:31 PM

assuming a three column table on sheet Ref with the name criterion in column 1, the output value in column 2 and the output cell in column 3

dim vData as variant
dim n as long
dim bMatch as boolean
vData = sheets("Ref").Range("ref_table").value
for n = lbound(vdata) to ubound(vdata)
    If InStr(ActiveWorkbook.Name, vdata(n, 1)) > 0 Then
        ActiveSheet.Range(vData(n, 3)).Value = vData(n, 2)
        ActiveSheet.Range(vData(n, 3)).Select
        bmatch = true
    end if
   if bmatch then exit for
next n

**lloydgodin** · 10-08-2012, 04:43 PM

The code in question is in an Add-in I created. I have been unsuccessful in getting the table onto a worksheet within it.

**shg** · 10-08-2012, 05:15 PM

Create a worksheet named Ref in the add-in, add your data there as Joseph suggested, add a named range to reference it, and put this code (modestly adapted from Joseph's, and completly untested) in the add-in:

Sub lg()
    Dim vData       As Variant
    Dim n           As Long
    Dim sName       As String
    Dim wks         As Worksheet

    vData = ThisWorkbook.Worksheets("Ref").Range("ref_table").Value
    Set wks = ActiveSheet
    sName = wks.Parent.Name

    For n = LBound(vData) To UBound(vData)
        If InStr(sName, vData(n, 1)) Then
            With wks.Range(vData(n, 3))
                .Value = vData(n, 2)
                Application.Goto .Cells
            End With
            Exit For
        End If
    Next n
End Sub

**lloydgodin** · 10-08-2012, 05:37 PM

I know how to add a worksheet except everything is greyed out. I simply am unable to do so. No idea why either.

**shg** · 10-08-2012, 05:55 PM

Select the add-in's VBA project in the Project Explorer Window, select the ThisWorkbook module, in the Properties window set the IsAddin property to False, and proceed from there. Set it back to True when you're done.

**lloydgodin** · 10-08-2012, 06:08 PM

Done & Done.
However, there are two issues.

On his code I get a run time error 9: subscript out of range.

vData = Sheets("Ref").Range("ref_table").Value

Your code is getting a run time error 1004: application defined or object defined error. On this line:

vData = ThisWorkbook.Worksheets("Ref").Range("ref_table").Value

**shg** · 10-08-2012, 06:42 PM

Lloyd, if the code is in the add-in, and worksheet "Ref" is in the add-in, and Sheets("Ref") refers to the active workbook (which can NEVER be an add-in), which of those codes do you think is correct?

Did you create the named range "ref_table"?

**lloydgodin** · 10-08-2012, 06:47 PM

Obviously the one that refers "Ref" being in the add-in.

I did but I may have misentered the scope whilst doing so. I'll have to check it on the morrow.

**shg** · 10-09-2012, 10:37 AM

Note the change in the code in the prior post:

sName = wks.Parent.Name

**lloydgodin** · 10-09-2012, 12:59 PM

It's working now. Thank you.

InStr()/Select Case [Excel VBA 2010]

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Re: InStr()/Select Case [Excel VBA 2010]

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions