Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modifying Row Data in a Range (Excel 97/SR2/VBA)

    Another question involving ranges, along the lines of <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=56355&page=0&view =expanded&sb=5&o=7&part=>this thread.</A>

    I have a range of data (columns A thru J, and rows 1 to 'varies'), with row 1 being a header row. One of the columns (column F) is Pay Code ID, a text field, with entries like 8%, 12%, 4%, OJT, and Not Applicable. One of the things I will be doing is calculating a new base salary after converting the Pay Code ID into a multiplication factor.

    I am looking for a method to cycle through the rows and identify the rows where the value in column F is OJT. For those rows that have OJT in column F, I need to be able to change the existing value in Column E to Column E + $0.50. Then change the column F value from OJT to Not Applicable. For example, in using the below code I can AutoFilter the Range on Column F, but how do I change the values in Column E to $14.30 in row 65, $14.73 in row 373, $13.94 in row 413, and $11.54 in row 523 as shown below?
    <pre>Sub OJTChange()
    Application.ScreenUpdating = False
    Dim oRange As String, strDel3 As String, rowNum As String, varCode
    arrCode = Array("OJT")
    rowNum = ActiveSheet.UsedRange.Rows.Count
    oRange = "A1:J" & rowNum
    For Each varCode In arrCode
    strDel3 = varCode
    Range(oRange).AutoFilter Field:=6, Criteria1:=strDel3
    Range("A2", Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    <font color=red>'here is where the change base rate and change OJT would belong</font color=red>
    Next
    Selection.AutoFilter
    Range("A1").Select
    Application.ScreenUpdating = True
    End Sub</pre>

    Can someone help me? Please.
    Attached Images Attached Images

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Modifying Row Data in a Range (Excel 97/SR2/VBA)

    Steve,

    There is no real advantage in adopting the autofilter facility in this case and the following code should loop through entry in the Shift colum, and where the value is OJT change it to "N/A", and add 0.5 to adjacent cell in the F column (Offset 0,-1).<pre>Sub OJTChange()
    Application.ScreenUpdating = False
    Dim oCell As Range, rngOJT As Range
    Set rngOJT = Range("F1:F" & ActiveSheet.UsedRange.Rows.Count)
    For Each oCell In rngOJT
    If oCell.Value = "OJT" Then
    oCell.Value = "N/A"
    oCell.Offset(0, -1).Value = oCell.Offset(0, -1).Value + 0.5
    End If
    Next
    Application.ScreenUpdating = True
    End Sub</pre>

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modifying Row Data in a Range (Excel 97/SR2/VBA)

    Thank you Andrew, that worked very well. I had an idea it was something that simple, I just didn't know how to get there.

    Also, your previous post causes me to ask two additional questions (just so I can learn). 1) any significance in using the lower case 'o' in front of the word 'cell'? I have seen you do it several times as well as Legare use oRange or oSheets. and 2) what does 'Application.ScreenUpdating" do? why does it need to be set false before the modifications, then back to true after? Just curious.

    Thanks again. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

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

    Re: Modifying Row Data in a Range (Excel 97/SR2/VBA)

    The o in front of some variable names is just a naming convention that many of us use. It identifies the variable as an object variable. I also use i for integer, l for Long Integer, d for Double Floating Point, str for String, dat for Date, etc.

    Setting Application.ScreenUpdating to false stops Excel from updating the screen display. This speeds up the processing of the code and also keeps the screen from flashing intermediate results.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modifying Row Data in a Range (Excel 97/SR2/VBA)

    Sounds like a shortcut method of what I am learning in my VB class. We use int for integer, lng for long, str for string. I understand.

    Thanks for the quick tutorial.

Posting Permissions

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