[Solved]-Change the values in a column depending upon different criteria-VBA Excel

There are quite a few ways to go about what you're trying to do. For what it's worth, this is how I would go about it. You had a few additional variables you didn't need, and your 'row' variable wasn't assigned a value at all.

Sub test2()
Dim lastRow As Long, _
    i As Long

Application.ScreenUpdating = False

With Sheet1
    lastRow = .Cells(Rows.Count, "D").End(xlUp).row

    For i = 2 To lastRow
        If .Cells(i, 1).Value = "Bol" Then
            .Cells(i, 4).Value = .Cells(i, 4).Value * 1.19
        End If

        If .Cells(i, 1).Value = "Amazon" Then
            .Cells(i, 4).Value = .Cells(i, 4).Value * 1.2
        End If

    Next i
End With


Application.ScreenUpdating = True

End Sub

I kept is relatively simple, so hopefully you can follow what's going on. If you have a lot of "If" statements, it may be cleaner to use VBAs "Select Case".

Also the text strings as you have them set up are case sensitive. "Bol" does not equal "bol" maybe that doesn't matter, but something to be aware of. If the string you pass it is "amazon" it will not pass the 'If' test.

Another assumption I made was that your data is on Sheet1. You should get in the habit of fully qualifying your ranges, it will make your life a lot easier as your code gets more complicated.

Last bit, I'm assuming the values in column D are all numbers. If there is text in there, you may run in to problems multiplying it.

Good luck!

You can simplify your code, and make it easier to read, by looping trough column A instead of column D and using the If/ElseIf statement to test each cell for either of the two conditions. By setting your range and defining c as a range variable for each cell in the range, you only have to loop through each cell and test for the two conditions. If the cell contains Bol use the Offset property to multiple the current value in column D by 1.19; ElseIf the cell contains Amazon use the Offset property to multiple the current value in column D by 1.2. Comments provide in the code.

Application.ScreenUpdating = False

'use the With statement to define your workbook and sheet, change as needed
'Note: "ThisWorkbook" identifies the workbook which contains this code
With ThisWorkbook.Sheets("Sheet1") 

    'Define the range you want to loop through, using the column you want to test
    Dim rng As Range: Set rng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) 

    'Define the variable for each cell-range
    Dim c As Range 

       'loop through each "c" in the range and if one of the conditions are met 
        For Each c In rng 
            If c = "Bol" Then

                'then use the "Offset property" to modify the value in column D
                c.Offset(, 3) = c.Offset(, 3).Value * 1.19 

            ElseIf c = "Amazon" Then
                c.Offset(, 3) = c.Offset(, 3).Value * 1.2
            End If
        Next c
End With

Application.ScreenUpdating = True

In-Place Modification

All the solutions have one common issue: you can use them only once. If you need to change the values after adding new records (rows) you should consider adding another column with the initial values so the code could be written to identify what has already been changed and what not. But that's for another question.

Your Sub Solution

You actually had only one serious mistake in two-three places.

Instead of row in the If statements you should have used c.Row and you could have removed Dim row As Integer:

Sub Test_Almost_Fixed()
Application.ScreenUpdating = False
Dim startrow As Integer
Dim c As Range
Dim Lastrow As Long
Application.ScreenUpdating = False

Lastrow = Cells(Rows.Count, "D").End(xlUp).row
For Each c In Range("D2:D" & Lastrow)


If Cells(c.Row, 1) = "Bol" Then
c.Value = c.Value * 1.19
End If

If Cells(c.Row, 1) = "Amazon" Then
c.Value = c.Value * 1.2
End If

Next
Application.ScreenUpdating = True
End Sub

Additionally after getting rid of the extra Application.ScreenUpdating = False and the Dim startrow As Integer and some further cosmetics, you could have had something like this:

Sub Test_Fixed()
    
    Dim c As Range
    Dim Lastrow As Long
    
    Lastrow = Cells(Rows.Count, "D").End(xlUp).row
    
    Application.ScreenUpdating = False
    
    For Each c In Range("D2:D" & Lastrow)
        
        If Cells(c.Row, 1) = "Bol" Then
            c.Value = c.Value * 1.19
        End If
        
        If Cells(c.Row, 1) = "Amazon" Then
            c.Value = c.Value * 1.2
        End If
        
    Next
    
    Application.ScreenUpdating = True

End Sub

A More Complex Sub Solution

Use the following for the ActiveSheet in a standard module (e.g. Module1). For a particular sheet you can place it in a sheet module (e.g. Sheet1) or create a button on the sheet.

Tip: When you have such a simple (short, fast) code and especially when you're using a Button to run it (in a 'one-time operation code'), it is good practice to use a MsgBox at the end of the code to actually know that the code has run and to prevent accidentally pressing the Button more than once.

Option Explicit

Sub Test()
    Const Proc As String = "Test"
    On Error GoTo cleanError
    
    ' Define Constants.
    Const FirstRow As Long = 2
    Const SourceColumn As Variant = 1 ' e.g. 1 or "A"
    Const TargetColumn As Variant = 4 ' e.g. 4 or "D"
    Dim Criteria As Variant   ' Add more values.
    Criteria = Array("Bol", "Amazon")
    Dim Multiplier As Variant ' Add more values.
    Multiplier = Array(1.19, 1.2)
    
    ' Check if Criteria and Multiplier Arrays have the same number
    ' of elements (columns).
    Dim ubCM As Long: ubCM = UBound(Criteria)
    If UBound(Multiplier) <> ubCM Then Exit Sub
    
    ' Write Source and Target Ranges to Source and Target Arrays.
    Dim rng As Range
    ' Define Last Non-Empty Cell.
    Set rng = Columns(TargetColumn).Find("*", , xlValues, , , xlPrevious)
    ' Check if Target Column is empty.
    If rng Is Nothing Then Exit Sub
    ' Check if the row of Last Non-Empty Cell is above FirstRow.
    If rng.Row < FirstRow Then Exit Sub
    Dim Target As Variant
    ' Write Target Range to Target Array.
    Target = Range(Cells(FirstRow, TargetColumn), rng).Value
    Set rng = Nothing
    Dim ubST As Long: ubST = UBound(Target)
    Dim Source As Variant
    ' Write Source Range to Source Array.
    Source = Cells(FirstRow, SourceColumn).Resize(ubST).Value
    
    ' Modify Target Array.
    Dim i As Long, j As Long
    ' Loop through elements (rows) of Source and Target Arrays.
    For i = 1 To ubST
        ' Loop through elements (columns) of Criteria and Multiplier Arrays.
        For j = 0 To ubCM
            ' Check if the value in current element (row) of Source Array
            ' matches the value of current element (column) in Criteria Array.
            If Source(i, 1) = Criteria(j) Then
                ' Modify value in current element (row) of Target Array
                ' by multiplying it with the value of current element (column)
                ' of Multiplier Array.
                Target(i, 1) = Target(i, 1) * Multiplier(j)
                ' Since a match is found, there is no need to loop anymore.
                Exit For
            End If
        Next j
    Next i
    Erase Source
    
    ' Write values of Target Array to Target Range.
    Cells(FirstRow, TargetColumn).Resize(ubST).Value = Target
    Erase Target

    ' Inform user.
    MsgBox "Data copied.", vbInformation, "Success"

    Exit Sub

cleanError:
    MsgBox "An unexpected error occurred in '" & Proc & "'." & vbCr _
         & "Run-time error '" & Err.Number & "':" & vbCr & Err.Description _
           , vbCritical, Proc & " Error"
End Sub

An Event Solution

To make it automatically change the values in column D for each change of a value in column A you can place the following code into the sheet module (e.g. Sheet1):

Option Explicit
    
Private Const SOURCE_COLUMN As Variant = 1 ' e.g. 1 or "A"
Private Const TARGET_COLUMN As Variant = 4 ' e.g. 4 or "D"

Private Sub sdfWorksheet_Change(ByVal Target As Range)
    Const Proc As String = "Worksheet_Change"
    On Error GoTo cleanError
    If Intersect(Columns(SOURCE_COLUMN), Target) Is Nothing Then Exit Sub
    
    Const FirstRow As Long = 2
    
    Dim rng As Range
    Set rng = Columns(TARGET_COLUMN).Find("*", , xlValues, , , xlPrevious)
    If rng Is Nothing Then Exit Sub
    If rng.Row < FirstRow Then Exit Sub
    Set rng = Cells(FirstRow, SOURCE_COLUMN).Resize(rng.row - FirstRow + 1)
    
    If Intersect(rng, Target) Is Nothing Then Exit Sub
    Dim cel As Range
    Application.Calculation = xlCalculationManual     ' -4135
    For Each cel In Target.Cells
        TestChange cel
    Next cel
    
CleanExit:
    Application.Calculation = xlCalculationAutomatic  ' -4105

Exit Sub

cleanError:
    MsgBox "An unexpected error occurred in '" & Proc & "'." & vbCr _
         & "Run-time error '" & Err.Number & "':" & vbCr & Err.Description _
           , vbCritical, Proc & " Error"
    On Error GoTo 0
    Resume CleanExit
End Sub

Private Sub TestChange(SourceCell As Range)
    Const Proc As String = "TestChange"
    On Error GoTo cleanError

    Dim Criteria As Variant
    Criteria = Array("Bol", "Amazon")
    Dim Multiplier As Variant
    Multiplier = Array(1.19, 1.2)
 
    Dim ubCM As Long: ubCM = UBound(Criteria)
    If UBound(Multiplier) <> ubCM Then Exit Sub

    Application.ScreenUpdating = False
    Dim TargetCell As Range, j As Long
    For j = 0 To ubCM
        If SourceCell.Value = Criteria(j) Then
            Set TargetCell = Cells(SourceCell.row, TARGET_COLUMN)
            TargetCell.Value = TargetCell.Value * Multiplier(j)
            Exit For
        End If
    Next j
    
CleanExit:
    Application.ScreenUpdating = True
    
    Exit Sub
    
cleanError:
    MsgBox "An unexpected error occurred in '" & Proc & "'." & vbCr _
         & "Run-time error '" & Err.Number & "':" & vbCr & Err.Description _
           , vbCritical, Proc & " Error"
    On Error GoTo 0
    Resume CleanExit
End Sub

I just provide this variant. It is working with array, so theoretically it is very quick. Probably no need to turn off the screen updating.

Sub test()
    Dim lastRow As Long, i As Long
    With Sheet1
        lastRow = .Cells(Rows.Count, "D").End(xlUp).row
        Dim vA As Variant 'Represents A2-A lastrow
        vA = .Range("A2").Resize(lastRow - 1).Value
        Dim vb As Variant 'Represents D2-D lastrow
        vb = .Range("D2").Resize(lastRow - 1).Value
        i = 0
        Dim v As Variant
        For Each v In vA
            i = i + 1
            If v = "Bol" Then
                vb(i, 1) = vb(i, 1) * 1.19
            ElseIf v = "Amazon" Then
                vb(i, 1) = vb(i, 1) * 1.2
            End If
        Next v
        .Range("D2").Resize(lastRow - 1).Value = vb ' Writing the values to the D column
    End With
End Sub