IF statement with dynamic range VBA ─ Excel-CodePudding

Good afternoon friends, I need your support because when I execute my macro in VBA, the results show the formula.

What I want is to be able to carry out an IF STATEMENT where only the conditions that I have put in the formula give me as a result, that is, only the text "Approved" or "Denied".

Here is my code that I currently have:

Sub if_status()

Range("D2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Approved"",""Approved"",""Denied"")"
Selection.AutoFill Destination:=Range("D2:D" & Range("B" & Rows.Count).End(xlUp).Row)
Range(ActiveCell, ActiveCell.End(xlDown)).Select

End Sub

I have tried this new code but it won't let me put the "ELSE" and it finally gives me an error. And neither if how to do the autocomplete until the last row:

Sub ANOTHER()

Range("D2").Select

If Range("C2").Value = "" Then Range("e2").Value = "Denied"
Else
    Range("D2").Value = "Approved"
End If
End Sub

CodePudding user response：

If you put something after Then on the same line, you cannot continue it on a new line.

The Then part needs its own line.

Sub ANOTHER()
    Range("D2").Select
    If Range("C2").Value = "" Then 
        Range("e2").Value = "Denied"
    Else
        Range("D2").Value = "Approved"
    End If
End Sub

If I understand correctly, your other question can be done with this one line.

Range("C1:C" & Range("B" & Rows.Count).End(xlUp).Row).Formula2R1C1 = "=IF(RC[-1]=""Approved"",""Approved"",""Denied"")"

This assumes you have your B and C columns defined correctly.

With your code you are determining the last row using Column B, and then setting Column C based on Column B. Is that correct?

CodePudding user response：

I found the way the formula is not displayed in the results and the USER can only see values. Additionally, the code validates until the last one that contains a value.

The code validates until the last row of column [B] and the IF statement validates the data of column [C] and leaves the result in column [D] only values do not show the formula..

Sub another_code()

Dim cell As Range
Set rng = Range("C2:C" & Range("B" & Rows.Count).End(xlUp).Row)

For Each cell In rng
    If cell.Value <> "" Then
        cell.Offset(0, 1).Value = "Approved"
    Else
        cell.Offset(0, 1).Value = "Denied"
    End If
Next cell

End Sub

CodePudding user response：

Dim ws As Worksheet

Set ws = ActiveSheet 'always specify a worksheet
With ws.Range("D2:D" & ws.Cells(Rows.Count, "B").End(xlUp).Row)
    .Formula = "=IF(C2=""",""Denied"",""Approved"")" 'apply formula
    .Value = .Value                                           'convert to value
End With