If function doesn't return result. Please assist

[sipirili]

I'm trying to create a macro where if column Q4 is less than 20, column S4 should show N and if column Q4 is between 20 to 30 and is more than 5,000,000, column S4 should show y and if not N and if column Q4 is more than 30, column S4 should show Y. This function should repeat till the last non blank row of Q.
I'm not sure why my codes are not working even for the 1st row and I have no idea how to continue till the last row. Someone help please. Thank you

Dim score As Long, result As String
    Set Age = Range("Q4").Number
    Set amt = Range("K4").Number
   
    If Age <= 20 Then
    result = "N"
    If Age > 20 And Age <= 30 And amt > 5000000 Then
    result = "Y"
    Else
    result = "N"
    If Age > 30 Then
    result = "Y"
    Range("S4").Value = result
    End If
    End If
    End If

 

[RoryA]

Welcome to the forum.

Your code has a few syntax and logic issues. Try something like this:

    Dim Age                   As Long
    Dim result                As String
    Dim amt                   As Double

    Age = Range("Q4").Value
    amt = Range("K4").Value

    If Age <= 20 Then
        result = "N"
    ElseIf Age > 20 And Age <= 30 Then
        If amt > 5000000 Then
            result = "Y"
        Else
            result = "N"
        End If
    Else
        result = "Y"

    End If

 

[Fishboy]

I'm trying to create a macro where if column Q4 is less than 20, column S4 should show N and if column Q4 is between 20 to 30 and is more than 5,000,000, column S4 should show y and if not N and if column Q4 is more than 30, column S4 should show Y. This function should repeat till the last non blank row of Q.
I'm not sure why my codes are not working even for the 1st row and I have no idea how to continue till the last row. Someone help please. Thank you

Dim score As Long, result As String
    Set Age = Range("Q4").Number
    Set amt = Range("K4").Number
   
    If Age <= 20 Then
    result = "N"
    If Age > 20 And Age <= 30 And amt > 5000000 Then
    result = "Y"
    Else
    result = "N"
    If Age > 30 Then
    result = "Y"
    Range("S4").Value = result
    End If
    End If
    End If

Hi sipirili, welcome to the boards.

One possible solution would be as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Age As Range, Amt As Range, Output As Range


Set Age = Range("Q4")
Set Amt = Range("K4")
Set Output = Range("S4")


If Age.Value < 20 Then
    Output.Value = "N"
ElseIf Age.Value >= 20 And Age.Value <= 30 Then
    If Amt.Value > 5000000 Then
        Output.Value = "Y"
    Else
        Output.Value = "N"
    End If
ElseIf Age.Value > 30 Then
    Output.Value = "Y"
End If


End Sub

 

[sipirili]

This doesn't work somehow. Thanks for replying still.

 

[sipirili]

This code works but how do i continue running this code till the last row? Tried adding a do and loop till the end but it doesnt work. Your help is much appreciated.

 

[Fishboy]

This doesn't work somehow. Thanks for replying still.

This code works but how do i continue running this code till the last row? Tried adding a do and loop till the end but it doesnt work. Your help is much appreciated.

Who are each of these replies aimed at?

If you are asking how to turn a Worksheet_Change event into a loop you are probably better of having it as a standard macro in a standard module. It can be modified as follows to work all the way to the end of the list, but it now needs to be run manually rather than reacting to changes on the sheet:

Sub ChangeOutput()
' Defines variables
Dim Age As Range, Amt As Range, Output As Range
Dim Cell As Range, cRange As Range
Dim LastRow As Long


' Defines LastRow as the last row of data based on column Q (Age column)
LastRow = ActiveSheet.Cells(Rows.Count, "Q").End(xlUp).Row
' Sets the check range as Q4 to the last row of Q
Set cRange = Range("Q4:Q" & LastRow)


' For each cell in the check range
For Each Cell In cRange
    ' Sets the Age range as column Q of the current row
    Set Age = Range("Q" & Cell.Row)
    ' Sets the Amt range as column K of the current row
    Set Amt = Range("K" & Cell.Row)
    ' Sets the Output range as column S of the current row
    Set Output = Range("S" & Cell.Row)
        
        ' If the Age value on the current row is less than 20 then...
        If Age.Value < 20 Then
            ' Update the Output value on the current row to N
            Output.Value = "N"
        ' Else if the Age value on the current row is between 20 and 30 then...
        ElseIf Age.Value >= 20 And Age.Value <= 30 Then
            ' If the Amt value on the current row is greater than 5000000 then...
            If Amt.Value > 5000000 Then
                ' Update the Output value on the current row to Y
                Output.Value = "Y"
            ' Else if the Amt value on the current row is less than 5000000 then...
            Else
                ' Update the Output value on the current row to N
                Output.Value = "N"
            End If
        ' Else if the Age value on the current row is greater than 30 then...
        ElseIf Age.Value > 30 Then
            ' Update the Output value on the current row to Y
            Output.Value = "Y"
        End If
    ' Check next cell in the check range
    Next Cell


End Sub

 

[sipirili]

@Fisboy, sorry thought I was replying to individual messages. Yours worked well. Thank you so much.

 

[Fishboy]

@Fisboy, sorry thought I was replying to individual messages. Yours worked well. Thank you so much.

Happy to help