CODEWITHSUNDEEP
vba
C. Rad
C. Rad

Reputation: 33

Based on the numeric value in one column I want to input another value in a different column

I want to write code to look at numbers in column AD and return a value based on numeric value in column AG. Access Database statement and the code I wrote that is returning an error are below. Really appreciate any help.

IIf([Days between File Date and CA Due Date]<30,"5",IIf(Eval([Days between File Date and CA Due Date] Between 31 And 60),"4",IIf(Eval([Days between File Date and CA Due Date] Between 61 And 90),"3",IIf(Eval([Days between File Date and CA Due Date] Between 91 And 150),"2",IIf([Days between File Date and CA Due Date]>151,"1","0")))))

Sub DayScore()
Dim LastRow As Long
    Dim i As Long
    LastRow = Range("AD" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("AD" & i).Value >= "30" Then
            Range("AG" & i).Value = "5"
        End If
    Next i
End Sub

Upvotes: 0

Views: 38

Answers (1)

user4039065
user4039065

Reputation:

Try,

Sub DayScore()
    Dim i As Long

    with worksheets("sheet1")
        For i = 2 To .cells(.rows.count, "AD").End(xlUp).Row
            If isnumeric(.cells(i, "AD").Value2) And Not IsEmpty(.Cells(i, "AD")) Then
                 .cells(i, "AG") = 5 + int(.cells(i, "AD").Value2 >30) _
                                     + int(.cells(i, "AD").Value2 >60) _
                                     + int(.cells(i, "AD").Value2 >90) _
                                     + int(.cells(i, "AD").Value2 >150)
            else
                .cells(i, "AG") = 0
            End If
        Next i
    end with
End Sub

A VBA True is -1 when used mathematically.

It is considered 'best practice' to keep numbers as true numbers (e.g. 5) and not text-that-looks-like-numbers (e.g. "5").

Upvotes: 1

Related Questions