VBA current cell value = another cell value if criteria met

[jonnyjonjon]

Hello, I've written the following program,

Sub task3V3P3()


Dim rng As Range
Dim cell As Range


    Set rng = Range("Q:Q")
    
    If Not rng Is Nothing Then
        For Each cell In rng.Cells
            If cell.Offset(0, -3).Value = "Approved" Or "QUOT" Then
                ActiveCell.Value = cell.Offset(0, 20).Value
            End If
        Next
    End If


End Sub

But it does not seem to work. The idea is for the current cell's value (Column Q) to be the same as another cell's value (Column BH), if the cell in column N's value is = Approved or QUOT. If the value in Column N is neither Approved or QUOT, then the current cell's value will not change at all. I would also want this formula to go on until it detects an empty cell, I think the "If not rng is nothing then" works though, if not, could someone suggest to me an alternative? Thank you so much

[jonnyjonjon]

The errors that I'm getting are,
error1.pngerror2.png

[Paul_Hossler]

OR's don't work that way

If (cell.Offset(0, -3).Value = "Approved")  Or (cell.Offset(0, -3).Value = "QUOT") Then

You don't need the parens around the two pieces, but I like to do it to improve readability

[jonnyjonjon]

OR's don't work that way

If (cell.Offset(0, -3).Value = "Approved")  Or (cell.Offset(0, -3).Value = "QUOT") Then

You don't need the parens around the two pieces, but I like to do it to improve readability

Thanks for the reply, not getting any errors now. But program isn't working like how it should. The values in the current cell becomes "0.00", while the rest of the cells in the column doesn't change at all. Forgot to mention that the values in column BH are from a VLOOKUP, not sure if it affects it or not though.

[Paul_Hossler]

Thanks for the reply, not getting any errors now. But program isn't working like how it should. The values in the current cell becomes "0.00", while the rest of the cells in the column doesn't change at all. Forgot to mention that the values in column BH are from a VLOOKUP, not sure if it affects it or not though.

If you post a small workbook with sample data it will be easier to see

Click [Go Advanced] bottom right and attach a small file

[mdmackillop]

Change Activecell to your variable Cell to enter the values
If you don't restrict "r", the code has to check all 2^20 cells in the column. There are various ways to do this e.g. finding LastRow
Using UCase avoids error due to capitalisation of data.

Sub task3V3P3()
    
    Dim rng As Range
    Dim cell As Range
    With ActiveSheet
    Set rng = Intersect(.UsedRange, .Range("Q:Q"))
    End With
    If Not rng Is Nothing Then
        For Each cell In rng.Cells
            If UCase(cell.Offset(0, -3).Value) = "APPROVED" Or UCase(cell.Offset(0, -3).Value) = "QUOT" Then
                cell.Value = cell.Offset(0, 20).Value
            End If
        Next
    End If
End Sub

Alternative code

Sub task3V3P4()    
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("N:N").SpecialCells(xlCellTypeConstants)
    If Not rng Is Nothing Then
        For Each cell In rng.Cells
            If UCase(cell.Value) = "APPROVED" Or UCase(cell.Value) = "QUOT" Then
                cell.Offset(, 3) = cell.Offset(0, 23).Value
            End If
        Next
    End If
End Sub