If Statement with variables

[Guest]

I am trying to figure out how to make the below VB run correctly. This code
selects row 1 and then based on what is in each cell, deletes any column that
does not have 1 of 4 variables, is not blank, and does not contain the word
"Name". Please help!!


' If statement to ask user what months are being reported on and formats
accordingly.
mthcount = InputBox("Enter # of Months to Report", "Rpt Mths #")

If mthcount = 2 Then
mthone = InputBox("Enter First Reporting Month (mmm-yy)", "Rpt Mth 1")
mthtwo = InputBox("Enter Second Reporting Month (mmm-yy)", "Rpt Mth
2")
ElseIf mthcount = 3 Then
mthone = InputBox("Enter First Reporting Month (mmm-yy)", "Rpt Mth 1")
mthtwo = InputBox("Enter Second Reporting Month (mmm-yy)", "Rpt Mth
2")
mththree = InputBox("Enter Third Reporting Month (mmm-yy)", "Rpt Mth
3")
ElseIf mthcount = 4 Then
mthone = InputBox("Enter First Reporting Month (mmm-yy)", "Rpt Mth 1")
mthtwo = InputBox("Enter Second Reporting Month (mmm-yy)", "Rpt Mth
2")
mththree = InputBox("Enter Third Reporting Month (mmm-yy)", "Rpt Mth
3")
mthfour = InputBox("Enter Fourth Reporting Month (mmm-yy)", "Rpt Mth
4")
End If

Rows("1").Select
If Rows("1").Select <> mthone Or mthtwo Or mththree Or mthfour Or "" Or
"Name" Then
Column.Delete
End If

 

[Guest]

Do you want to delete the entire column(s)? Be sure to back up your data
before trying. Also, are the dates in Row1 dates formatted as "mmm-yy" or
are they text? Inputbox returns text - so if the data in row one are numbers
(date values) it won't match.

Sub test()
Dim i As Long
Dim mthcount As Long
Dim mth() As Long
Dim rngCell As Range
Dim rngDelete As Range

mthcount = CLng(InputBox("Enter # of Months to Report", "Rpt Mths #"))
ReDim mth(1 To mthcount)

For i = 1 To mthcount
mth(i) = InputBox("Enter " & Application.Choose(i, "First", _
"Second", "Third", "Fourth") & " Reporting Month (mmm-yy)", _
"Rpt Mth " & i)
Next i

For Each rngCell In Rows("1").Cells
If IsError(Application.Match(rngCell.Value, mth, 0)) And _
rngCell.Value <> "Name" And rngCell.Value <> "" Then
If rngDelete Is Nothing Then
Set rngDelete = rngCell
Else: Set rngDelete = Union(rngDelete, rngCell)
End If
End If
Next rngCell

If Not rngDelete Is Nothing Then _
rngDelete.EntireColumn.Delete

End Sub

 

[Tom Ogilvy]

If Rows("1").Select <> mthone Or mthtwo Or mththree Or mthfour Or "" Or
"Name" Then


would have to be something like

set rng = Cells(1,256).End(xltoLeft)
for each i = rng.column to 1 step -1
set cell = cells(1,i)
if cell<>"" and lcase(cell) <> "name" then
s = lcase(format(cell,"mmm-yy"))
if s <> lcase(mthone) and s <> lcase(mthtwo) _
and s <> lcase(mththree) and s <> lcase(mthfour) then
cell.EntireColumn.Delete
end if
end if
Next

 

[Guest]

Correct, I want to delete the entire column(s). The dates in row 1 are text.
I copied and pasted values of a 'mmm-yy from a different worksheet to get
these text values in row 1.

I used your code below and it crashes stating "Type Mismatch":

mth(i) = InputBox("Enter " & Application.Choose(i, "First", _
"Second", "Third", "Fourth") & " Reporting Month (mmm-yy)", _
"Rpt Mth " & i)

Here is some data from the table in case you need it:
Name Dec-05 Jan-06 Feb-06 Mar-06 0
Hetfield 8.31797508 13.21934081 17.25015038 3.12754056
Akerfeldt 4.84123504 4.93747796 5.51696188 3.91421368

 

[Guest]

Sorry, I forgot to change
Dim mth() As Long
to
Dim mth() As String