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,
2057720578

[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