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

    Find base and add asterisks (Excel xp)

    I need to search thru column A for the word Base or BASE. On the row I need to add an "*" to numbers less than 100 and an "**" to numbers less than 30. How do i search thru each row for Base and then add the asterisks in a macro. Thanks for the help.

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Here's a sample .xls of how to search through a given column for 'base'. It adds an asterick to the previous col (A in my case). I mis-read <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> your post when I wrote my solution so it doesn't quite do what you want, but you could modify it to do the counting part. (I was too lazy to change the code to make it work exactly as you needed.... <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15>

    I didn't know if you wanted to test for only 'base' or also for things like 'baseball' where the word 'base' is in the string. I did it both ways.

    To put the asterick in the same cell as the found word, you'd change the code to do this:
    <pre>rng.cells(k,1) = "*" & rng.cells(k,1)</pre>

    This overwrites the existing cell with the asterick and then appends the same string.

    HTH,
    Deb
    Attached Files Attached Files

  4. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Thank you for the help.

    I guess i am confused, i need to search for the word "Base" or "BASE" in column A. When i find it i need to look in that row at the numbers. So in my example i've attached B3 would become 40* and D3 would become 22**. Also C6 would become 15** and D6 would be 49*. This is just a small sample, the real one will have 2000 rows. thanks
    Attached Files Attached Files

  5. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Ok, I made the changes to what I already had in the first workbook (which just searched for 'base' and added * to that row).

    Your required number test is:
    add * if n < 30
    add ** if 30 >= n < 100

    I added a button to your new 'data' sheet that runs the code (which is in its own module). It first clears all * and ** from all 'base' cells and then goes through each row for a match. If one is found, it then tests the values for the next 3 cols to see if they fall within your criteria. If yes, they get the * or ** pre-pended to their value. <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>

    Deb <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Attached Files Attached Files

  6. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Ooops, <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> I just posted my solution and noticed that you wanted the * or ** to appear after the number instead of before like I did (22* not *22).

    I'll leave it as an exercise to you to change the code to switch the position of the * character.

    Deb

  7. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Thank you very much. Wow this is harder than i thought it would be. I figured out how to concatenante the asterisks to the end. One thing i don't understand is...

    Set rng = ActiveSheet.Range("A1")
    Set rng = Range(rng, rng.End(xlDown)) ' find last non-empty row

    How come you are re-setting rng from A1 to the end of the range? When i use the macro on a large dataset that has 1500 rows it sets the rng as 4 instead of 1500. I think it's because there is a blank row at line 5 so it thinks the end is at row 4. How can i get around that? Thank you for all the help on this.

  8. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Try using:

    <pre> Set rng = ActiveSheet.Range("A1").Offset(ActiveSheet.UsedRan ge.Row+ActiveSheet.UsedRange.Rows.Count,0).End(xlU p)
    </pre>

    Legare Coleman

  9. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Glad it worked out well for you. I wrote the code to be more flexible so that if you change the search term ('base') or the numeric criteria, you just have to change those constant values and re-run the code. One thing you didn't say in your requirements was whether or not you needed to differentiate between terms like 'base' and 'baseball' are these the same? In my first version I had two routines that did both type of tests (exact match or any match).

    To answer your question about those two lines of code... Your problem statement said that the text is in col A but I don't know how many rows need to be tested (15 or 150 or 1500) so I had to programmatically determine where the last row was. The code assumes you start at cell A1 and it goes until A'n' so that code determines the start and end points to be tested. If you know you'll only ever need to test a fixed number of rows, 1500 for example, you could hard-code it to :

    Set rng = range("A1:A1500")

    My code didn't assume a fixed set of rows, however.
    <pre>' define starting point of col to test for search text
    Set rng = ActiveSheet.Range("A1")
    ' calculate ending row, same as cntr-end on A1, it stops at last populated row
    Set rng = Range(rng, rng.end(xldown)) </pre>

    So now 'rng' points to an area of 'n' rows in col A. This is the search area and this is what the next part of the code uses to loop through for testing.
    This code only works if your data is in contiguous rows, no empty rows.

    Yes it is resetting the variable 'rng' as you said but I want 'rng' to contain the address of all populated rows in col A and the function Range() requires two arguments; a start point and an end point.

    Thnx, Deb

  10. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Yup, that'll work too but this poster is new to VBA it seems and I'm sure that line would be very confusing to figure out. I tend to prefer shorter statements that are still readable a week after I wrote the code <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Deb

  11. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    Ok, it's too complicated for me so I did this so i could work it out in my mind. I got stuck on the lines where i put >>>. Can anyone help me. Thank you.

    Sub FindBasePutAsterisk()
    Dim rng As Range, k As Integer, j As Integer
    Dim LastRow As Long

    strFIND = "Base"
    LastRow = 1000
    maxCol = 10

    Application.ScreenUpdating = False
    For k = 1 To LastRow
    If Left(Range("A" & k).Value, 4) = strFIND Then
    For j = 1 To maxCol
    >>> If IsLowRange(rng.Cells(k, j + 1)) Then 'when it gets here the value of the A cell has Base in it so i want to look at the value of the next col to the right
    >>> rng.Cells(k, j + 1) = rng.Cells(k, j + 1) & "**"
    >>> ElseIf IsHighRange(rng.Cells(k, j + 1)) Then
    >>> rng.Cells(k, j + 1) = rng.Cells(k, j + 1) & "*"
    End If
    Next j
    End If
    Next k

    Application.ScreenUpdating = True
    MsgBox ("Done")
    End Sub

    ' is the given number in the < 30 range?
    Public Function IsLowRange(dat As Integer) As Boolean
    IsLowRange = False
    If dat < 30 Then IsLowRange = True
    End Function

    ' is the given number in the 31 > n < 100 range?
    Public Function IsHighRange(dat As Integer) As Boolean
    IsHighRange = False
    If dat >= 30 + 1 And dat < 100 Then IsHighRange = True
    End Function

  12. #11
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find base and add asterisks (Excel xp)

    I use two functions to test for your numeric values. You wanted to put * or ** on all values that were within a given range (< 30 or < 100).

    IsLowRange() is a function that returns True/False for the given value. If it's True than the value of the current cell is within your required low range (< 30) and it gets an *.

    Same for IsHighRange() but it returns True if the given value is in the required high range (x >= 30 and x < 100).

    I could of done the test in the same line of code but that makes it more complex and messy to read plus it's not good technique to cram everything in one module. If you ever needed to change your definition of low or high, you just need to change those two subs (or the constants I provided).

    The part about rng.cells(k,,j+1) is looking at the cols (B, C, D) where the 'base' text was found. Look in the help for definition of Cells(), it needs a row and col as input. k= the counter for the rows and j= the counter for the cols. Cells() starts counting at 1 so the first time through I'm looking at the cell at rng.cells(1, 2) which is in the current row (k=1) and 2nd column (j=2) so that's the cell immediately to the right of the 'base' cell.
    <pre>A5='base' ' rng.cells(1,1)
    B5=22 ' rng.cells(1,2)
    C5=44 ' rng.cells(1,3)
    D5=212 ' rng.cells(1,4)</pre>

    Example: If 'base' was found in cell A5 then I need to look at cells B5, C5 and D5 to test their values to see if I need to put * or ** in them. It's in a For loop as you see so I loop through each row in the range (size determined on-the-fly) and then when a match is found, I march across that row through each column (3 cols in your case) hence the inner For loop (j as the index).

    In words the loop is this:<pre>For All_rows_in-Search_Range
    current_cell = current_row + current_col
    if current_cell_value < 30 then
    append_one_star_to_current_cell
    else if current_cell_value >= 30 or current_cell_value < 100 then
    append_two_stars_to_current_cell
    end if
    goto_next row
    end ForLoop</pre>


    To better understand what's happening, I recommend using the de###### and stepping through the code one line at a time. I also suggest adding debug.print statements which display in the Immediate window of the editor. I did this myself when I was writing it to make sure I was on the right cell. After the print statements showed me that I was doing the right thing, I replaced them with real code. In most of my real apps though,I tend to just comment out the debug.print statements instead of removing them as they're often useful when things break (and code does break sometimes).

    Deb

Posting Permissions

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