IF condition is not working with formula

[elsuji]

dear Team,

I am using the bellow code for calculating my values.

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


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

[Paul_Hossler]

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) "

[elsuji]

Dear Paul,

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

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

[Paul_Hossler]

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


27116

[elsuji]

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

[Paul_Hossler]

Are you SURE that you want to use


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

[elsuji]

Yes

[elsuji]

Dear Mr.Paul,

Waiting for your reply

[p45cal]

try:
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.

[elsuji]

Dear p45cal,

Your code is working as per my requirement perfectly.

Thanks for your support.