Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find Mean or MEAN or mean (Excel xp)

    I'm trying to write a macro to search for the word Mean, MEAN, (mean), (MEAN), mean:, etc. in Column A. I was using an input box but the users don't like that. I don't know how to make it look for all these possibilities for the word Mean.

    Range("A1").Select
    lLastRow = ActiveSheet.Range("A65536").End(xlUp).Row - 1
    'iMean = InputBox("Search for what? Spell exactly with punctuation...") 'want to delete this

    For i = 1 To lLastRow
    If ActiveCell.Value = iMean Then
    ActiveCell.EntireRow.NumberFormat = "0.0"
    ActiveCell.Offset(1, 0).Activate
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Next i


    Thank you for the help. <img src=/S/basket.gif border=0 alt=basket width=58 height=52>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Find Mean or MEAN or mean (Excel xp)

    How do you want to handle multiple instances of the word "mean", what will you do with them after you find them?

    [Edit - doh, I mean other than set the number format?]
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Find Mean or MEAN or mean (Excel xp)

    Subject to my confused question (sorry), here's some starter code:

    Sub SetMeanRows()
    Dim rngTC As Range, rngCell As Range
    Dim intC As Integer
    On Error Resume Next
    Set rngTC = ActiveSheet.Columns("A:A").SpecialCells(xlCellType Constants, 2)
    If rngTC Is Nothing Then Exit Sub
    For Each rngCell In rngTC
    If InStr(LCase(rngCell.Value), "mean") > 0 Then
    intC = intC + 1
    rngCell.EntireRow.NumberFormat = "0.0"
    End If
    Next rngCell
    MsgBox intC & " instances of Mean found"
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Mean or MEAN or mean (Excel xp)

    yep, that works for me now. Thank you

    I am doing another one...

    Sub FlipBase()
    'searches for BASE in column A and moves, adds % sign
    'Before: A, 100
    'After: (100), %, A
    Dim rngTC As Range
    Dim rngCell As Range
    Dim intC As Integer

    Range("A1").Select
    On Error Resume Next
    Set rngTC = ActiveSheet.Columns("A:A").SpecialCells(xlCellType Constants, 2)
    If rngTC Is Nothing Then Exit Sub
    For Each rngCell In rngTC
    If InStr(LCase(rngCell.Value), "base") > 0 Then
    intC = intC + 1
    rngCell.EntireRow.Cut
    rngCell.Offset(-1, 0).Insert Shift:=xlDown
    'add parens to cols B thru end and center
    rngCell.Offset(1, 0).Insert Shift:=xlDown
    'add a % sign underneath cells that have a base number
    'add parens around the letters
    End If
    Next rngCell

    MsgBox ("That's All Folks!")
    Range("A1").Select

    End Sub

    I am looking for the word "base" in column A. When i find it i need to cut it and insert it before the row above it. Then i need to add parens to the columns in the base row and center that row. Then I need to add a % sign below the bases. The 3rd lines will have letters, A, B, C in each columns. I need to add parens to these letter. Another problem i have is that there will be blank rows or cells in column A, i don't know how to allow for that.

    Can you help me with this one. Thank you so much.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Find Mean or MEAN or mean (Excel xp)

    I'm a unclear on exactly what you want; can you post a simple "before and after" example?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Mean or MEAN or mean (Excel xp)

    The data comes to me like the attached before sheet. I need to run a macro to make it like the after sheet.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Find Mean or MEAN or mean (Excel xp)

    On your 'after' sheet your numbers become formatted as text, which is unusual; did you want them instead to be negative? This code sets them as text. Please test against some real data, backed up in case of problems.

    Sub FlipBase()
    Dim rngTC As Range, rngCell As Range, rngCellT As Range
    Dim intC As Integer
    On Error Resume Next
    Set rngTC = ActiveSheet.Columns("A:A").SpecialCells(xlCellType Constants, 2)
    If rngTC Is Nothing Then Exit Sub
    For Each rngCell In rngTC
    If InStr(LCase(rngCell.Value), "base") > 0 Then
    If InStr(LCase(rngCell.Value), "%") = 0 Then
    For Each rngCellT In Range(rngCell.Offset(0, 1), rngCell.Offset(-1, 3))
    With rngCellT
    .NumberFormat = "@"
    .HorizontalAlignment = xlCenter
    .Value = "(" & .Value & ")"
    End With
    Next rngCellT
    rngCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
    rngCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
    For Each rngCellT In Range(rngCell.Offset(1, 1), rngCell.Offset(1, 3))
    With rngCellT
    .NumberFormat = "@"
    .HorizontalAlignment = xlCenter
    .Value = "'%"
    End With
    Next rngCellT
    rngCell.Offset(-1, 0).EntireRow.Copy rngCell.Offset(2, 0)
    rngCell.Offset(-1, 0).EntireRow.Delete Shift:=xlUp
    End If
    End If
    Next rngCell
    Range("A1").Select
    End Sub

    No prizes for clear coding here. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Mean or MEAN or mean (Excel xp)

    goodie, goodie. this might help me do several others also. Thank you very much. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Mean or MEAN or mean (Excel xp)

    It's me again. What if i have more than 3 columns and don't really know how many there will be? If i know that

    lLastCol = ActiveSheet.Range("IV1").End(xlToLeft).Column

    how can i integrate this into the macro? thanks

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Find Mean or MEAN or mean (Excel xp)

    This should handle your questions on the first 15 characters and varying number of columns, changed lines in red (note I had to wrap a couple of the lines of code)

    Sub FlipBase()
    'searches for BASE in column A and moves, adds % sign
    Dim rngTC As Range, rngCell As Range, rngCellT As Range
    Dim intC As Integer
    On Error Resume Next
    Set rngTC = ActiveSheet.Columns("A:A").SpecialCells(xlCellType Constants, 2)
    If rngTC Is Nothing Then Exit Sub
    For Each rngCell In rngTC
    <font color=red> If InStr(LCase(Left(rngCell.Value, 15)), "base") > 0 Then</font color=red>
    If InStr(LCase(rngCell.Value), "%") = 0 Then
    <font color=red> For Each rngCellT In Range(rngCell.Offset(0, 1), rngCell.Offset(-1, _
    rngCell.Offset(0, 256 - ActiveCell.Column).End(xlToLeft).Column - 1))</font color=red>

    With rngCellT
    .NumberFormat = "@"
    .HorizontalAlignment = xlCenter
    .Value = "(" & .Value & ")"
    End With
    Next rngCellT
    rngCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
    rngCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
    <font color=red> For Each rngCellT In Range(rngCell.Offset(1, 1), rngCell.Offset(1, _
    rngCell.Offset(0, 256 - ActiveCell.Column).End(xlToLeft).Column - 1))</font color=red>

    With rngCellT
    .NumberFormat = "@"
    .HorizontalAlignment = xlCenter
    .Value = "'%"
    End With
    Next rngCellT
    rngCell.Offset(-1, 0).EntireRow.Copy rngCell.Offset(2, 0)
    rngCell.Offset(-1, 0).EntireRow.Delete Shift:=xlUp
    End If
    End If
    Next rngCell
    Range("A1").Select
    Set rngTC = Nothing
    End Sub

    Also, parts of the code are overkill to prevent problems in case the source column is other than column A.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find Mean or MEAN or mean (Excel xp)

    ok, i'll work thru it. thank you, thank you, thank you <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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