PDA

View Full Version : Sleeper: VBA If formula help



malleshg24
08-24-2017, 06:42 AM
Hi Team,:banghead::help:help

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
08-24-2017, 07:00 AM
have you tried a simple if statement, put this in f2


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

malleshg24
08-24-2017, 07:59 AM
Thank you so much, above formula worked for me in excel, In
vba when tried its not working. :banghead::help:help


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
08-24-2017, 09:38 AM
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
08-25-2017, 06:13 AM
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
08-25-2017, 08:29 AM
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
08-25-2017, 08:46 AM
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
08-26-2017, 03:01 AM
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