Excel VBA: Inserting value in cell using xlDown and Offset

Question

I'm having some trouble getting my code to insert a value from an InputBox. The offending lines in particular are the following:

Set p = nPoints.End(xlDown).Offset(1, 0)
            p.Value = nPointVal

What this should do is find the last row of a spreadsheet, the first available cell to the right, and insert the stored value nPointVal. However, instead of doing so, it simply doesn't insert anything. Any help is greatly appreciated, and my full code is below.

Sub takeTwo()

On Error Resume Next

Dim fNameString As Variant
Dim lNameString As Variant
Dim sEmailString As Variant
Dim nPointVal As Integer
Dim sEventName As String
Dim n As Integer, r As Long, c As Range, d As Range, e As Range, p As Range, sE As Range
Dim fName As Range, lName As Range, sEmail As Range, nPoints As Range
Dim lEvent As Integer
Set fName = ActiveSheet.Range("FirstName")
Set lName = ActiveSheet.Range("LastName")
Set sEmail = ActiveSheet.Range("eMailAddr")


fNameString = Split(Application.InputBox("First Names in comma delimited format.", Type:=2), ",")
lNameString = Split(Application.InputBox("Last Names in comma delimited format.", Type:=2), ",")
sEmailString = Split(Application.InputBox("Email Addresses in comma delimited format.", Type:=2), ",")
nPointVal = InputBox("Please enter a point value for this event")
sEventName = InputBox("Please enter the name of the event.")

lEvent = NextEmptyColumn(Range("A1"))
Set sE = Range("A1").Offset(0, lEvent)
sE.Value = sEventName
' sEventPos = sE.Offset(0, lEvent)

If fNameString <> False And lNameString <> False Then

    For i = LBound(fNameString) To UBound(fNameString)

        fNameString(i) = Trim(fNameString(i)) ' Trim off leading and trailing whitespace.
        lNameString(i) = Trim(lNameString(i)) ' Trim off leading and trailing whitespace.

        Set c = fName.Find(fNameString(i), LookIn:=xlValues, LookAt:=xlWhole)
        Set d = lName.Find(lNameString(i), LookIn:=xlValues, LookAt:=xlWhole)

        If c And d Is Nothing Then

            Set c = fName.End(xlDown).Offset(1, 0)
            c.Value = fNameString(i)
            Set d = lName.End(xlDown).Offset(1, 0)
            d.Value = lNameString(i)
            Set e = sEmail.End(xlDown).Offset(1, 0)
            e.Value = sEmailString(i)
            Set p = nPoints.End(xlDown).Offset(1, 0)
            p.Value = nPointVal

            Dim s As Range ' Our summation range
            Set s = Range(c.Offset(0, 5), c.Offset(0, c.EntireRow.Columns.Count - 1))

            ' c.Offset(1, 3).Formula = "=((" & s.Address & ""

        End If

    Next


End If

End Sub

You don't ever set the range nPoints to anything. If you are trying to find the last cell in a column, it is better practice to go from the last row in the sheet and go up. — Kyle
besides I feel like you want If c Is Nothing And d Is Nothing Then Instead of If c And d Is Nothing Then — user3598756
On Error Resume Next is almost always a very bad idea. It often functions as On Error Mislead Programmer. Delete it so that you can see where the bugs are. — John Coleman
To temper my comment: On Error Resume Next in the middle of the code, followed immediately by something like trying to open a file for reading, followed immediately by a conditional like If Err.Number > 0 Then ... followed immediately by On Error GoTo 0, is a common enough error-handling idiom and is unproblematic. Your use isn't like that -- it is global and has the effect of hiding all run-time errors, making code needlessly hard to debug. Don't globally suppress errors -- eliminate them if possible, handle them if not. — John Coleman
@Kyle: What range would I set nPoints to if the range is yet to be created by this same code? — Jonathan Rauscher

Jonathan Rauscher Jonathan Rauscher · Accepted Answer · 2016-09-24T16:38:17

So I found a simpler solution:

Set p = fName.End(xlDown).Offset(0, lEvent)
            p.Value = nPointVal

This references the same row in which the inserted name is on, which is what we want because the point value is related to this person. Someone please give advice if there could be some issues with this.

Excel VBA: Inserting value in cell using xlDown and Offset

1 Answers