IF condition is not working with formula

dear Team,

I am using the bellow code for calculating my values.

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim ws As Worksheet, lRow As Long, DifValue As String
 Set ws = ThisWorkbook.Sheets("Master Data")
 With ws
 lRow = .Range("T" & .Rows.Count).End(xlUp).Row
 .Range("E4:E" & lRow).Formula = "=250 - (T4-L4)"
 .Range("F4:F" & lRow).Formula = "=600 - (T4-M4)"
 .Range("G4:G" & lRow).Formula = "=1000 - (T4-N4)"
 .Range("H4:H" & lRow).Formula = "=1000 - (T4-O4)"
 .Range("I4:I" & lRow).Formula = "=1000 - (T4-P4)"
 .Range("K4:K" & lRow).Formula = "=2000 - (T4-R4)"
 If .Range("C" & lRow).Value = "LW300FV(ARAI)" Then
 .Range("J4:J" & lRow).Formula = "=2000 - (T4-Q4)"
 Else
 .Range("J4:J" & lRow).Formula = "=1000 - (T4-Q4)"
 End If


' .Range("W4:W" & lRow).Formula = DateDiff("D", CDate(.Range("V4")), Now)
Dim LastRow  As Long, i As Long
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    For i = 4 To LastRow
    If .Range("V" & i).Value = "" Then
        .Range("W" & i).Value = ""
    Else
        .Range("W" & i).Value = DateDiff("d", .Range("V" & i).Value, Date)
    End If
   
    If .Range("W" & i).Value >= 30 Then
        .Range("U" & i).Value = ""
        .Range("V" & i).Value = ""
    End If     


    Next i


 End With
End Sub

---

In above code all are working except If condition on

Code:

---

If .Range("C" & lRow).Value = "LW300FV(ARAI)" Then .Range("J4:J" & lRow).Formula = "=2000 - (T4-Q4)"
 Else
 .Range("J4:J" & lRow).Formula = "=1000 - (T4-Q4)"
 End If

---

When ever i am using this condition this will run the Else condition only.

Can any one help me where and what is the mistake i am doing on this

Syntax looks good.

Check the actual data in .Range("C" & lRow) to see if there are any leading or trailing spaces in the cell, e.g. "LW300FV(ARAI) "

Dear Paul,

The name "LW300FV(ARAI)" is correctly updated on my sheet.

I uploaded my file here for your reference. Please check and confirm

Not sure why you're using Selection_Change, but it looks like lRow is always = the last used row number, 8 in the sample you attached

Maybe if you explained WHAT you want to do it'd be easier to suggest something


Attachment 27116

Dear Paul,


My requirement is once i entered the values on column "T" & "Q" it should calculate automatically and update on column "J". So i am using Selection_Change.

And in column "J" the default formula is .Range("J4:J" & lRow).Formula = "=1000 - (T4-Q4)" . But where the name "LW300FV(ARAI) " is update on column "C" that row only the formula to be change to .Range("J4:J" & lRow).Formula = "=2000 - (T4-Q4)" .

When i am using If .Range("C" & lRow).Value = "LW300FV(ARAI)" Then this is checking the last updated row only. can you please help me

Are you SURE that you want to use

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

---

That gets executed anytime you just click anywhere on "Master Data", A5, B20, Z100, etc.

lRow is always = 8 and that point, and so the If/Then fails

Yes

Dear Mr.Paul,

Waiting for your reply

try:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long, DifValue As String
lRow = Range("T" & Rows.Count).End(xlUp).Row
Range("E4:E" & lRow).Formula = "=250 - (T4-L4)"
Range("F4:F" & lRow).Formula = "=600 - (T4-M4)"
Range("G4:G" & lRow).Formula = "=1000 - (T4-N4)"
Range("H4:H" & lRow).Formula = "=1000 - (T4-O4)"
Range("I4:I" & lRow).Formula = "=1000 - (T4-P4)"
Range("K4:K" & lRow).Formula = "=2000 - (T4-R4)"
Range("J4:J" & lRow).Formula = "=IF(C4=""LW300FV(ARAI)"",2000,1000) - (T4-Q4)"


' Range("W4:W" & lRow).Formula = DateDiff("D", CDate(Range("V4")), Now)
Dim LastRow  As Long, i As Long

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

For i = 4 To LastRow
  If Range("V" & i).Value = "" Then
    Range("W" & i).Value = ""
  Else
    Range("W" & i).Value = DateDiff("d", Range("V" & i).Value, Date)
  End If
   
  If Range("W" & i).Value >= 30 Then
    Range("U" & i).Value = ""
    Range("V" & i).Value = ""
  End If
Next i
End Sub

---

You don't need to Dim ws and have the With...End With because all unqualified ranges in a sheet's code-module refer to that sheet, regardless of any other sheet being active or not.

Dear p45cal,

Your code is working as per my requirement perfectly.

Thanks for your support.