Declare one string with multiple variables

[mhdakhtar8]

Hi,

I'm looking for way around getting multiple values set to one variable declared as String. Following codes might give you a glimpse of what I'm looking for.

Dim Name as string
Name = value1 or value2 or value3

If range("A1"). Value = Name then
Activecell.entirecolumn.delete
End If

 

[Jaafar Tribak]

You don't need the Name variable ... You can just use this :

With Range("A1")
    If .Value = value1 Or .Value = value2 Or .Value = value3 Then
        ActiveCell.EntireColumn.Delete
    End If
End With

If you really need the Name string then if the variables value1,value2 and value3 contain strings then you could maybe use something along thes lines :

    Dim Name As String
    Name = Chr(0) + CStr(value1) + Chr(0) + CStr(value2) + Chr(0) + CStr(value3)

    On Error Resume Next
        With WorksheetFunction
            Name = UCase((.Index(Split(Name, Chr(0)), 1, .Match(CStr(Range("A1").Value), Split(Name, Chr(0))))))
        End With
    On Error GoTo 0

    If UCase(((CStr(Range("a1").Value)))) = Name Then
        ActiveCell.EntireColumn.Delete
    End If

 

[mhdakhtar8]

Here's what I'm using. but It's giving me Compile error for sname.Length. can you check?

Sub Generate_Producitvity_Report()
Dim OW As Workbook, ws As Worksheet, sname() As String, i As Integer
sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
Set OW = ActiveWorkbook
For Each ws In ThisWorkbook.Worksheets
ws.Select
With ws
    [COLOR="#FF0000"]For i = 0 To sname.Length[/COLOR]
        If ws.Name <> sname(i) Then
        MsgBox sname(i) & " sheet not found"
        End If
    Next i
End With
Next ws
End Sub

 

[Jaafar Tribak]

Try this :

Sub Generate_Producitvity_Report()
    Dim OW As Workbook, sname() As Variant, i As Integer
    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
    Set OW = ActiveWorkbook [B][COLOR=#008000]' <---- I think should be ThisWorkbook[/COLOR][/B]
    For i = 1 To OW.Worksheets.Count
        If InStr(1, Join(sname, Chr(0)), OW.Worksheets(i).Name, vbTextCompare) = 0 Then
            MsgBox OW.Worksheets(i).Name & " sheet not found"
        End If
    Next
End Sub

 

[Jaafar Tribak]

You could also try this :

Sub Generate_Producitvity_Report()
    Dim OW As Workbook, ws As Worksheet, sname() As Variant, i As Integer
    sname = Array("COS REJECT", "APPROVAL SENT", "Pending Client Response", "Awaiting Four-Eye Check", "Awaiting RDS Approval", "SHEET6", "SHEET1")
    Set OW = ActiveWorkbook[B][COLOR=#008000] ' <---- I think should be ThisWorkbook[/COLOR][/B]
    For i = 0 To UBound(sname)
        On Error Resume Next
            Set ws = OW.Worksheets(sname(i))
            If ws Is Nothing Then
                MsgBox sname(i) & " sheet not found"
            End If
        On Error GoTo 0
    Next
End Sub