VBA IF Help plz !!

[Mallesh23]

Hi Team,

My if formula is not working plz suggest where its going wrong, and
also, if any other solution then also i am ok. Thanks.

Sub Test()
Dim lr As Long
Dim i As Integer

Dim ws As Worksheet​

<strike></strike>

Set ws = ThisWorkbook.Worksheets("sheet1")
lr = ws.Range("A1000").End(xlUp).Row

For i = 1 To lr
ws.Cells(i + 1, 2).Formula = "=if(ws.cells(i+1,2)>5500,""New"",""Historic"")"
'ws.Range("a2:a" & lr).Formula = "=if(ws.cells(i+1,2)>5500,""New"",""Historic"")"

Next i
End Sub




Regards
Mallesh
<strike></strike>

 

[Joe4]

Please walk us through an actual example of what SHOULD happen.
So, based on your loop, the first iteration is for row 1.
As we loop through for 1, what formula should be placed in what cell?

 

[cerfani]

well one thing i notice is you are writing vba/excel hybrid code and attempting to set it to cell values...

"=if(ws.cells(i+1,2)>5500,""New"",""Historic"")"

that wont work in excel

"=if(" & ws.cells(i+1,2).Address & ">5500,""New"",""Historic"")"

that might work, try it out

 

[Joe4]

Since you are applying the same formula to a continuous range, this can be done pretty easily without any loops.
I just need to know exactly what the formula is, and how it is related to the cell it is going on.
I think there is flawed logic in the original attempt, as it looks like it would create a circular reference (trying to put a formula in Cells(i + 1, 2) that references Cells(i + 1, 2)).
That is why I want clarification as to what the formula should look like and what cell it goes in.

 

[Mallesh23]

Joe/cerafani

Thanks for looking in my problem,

Joe:
I have 40000+ number in Column A to compare with one specific number
those 40000 numbers are actually dates, comes in converted number format like 41640 (1-Jan-14)

I Want the result in

sheet1​

Column B2:B40200,
My Formula in excel it works=IF(A2>41640,"New","Historic") and I fill down the formula.


Cefafani:

result is showing 0 value in Column B
ws.Cells(i + 1, 2).Formula = "=if(" & ws.Cells(i + 1, 2).Address & ">5500,""New"",""Historic"")"

Thanks again for your precious time !

Thanks
Regards,
Mallesh

 

[Peter_SSs]

Try this in a copy of your workbook

Sub Test2()
  Dim lr As Long
  Dim ws As Worksheet
  
  Set ws = ThisWorkbook.Worksheets("sheet1")
  With ws
    lr = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("B2:B" & lr).Formula = "=IF(A2>41640,""New"",""Historic"")"
  End With
End Sub

 

[cerfani]

Hey Mallesh23, I just noticed the formula I tried to fix for you is a circular reference... the cell formula you are setting is referencing itself

 

[Mallesh23]

Peter, ​

I used your code, it worked awsome and also thanks to all for your help and suggestion.

Regards,
Mallesh

 

[Peter_SSs]

Peter, ​

I used your code, it worked awsome and also thanks to all for your help and suggestion.

Regards,
Mallesh

You are welcome, though I was just implementing what Joe was alluding to.

Since you are applying the same formula to a continuous range, this can be done pretty easily without any loops.