Consulting

Results 1 to 10 of 10

Thread: IF condition is not working with formula

  1. #1
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location

    IF condition is not working with formula

    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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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) "
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Dear Paul,

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

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

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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


    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    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

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Yes

  8. #8
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Dear Mr.Paul,

    Waiting for your reply

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Dear p45cal,

    Your code is working as per my requirement perfectly.

    Thanks for your support.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •