Share via

RIGHT Function in VBA

Anonymous
2022-10-17T16:05:55+00:00

I cannot seem to locate via research the code I need.

In certain cells for each student, I have the following: Grade: ###, where ### equals their grade.

If I pull the string value, ie Grade: 90, I need to retrieve the numeric value from that string, ie. 90, and convert it to an integer.

What is the code to pull the value 90 out and then convert to an integer so I can use it in a formula?

Thanks,

Les

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2022-11-02T14:09:06+00:00

Hi there

For Each g In GradeRng

Is the same as saying

For Each cell In the Grade Range

In Excel each cell is considered a range hence we dimensioned as Range

The "g" means grade.

So

For Each g In GradeRng in other words is

For Each grade In the Grade Range

The For Each statement loops thru all the cells in the given/settled range looking for the values you want to evaluate with the if statements

I hope this helps you and gives you a solution to your problem

Regards

Jeovany

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2022-10-30T23:19:13+00:00

You are welcome!

Regarding "...What is the code to pull the value 90 out and then convert it to an integer so I can use it in a formula?"

This line of code will do it

grade = CInt(Replace(g, "Grade: ", ""))

Regarding "...I need to execute a code that will:

1) Check to see if a student missed it

2) Change the yellow background to green,"

Something like this will do it

Sub GiveBackQuestions()

Dim lastrow As Long

Dim GradeRng As Range

Dim g As Range

Dim grade

With ActiveSheet 

lastrow = .Cells(Rows.Count, "B").End(xlUp).Row 

Set GradeRng = .Range(Cells(2, "G"), Cells(lastrow, "G")) 

For Each g In GradeRng 

        If InStr(g.Value, "Grade: ") > 0 Then 

                **grade = CInt(Replace(g, "Grade: ", ""))** 

                MsgBox .Range("B" & g.Row).Value & " Grade is =" & grade 

         End If 

        If .Range("F" & g.Row).Value = 0 Then 

                .Range("C" & g.Row & ":F" & g.Row).Interior.Color = vbGreen 

        End If 

Next g

End With

End Sub

NOTE: We don't know what will you do once the grade value is pulled from the string Grade: ###

Regarding "... 3) Adjust the grade indicated"

What do you mean by that?

What do you want to happen?

Was this answer helpful?

1 person found this answer helpful.
0 comments

14 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-10-20T12:50:04+00:00

    I am looking for VBA coding to do this. I believe what you suggested is within the spreadsheet.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-10-17T16:44:01+00:00

    To help us help you, it is always best to provide detailed examples. For VBA questions, that should include relevant code, which includes variable declarations. And FYI, the string "Grade: 90" is not in the form "Grade: ###". In particular, the integer to extract is not always the rightmost __3__ characters.

    The following might be more verbose than necessary. Because you neglect to specify the VBA context, I try to cover all bases so that you can adapt accordingly.

    Function rightInt(r As Range) As Double
    Dim s As String, n As Long
    s = r(1).Value2
    n = Mid(s, InStrRev(s, " "))
    rightInt = n
    End Function

    Demonstration:

    with the formula =rightInt(A1) in B1.

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2022-10-17T16:13:05+00:00

    Why use VBA? Let's say the text Grade: 90 is in D2, In - for example - E2, enter the formula

    =--RIGHT(D2,LEN(D2)-FIND(" ",D2))

    The formula can be filled down if required.

    Was this answer helpful?

    0 comments