+ Reply to Thread
Results 1 to 6 of 6

Find and Replace Values in Range

  1. #1
    ThatGirlinMS
    Guest

    Find and Replace Values in Range

    I have a workbook with serveral sheets that has numbers formatted as
    'General' throughout in varying places. The worksheets are of no
    specific width or length.

    I need to loop through all sheets looking for cells containing data
    within a range (ex. 167 - 450) and change to numbers (ex subtract one
    from the value)


    any ideas? I thought there has to be a better way than looping through
    EVERY cell in EVERY sheet.

    (>= 167 and <= 450) = (value - 1)

    Thanks!


  2. #2
    Don Guillett
    Guest

    Re: Find and Replace Values in Range

    how about something like this

    for each ws in worksheets
    for each c in range("a167:a450")
    if c>=167 and c<=450 then c.value=c-1
    next c
    next ws
    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "ThatGirlinMS" <heather.pike@nmhs.net> wrote in message
    news:1149889139.125297.128630@g10g2000cwb.googlegroups.com...
    >I have a workbook with serveral sheets that has numbers formatted as
    > 'General' throughout in varying places. The worksheets are of no
    > specific width or length.
    >
    > I need to loop through all sheets looking for cells containing data
    > within a range (ex. 167 - 450) and change to numbers (ex subtract one
    > from the value)
    >
    >
    > any ideas? I thought there has to be a better way than looping through
    > EVERY cell in EVERY sheet.
    >
    > (>= 167 and <= 450) = (value - 1)
    >
    > Thanks!
    >




  3. #3
    ThatGirlinMS
    Guest

    Re: Find and Replace Values in Range


    Don Guillett wrote:
    > how about something like this
    >
    > for each ws in worksheets
    > for each c in range("a167:a450")
    > if c>=167 and c<=450 then c.value=c-1
    > next c
    > next ws
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "ThatGirlinMS" <heather.pike@nmhs.net> wrote in message
    > news:1149889139.125297.128630@g10g2000cwb.googlegroups.com...
    > >I have a workbook with serveral sheets that has numbers formatted as
    > > 'General' throughout in varying places. The worksheets are of no
    > > specific width or length.
    > >
    > > I need to loop through all sheets looking for cells containing data
    > > within a range (ex. 167 - 450) and change to numbers (ex subtract one
    > > from the value)
    > >
    > >
    > > any ideas? I thought there has to be a better way than looping through
    > > EVERY cell in EVERY sheet.
    > >
    > > (>= 167 and <= 450) = (value - 1)
    > >
    > > Thanks!
    > >


    Nope, that's not the solution to my question. I have to look at EVERY
    cell in EVERY sheet. Then I have to see if the value in that cell is
    between a couple of values (ex. 167 - 450) and perform an action
    accordingly (ex subtract one from the value). I have a
    Selection.SpecialCells(xlCellTypeConstants).Select which selects all
    cells with a constant value within. Is there a way to loop through
    every cell in the selection?


  4. #4
    ThatGirlinMS
    Guest

    Re: Find and Replace Values in Range


    Don Guillett wrote:
    > how about something like this
    >
    > for each ws in worksheets
    > for each c in range("a167:a450")
    > if c>=167 and c<=450 then c.value=c-1
    > next c
    > next ws
    > --
    > Don Guillett
    > SalesAid Software
    > dguillett1@austin.rr.com
    > "ThatGirlinMS" <heather.pike@nmhs.net> wrote in message
    > news:1149889139.125297.128630@g10g2000cwb.googlegroups.com...
    > >I have a workbook with serveral sheets that has numbers formatted as
    > > 'General' throughout in varying places. The worksheets are of no
    > > specific width or length.
    > >
    > > I need to loop through all sheets looking for cells containing data
    > > within a range (ex. 167 - 450) and change to numbers (ex subtract one
    > > from the value)
    > >
    > >
    > > any ideas? I thought there has to be a better way than looping through
    > > EVERY cell in EVERY sheet.
    > >
    > > (>= 167 and <= 450) = (value - 1)
    > >
    > > Thanks!
    > >


    Nope, that's not the solution to my question. I have to look at EVERY
    cell in EVERY sheet. Then I have to see if the value in that cell is
    between a couple of values (ex. 167 - 450) and perform an action
    accordingly (ex subtract one from the value). I have a
    Selection.SpecialCells(xlCellTypeConstants).Select which selects all
    cells with a constant value within. Is there a way to loop through
    every cell in the selection?


  5. #5
    ThatGirlinMS
    Guest

    Re: Find and Replace Values in Range


    ThatGirlinMS wrote:
    > Don Guillett wrote:
    > > how about something like this
    > >
    > > for each ws in worksheets
    > > for each c in range("a167:a450")
    > > if c>=167 and c<=450 then c.value=c-1
    > > next c
    > > next ws
    > > --
    > > Don Guillett
    > > SalesAid Software
    > > dguillett1@austin.rr.com
    > > "ThatGirlinMS" <heather.pike@nmhs.net> wrote in message
    > > news:1149889139.125297.128630@g10g2000cwb.googlegroups.com...
    > > >I have a workbook with serveral sheets that has numbers formatted as
    > > > 'General' throughout in varying places. The worksheets are of no
    > > > specific width or length.
    > > >
    > > > I need to loop through all sheets looking for cells containing data
    > > > within a range (ex. 167 - 450) and change to numbers (ex subtract one
    > > > from the value)
    > > >
    > > >
    > > > any ideas? I thought there has to be a better way than looping through
    > > > EVERY cell in EVERY sheet.
    > > >
    > > > (>= 167 and <= 450) = (value - 1)
    > > >
    > > > Thanks!
    > > >

    >
    > Nope, that's not the solution to my question. I have to look at EVERY
    > cell in EVERY sheet. Then I have to see if the value in that cell is
    > between a couple of values (ex. 167 - 450) and perform an action
    > accordingly (ex subtract one from the value). I have a
    > Selection.SpecialCells(xlCellTypeConstants).Select which selects all
    > cells with a constant value within. Is there a way to loop through
    > every cell in the selection?


    I solved it myself. Thank's for viewing!

    Sub callme()
    Application.ScreenUpdating = False
    Dim sheetcnt As Integer
    sheetcnt = ActiveWorkbook.Sheets.Count
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    MsgBox "Working on sheet " & ActiveSheet.Name

    Selection.SpecialCells(xlCellTypeConstants).Select

    Dim oCell As Range
    For Each oCell In Selection
    If IsNumeric(oCell.Value) Then
    If oCell.Value >= 167 And oCell.Value <= 450 Then
    oCell.Value = (oCell.Value - 1)
    oCell.Font.Bold = True
    End If
    If oCell.Value >= 475 And oCell.Value <= 595 Then
    oCell.Value = (oCell.Value + 2)
    oCell.Font.Bold = True
    End If
    If oCell.Value >= 596 And oCell.Value <= 805 Then
    oCell.Value = (oCell.Value + 5)
    oCell.Font.Bold = True
    End If
    If oCell.Value >= 812 And oCell.Value <= 814 Then
    oCell.Value = (oCell.Value - 1)
    oCell.Font.Bold = True
    End If
    If oCell.Value >= 815 And oCell.Value <= 819 Then
    oCell.Value = (oCell.Value + 2)
    oCell.Font.Bold = True
    End If
    End If
    Next
    Next
    MsgBox "Done"
    End Sub


  6. #6
    Don Guillett
    Guest

    Re: Find and Replace Values in Range

    I'm glad you solved it, all by yourself....
    A simple mod to my "something like" would have done it, as well.
    BTW You do NOT need to activate the worksheet or select the special cells.

    --
    Don Guillett
    SalesAid Software
    dguillett1@austin.rr.com
    "ThatGirlinMS" <heather.pike@nmhs.net> wrote in message
    news:1150121275.200526.47140@c74g2000cwc.googlegroups.com...
    >
    > ThatGirlinMS wrote:
    >> Don Guillett wrote:
    >> > how about something like this
    >> >
    >> > for each ws in worksheets
    >> > for each c in range("a167:a450")
    >> > if c>=167 and c<=450 then c.value=c-1
    >> > next c
    >> > next ws
    >> > --
    >> > Don Guillett
    >> > SalesAid Software
    >> > dguillett1@austin.rr.com
    >> > "ThatGirlinMS" <heather.pike@nmhs.net> wrote in message
    >> > news:1149889139.125297.128630@g10g2000cwb.googlegroups.com...
    >> > >I have a workbook with serveral sheets that has numbers formatted as
    >> > > 'General' throughout in varying places. The worksheets are of no
    >> > > specific width or length.
    >> > >
    >> > > I need to loop through all sheets looking for cells containing data
    >> > > within a range (ex. 167 - 450) and change to numbers (ex subtract one
    >> > > from the value)
    >> > >
    >> > >
    >> > > any ideas? I thought there has to be a better way than looping
    >> > > through
    >> > > EVERY cell in EVERY sheet.
    >> > >
    >> > > (>= 167 and <= 450) = (value - 1)
    >> > >
    >> > > Thanks!
    >> > >

    >>
    >> Nope, that's not the solution to my question. I have to look at EVERY
    >> cell in EVERY sheet. Then I have to see if the value in that cell is
    >> between a couple of values (ex. 167 - 450) and perform an action
    >> accordingly (ex subtract one from the value). I have a
    >> Selection.SpecialCells(xlCellTypeConstants).Select which selects all
    >> cells with a constant value within. Is there a way to loop through
    >> every cell in the selection?

    >
    > I solved it myself. Thank's for viewing!
    >
    > Sub callme()
    > Application.ScreenUpdating = False
    > Dim sheetcnt As Integer
    > sheetcnt = ActiveWorkbook.Sheets.Count
    > Dim ws As Worksheet
    > For Each ws In ActiveWorkbook.Worksheets
    > ws.Activate
    > MsgBox "Working on sheet " & ActiveSheet.Name
    >
    > Selection.SpecialCells(xlCellTypeConstants).Select
    >
    > Dim oCell As Range
    > For Each oCell In Selection
    > If IsNumeric(oCell.Value) Then
    > If oCell.Value >= 167 And oCell.Value <= 450 Then
    > oCell.Value = (oCell.Value - 1)
    > oCell.Font.Bold = True
    > End If
    > If oCell.Value >= 475 And oCell.Value <= 595 Then
    > oCell.Value = (oCell.Value + 2)
    > oCell.Font.Bold = True
    > End If
    > If oCell.Value >= 596 And oCell.Value <= 805 Then
    > oCell.Value = (oCell.Value + 5)
    > oCell.Font.Bold = True
    > End If
    > If oCell.Value >= 812 And oCell.Value <= 814 Then
    > oCell.Value = (oCell.Value - 1)
    > oCell.Font.Bold = True
    > End If
    > If oCell.Value >= 815 And oCell.Value <= 819 Then
    > oCell.Value = (oCell.Value + 2)
    > oCell.Font.Bold = True
    > End If
    > End If
    > Next
    > Next
    > MsgBox "Done"
    > End Sub
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1