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

Topic

New Reply

Another question involving ranges, along the lines of this thread.

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?

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
        'here is where the change base rate and change OJT would belong
    Next
    Selection.AutoFilter
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

Can someone help me? Please.

Reply | Quote

Replies

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).

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

Andrew C

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote