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
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. – KyleIf c Is Nothing And d Is Nothing Then
Instead ofIf c And d Is Nothing Then
– user3598756On Error Resume Next
is almost always a very bad idea. It often functions asOn Error Mislead Programmer
. Delete it so that you can see where the bugs are. – John ColemanOn 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 likeIf Err.Number > 0 Then ...
followed immediately byOn 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 ColemannPoints
to if the range is yet to be created by this same code? – Jonathan Rauscher