IF formula in Macro for entire row

[sofiarashid]

Hi
I am working on a project towards my yellow belt and have been set a task to write a Macro to format a regular report. I hit an obstacle and am struggling how to insert an IF formula for the following-

If=(cell A<cell B,”QB”,””) – i.e if value in cell A if less than value in cell B bring back statement “QB” (in cell C) if not leave Blank.

Also, I want to do this for the entire row but as the report is different size each month, I am unable to give an exact number of rows.

Please help :crying:

[SamT]

I want to do this for the entire row but as the report is different size each month, I am unable to give an exact number of rows.
Assuming you meant "for the entire column"


Dim Cel As Range
For each Cel in Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
With Cel
If .Value < .Offset(,1) Then
.Offset(,2) = "QB"
Else
.Offset(,2) = ""
End If
End With

[mdmackillop]

I assume you are refering to a column. not Rows

Sub FillFormula()
Dim x As Long, y As Long
x = 1
y = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(x, 3), Cells(y, 3)).FormulaR1C1 = "=IF(RC[-2]<RC[-1],""QB"","""")"
End Sub

[sofiarashid]

Thanks Sam for the reply

sorry and yes I did mean row :)

I have tried the above and was getting an error to input End Sub. so I have but I keep getting the error - 'Compile Error - For without next'


Dim Cel As Range
For each Cel In Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
With Cel
If .Value < .Offset(,1) Then
.Offset(,2) = "QB"
Else
.Offset(,2) = ""
End If
End With
End Sub


I am totally new to the VBA world which doesn't help!

Thanks in advance

[SamT]

Dim Cel As Range
For each Cel In Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)) '<<<<
With Cel '<<<
If .Value < .Offset(,1) Then '<<
.Offset(,2) = "QB"
Else
.Offset(,2) = ""
End If '<<
End With '<<<
Next Cel '<<<<

[sofiarashid]

Dim Cel As Range
For each Cel In Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)) '<<<<
With Cel '<<<
If .Value < .Offset(,1) Then '<<
.Offset(,2) = "QB"
Else
.Offset(,2) = ""
End If '<<
End With '<<<
Next Cel '<<<<


Sam

This isn't working :( ... I want this to populate the entire Col A with the IF formula. However, the above is only changing cell C2

thanks

[mdmackillop]

Try post #3

[sofiarashid]

My Bad! it does!

Thanks... you're a STAR :)

[sofiarashid]

thanks for your help :)

[SamT]

I want this to populate the entire Col A with the IF formula.
Put the formula desired in A1, then Select the entire column and press Ctrl+D