| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Statement with variables
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement with variables
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 "Neutron1871" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement with variables
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 -- Regards, Tom Ogilvy "Neutron1871" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement with variables
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 "JMB" wrote: 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 "Neutron1871" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Statement with variables
Sorry, I forgot to change
Dim mth() As Long to Dim mth() As String "Neutron1871" wrote: 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 "JMB" wrote: 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 "Neutron1871" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF Statement with two variables | Excel Worksheet Functions | |||
| how do i if statement for 3 variables | Excel Worksheet Functions | |||
| Too many variables for an if than statement? | New Users to Excel | |||
| Use variables in sql statement | Excel Programming | |||
| Variables in a Range statement | Excel Programming | |||
Linear Mode
