Sleeper: VBA If formula help

[malleshg24]

Hi Team,

I need vba help here, either if formula or case.

In sheet2 under column E I Have numbers.
In column F, i am looking for result.


if Range(E2).value = 4, then F(2).Value = 2
if range(E2).Value = 3, then F(2).value = 1
if the number is other than (3,4) , the same value should be pasted in F1.
exampel if (E2).value = 5, then F(2).value = 5
if (e2).value = 0 , then F(2).value = 0
if (e2).value = 1 , then (f2).value = 1
I wan this formula till ("F2:e"&lr), depending on cell value in column (E)
Please assist.

[offthelip]

have you tried a simple if statement, put this in f2

=IF(E2=3,1,IF(E2=4,2,E2))

[malleshg24]

Thank you so much, above formula worked for me in excel, In
vba when tried its not working.

Dim ws as worksheet
Set ws = worksheets(1)
lr = cells(rows.count,1).end(xlup)
ws.range("f2,f"&lr).formula = "=IF(E2=3,1,IF(E2=4,2,E2))"

Thanks in advance

Regards,
Mallesh

[offthelip]

try this:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
 Cells(i, 6).Formula = "=if(E" & i & "=3,1,if(E" & i & "=4,2,E" & i & "))"
Next i
End Sub

[malleshg24]

Thanks. awesome, it worked for me,

in immediate next column , I need one more if formula, here the result is in text,

Cells(i, 7).Formula = if(F2<=2,"Target Met","Target not met"). how to put this line in vba?.....

Regards,
Mallesh.

[greyangel]

Thanks. awesome, it worked for me,

in immediate next column , I need one more if formula, here the result is in text,

Cells(i, 7).Formula = if(F2<=2,"Target Met","Target not met")

. how to put this line in vba?.....

Regards,
Mallesh.

Could you just do a macro recorder and then do an xlfill

Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
 Range("A3").AutoFill Destination:=Range("A3:A" & Lastrow)

[offthelip]

Here is some vba code that will do that, what is worth learning about this is; it shows you a way around the problem of trying to include double quotes in a string,
I do it by using the chr() function which will return any ascii character by number.

P.S. I will try to remember to code tags, (apologies!!)

Sub test2()
tt = Chr(34)
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastrow
        Cells(i, 6).Formula = "=if(E" & i & "=3,1,if(E" & i & "=4,2,E" & i & "))"
        Cells(i, 7).Formula = "=if(F" & i & "<=2," & tt & "Target Met" & tt & "," & tt & "Target Not Met" & tt & ")"
    Next i
     
End Sub

[mdmackillop]

You can use the macro recorder to provide the VBA formula. Just click in the formula in the formula bar and press enter. Only small adjustment is required to use it. Note the benefit of no looping if large numbers are involved.

'Recorded macto
Sub Macro1()
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=3,1,IF(RC[-1]=4,2,RC[-1]))"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<=2,""Target Met"",""Target Not Met"")"
Range("G3").Select
End Sub


'Adjusted macro
Sub Test()
Dim r As Range
Set r = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
r.Offset(, 5).FormulaR1C1 = "=IF(RC[-1]=3,1,IF(RC[-1]=4,2,RC[-1]))"
r.Offset(, 6).FormulaR1C1 = "=IF(RC[-1]<=2,""Target Met"",""Target Not Met"")"
End Sub