Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Location
    Venice, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding cells in a range (2000)

    Hi,
    I am searching for a value in named range in each of several worksheets. The value is a variable, the sheet is a variable. I would like to find this value, and delete the entire column it is in. This should automatically resize the range to the remaining cells (hopefully). For instance:

    Cells "BL6" through "BW6" on the sheet named "January" are named "MyJanRnge".
    I would like to search "MyJanRnge" for a value of "RemAcct", (lets say 1141), and delete the entire column.
    I have tried cells.find, as noted in <post#=230121>post 230121</post#>, and for each as noted in <post#=227159>post 227159</post#> and about 20 other variations found throughout the board, but can't hit paydirt.

    Here is what I have as of now, any help is appreciated.

    <pre>Sub RemovAcct()
    Dim MyRange As Range
    Dim MyRn As Range
    On Error Resume Next
    RemAcct = Application.InputBox(Prompt:="Account Number To Be Removed. _
    You Can Not Remove The Original 14 Accounts, 1010 to 1140.", _
    Title:="Account Number", Type:=2)
    If RemAcct = False Then
    Exit Sub
    Else
    End If
    If RemAcct / 10 = Int(RemAcct / 10) Then
    MsgBox Prompt:="You Cannot Remove this Account", Title:="Incorrect _
    Account Number"
    Else
    Application.DisplayAlerts = False
    Worksheets(RemAcct).Delete
    Application.DisplayAlerts = True
    For sc = 3 To 14
    Sheets(sc).Columns("H:CZ").Hidden = False
    MyRn = "My" & Left(Sheets(sc).Name, 3) & "Rnge"
    Set MyRange = Cells.Find(What:=RemAcct, After:=Sheets(sc). _
    Range(MyRn),SearchDirection:=xlNext, MatchCase:=True)
    With Sheets(sc).Range(MyRange)
    .Select
    .EntireColumn.Delete
    End With
    Set MyRange = Sheets(sc).Range("H6")
    RemAcct = CInt(RemAcct)
    Line:
    If MyRange = "" Then
    GoTo Line2:
    Else
    If MyRange = RemAcct Then
    Set MyRange = MyRange.Offset(0, 1)
    MyRange.Offset(0, -1).EntireColumn.Delete
    GoTo Line:
    Else
    Set MyRange = MyRange.Offset(0, 1)
    GoTo Line:
    End If
    End If
    Line2:
    Sheets(sc).Columns("H:CZ").Hidden = True
    Sheets(sc).Select
    ActiveSheet.Range("A7").Select
    Next sc
    End If
    Sheets("Summary").Select
    End Sub</pre>



    TIA
    Todd

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding cells in a range (2000)

    First, some general remarks:
    1. <LI>Apparently, you haven't set Option Explicit, for the code contains variables that haven't been declared (RemAcct, sc). I strongly recommend that you check the Require Declaration box in Tools/Options... (in the Visual Basic Editor) to add Option Explicit to all new modules, and that you add Option Explicit to all existing modules. Not declaring variables explicitly can lead to unexpected results.
      <LI>You should only use On Error Resume Next in small sections of code where you check for errors yourself by inspecting the Err object, or if you have a very specific reason for it. If you put it at the start of a procedure, as in your code, you will never know where things go wrong. In this case, you are confusing strings and ranges, but you don't get warned because of the On Error Resume Next.
      While writing and testing code, it is often a good idea to have no error handling, so that you can see where things go wrong. In the final version, you should have error handling to prevent the end user from being dumped into the Visual Basic Editor. A general frame for error handling is:

      Sub MyProcedure()
      On Error GoTo ErrHandler

      ...
      ...

      ExitHandler:
      ' Do your cleaning up here, if necessary
      ...
      Exit Sub

      ErrHandler:
      MsgBox Err.Description
      Resume ExitHandler
      End Sub
      <LI>You use Type:=2 in the InputBox function; that is for text. If I understand your example correctly, you are asking for an aacount number, so you should use Type:=1.
      <LI>In most cases, it is possible to manipulate ranges in VBA without selecting them. This is more efficient.
      <LI>To find the first occurrence of something in a range, use range.Find; you don't need Cells for this. To find subsequent occurrences, use range.FindNext. The result of Find/FindNext is a range; if this is Nothing, the target was not found.
    Here is a modified version of your code, It doesn't do all you want (the range is not resized correctly), but it does delete the columns in the named range.

    Sub RemovAcct()
    Dim MyRange As Range
    Dim MyRn As Range
    Dim RemAcct As Integer
    Dim sc As Integer
    Dim sh As Worksheet

    RemAcct = Application.InputBox(Prompt:="Account Number To Be Removed." & _
    "You Can Not Remove The Accounts, 1010, 1020, ... 1140.", Title:="Account Number", Type:=1)
    If RemAcct Mod 10 = 0 Then
    MsgBox Prompt:="You Cannot Remove This Account", Title:="Incorrect Account Number"
    Exit Sub
    End If

    Application.DisplayAlerts = False
    Worksheets(RemAcct).Delete
    Application.DisplayAlerts = True

    For sc = 3 To 14
    Set sh = Worksheets(sc)
    sh.Columns("H:CZ").Hidden = False

    Set MyRn = sh.Range("My" & Left(sh.Name, 3) & "Rnge")
    Set MyRange = MyRn.Find(What:=RemAcct, SearchOrder:=xlByColumns)
    Do While Not MyRange Is Nothing
    MyRange.EntireColumn.Delete
    If MyRn Is Nothing Then Exit Do
    Set MyRange = MyRn.FindNext
    Loop

    sh.Columns("H:CZ").Hidden = True
    Next sc

    Sheets("Summary").Select
    End Sub

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Location
    Venice, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cells in a range (2000)

    HansV,
    I had not set option explicit. I have seen this recommendation before, and never had time to ask why. I just set it however, on your advice.

    The On Error Resume Next is in place because originally, I had set the code to remove a sheet, and if the sheet did not exist, it would give an error. I could trap this easily enough by an if statement, but when this sub was only a few lines long and only removed a sheet, there was really no need. I need to, and have, removed this line, and will trap the sheet error this way. BTW, is ErrHandler not a sub, like a line label, only outside the procedure?

    I have to use type:=2, if not, the delete sheet statements look for the index number, rather than the sheet named RemAcct. probably could Cstr and then change back, but I didnt think it would be an issue, as it will compare 1141 with "1141", and find it (or so I think).

    Yes, I just rewrote several hundreds of lines, based on some help you gave me in a different thread <post#=233379>post 233379</post#>. Here, I needed to select the cell so I could watch what was going on, I initially had only referenced ranges, but got lost too easy <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    I haven't been able to try the code you sent for me yet, because I dont understand one thing.

    <pre>Do While Not MyRange Is Nothing
    MyRange.EntireColumn.Delete
    If MyRn Is Nothing Then Exit Do
    Set MyRange = MyRn.FindNext
    Loop
    </pre>



    I do not really understand the "not myrange is nothing" double negative here, meaning if the cell is not empty, delete the column? I looked in help, but can't find it, although I am sure it is there. I have seen this code numerous times on this board, but never really figured it out.

    The remaining code, from "Set MyRange = Sheets(sc).Range("H6")" to "Line2:" is important, as it deals with a different area of the workbook, but this is ok, I will probably learn a better way to handle this work when I figure out the first part. (notice I didnt select any cells there, thanks to your last reply, hehe).

    The selections at the bottom are to move the selected cell to the beginning of the page, or the first typed cell, and just do that cuz Im a neatnik.

    Nice job with the Mod thing. Didn't know that one was around, but did a search on it, and found it right away.

    Thanks for the quick response, and any further help.

    Todd

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding cells in a range (2000)

    Hello Todd,

    1. About error handling

    ErrHandler and ExitHandler are labels, just like the Line and Line2 labels in your original code. Old versions of Basic had little in the way of structured programming, so programmers had to use labels and GoTo a lot, resulting in so-called spaghetti code. Nowadays, the use of labels and GoTo is hardly necessary any more, but in error handling we still use On Error GoTo <label> and Resume <label>.

    If you want to prevent an error occurring while trying to remove a non-existing sheet, you can use On Error Resume Next, but you should restore error trapping immediately afterwards, e.g.

    On Error Resume Next
    Worksheets(...).Delete
    On Error GoTo ErrHandler ' or On Error GoTo 0 if you want standard Excel error messages.

    2. About the find loop

    I'll try to explain the following bit of code:

    (a) Set MyRange = MyRn.Find(What:=RemAcct, SearchOrder:=xlByColumns)
    ([img]/forums/images/smilies/cool.gif[/img] Do While Not MyRange Is Nothing
    MyRange.EntireColumn.Delete
    (d) If MyRn Is Nothing Then Exit Do
    (e) Set MyRange = MyRn.FindNext
    (f) Loop

    In (a), we instruct Excel to look for RemAcct within the range MyRn, starting at the top left corner and proceeding by columns, i.e. down, then across.

    The result is a Range object reperesenting the first cell containing RemAcct. If RemAcct is not found, this range is an empty object; we can test for that by using MyRange Is Nothing. Note the use of the word Is instead of the operator =. Because "empty" is not really a value, we can't test MyRange = Nothing. We only want to delete a column if this condition is not fulfilled, so in ([img]/forums/images/smilies/cool.gif[/img] we use the test Not MyRange Is Nothing. To make is clearer, you could write Not (MyRange Is Nothing).

    If OK, we delete the entire column of the found cell in .

    I don't know if (d) is really necessary. If your original named range consisted of one column, and this column is deleted, the range might disappear entirely, so I test if it has become Nothing. If so, (d) gets us out of the loop.

    Instruction (e) continues the search within the range, then in (f) we loop back to ([img]/forums/images/smilies/cool.gif[/img] - note that we don't need a label for this. ([img]/forums/images/smilies/cool.gif[/img] tests again if MyRange is not empty, and so we continue until RemAcct is not found any more. Then, MyRange will be Nothing, so we jump out of the loop, and execution will continue on the instruction past (f).

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding cells in a range (2000)

    Todd,
    If you use the RemAcct as an Integer and have it as a number EVERYWHERE but it is ALSO used as a sheetname, then you can delete it using:

    <pre> On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets(Format(RemAcct, "0")).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    </pre>


    Steve

  6. #6
    New Lounger
    Join Date
    Mar 2003
    Location
    Venice, Florida, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cells in a range (2000)

    HansV,

    Thanks very much for your help here, I get it fully now.

    Just two more questions. First, I am trying to sort the range after adding data to it. I have tried the following:

    <pre> Sub InsertCols()
    Dim LastAcctNum As Integer
    Dim COC As Integer
    Dim MyRn As Variant
    Dim Mon As Range

    NewAcct = CInt(NewAcct)
    For sc = 3 To 14
    Sheets(sc).Columns("H:CZ").Hidden = False
    MyRn = "My" & Left(Sheets(sc).Name, 3) & "Rnge"
    Set Mon = Sheets(sc).Range(MyRn).Range("A1").Offset(0, Range(MyRn).Columns.Count
    Sheets(sc).Parent.Names(MyRn).RefersTo = Sheets(sc).Range(MyRn).Resize _
    (Sheets(sc).Range(MyRn).Rows.Count, Sheets(sc).Range(MyRn).Columns._
    Count + 1)
    Range(MyRn).Range("A1").Offset(0, Range(MyRn).Columns.Count - 1).Value = NewAcct
    Set Mon = Sheets(sc).Range("My" & Left(Sheets(sc).Name, 3) & "Rnge")

    Sheets(sc).Mon.Sort ' and Sheets(sc).Range(Mon).Sort
    ' and Sheets(sc).Range("BI6:BV6").Sort

    COC = Range(Sheets(sc).Range("H6"), Sheets(sc).Range("H6").End(xlToRight)). _
    Cells.Count
    LastAcctNum = Sheets(sc).Range("H6").Offset(0, COC - 1)
    Set MyRange = Sheets(sc).Range("H6")
    For MyCell = 1 To COC
    If NewAcct > LastAcctNum Then
    Set MyRange = MyRange.Offset(0, COC)
    GoTo Line4
    ElseIf NewAcct > MyRange.Value Then
    Set MyRange = MyRange.Offset(0, 1)
    Else
    Line4:
    </pre>

    Trunced here

    I get Sort Method of Object Range Class failed.
    I also realize I need to spend some time making Sheets(sc) = sh, like in the previous examples, and I plan to do that tomorrow. Certianly makes the code cleaner and easier to write and read.

    Second question, How would you like your name spelled in the credits to this project, <img src=/S/smile.gif border=0 alt=smile width=15 height=15> . Seriously, you have been a great help on this. Thanks.'

    TB

    PS

    Thanks also to Steve, I will be using that trick elsewhere

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding cells in a range (2000)

    Hi Todd,

    Sort is a method of the Range object. In your code, Mon is a range. Sheets(sc).Mon is not valid because Mon is not a property of a worksheet. Range(Mon) is not valid either because Mon is not a string. Apart from that, you must specify at least one key to sort on.

    Try

    Mon.Sort Key1:=Mon.Columns(1)

    if you want to sort on the first column. If you have column headings that should stay in place, add Header:=xlYes:

    Mon.Sort Key1:=Mon.Columns(1), Header:=xlYes

    <img src=/w3timages/blueline.gif width=33% height=2>

    I don't want to be mentioned in the credits. If you wish, you can mention Woody's Lounge. The URL is http://www.wopr.com/cgi-bin/w3t/wwwthreads.pl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •