KayCee
12-21-2020, 05:47 AM
I have a membership table that contains details of payments from members. From time to time I need to add members to the membership list (and other sheets) and so I use command buttons to activate VBA code to do this task. The aim is to add a blank row (Row9) just below the top name in the members list, add a new name using a form, and then copy down the formulas from the top row (Row8) to the new Row9 row.
As you will see from the first image, the D8 cell does not contain a formula and so I would not expect the new row D9 cell to contain a formula. However, as you can see from the second image it does contain a formula!
The section of code that I use to add the new blank row, then enter the new members name, then copy down the formulas is shown below.
I would be grateful for any assistance in solving this problem as I cannot see any reason looking at my VBA code why the rogue formula would be inserted in cell D9.
' Enter a new name.
Sheet27.Select ' Select Members sheet
Load AddNameForm
strName = Application.Trim(AddNameForm.SURNAME.Value & " " & AddNameForm.Forename.Value) ' strName dimensioned earlier as a string
If strName = "" Then
MsgBox ("You did not enter a name")
Sheet27.Select 'Members sheet
Exit Sub
End If
If AddNameForm.Forename.Value = "" Then
MsgBox ("You did not enter a Forename")
Sheet27.Select 'Members sheet
Exit Sub
End If
If AddNameForm.SURNAME.Value = "" Then MsgBox ("You did not enter a Surname")
Sheet27.Select 'Members sheet
Exit Sub
End If
'Turn off screen updating during next process
Application.ScreenUpdating = False
'Select worksheet to input new name and resort in alphabetical order
Sheet2.Select ' November sheet
ActiveSheet.Unprotect
'Select top name cell (D8), create blank row below with same formatting
Range("D8").Activate
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
'Select blank name cell and insert new name
Range("D9").Activate
ActiveCell.Value = strName
' Copy formula down for new name entry
Range("Z8").End(xlToLeft).Select
Selection.Copy
Range("Z8").End(xlToLeft).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
The VBA code then goes on to sort the names in alphabetical order. If it would help to have the full code please let me know.
As you will see from the first image, the D8 cell does not contain a formula and so I would not expect the new row D9 cell to contain a formula. However, as you can see from the second image it does contain a formula!
The section of code that I use to add the new blank row, then enter the new members name, then copy down the formulas is shown below.
I would be grateful for any assistance in solving this problem as I cannot see any reason looking at my VBA code why the rogue formula would be inserted in cell D9.
' Enter a new name.
Sheet27.Select ' Select Members sheet
Load AddNameForm
strName = Application.Trim(AddNameForm.SURNAME.Value & " " & AddNameForm.Forename.Value) ' strName dimensioned earlier as a string
If strName = "" Then
MsgBox ("You did not enter a name")
Sheet27.Select 'Members sheet
Exit Sub
End If
If AddNameForm.Forename.Value = "" Then
MsgBox ("You did not enter a Forename")
Sheet27.Select 'Members sheet
Exit Sub
End If
If AddNameForm.SURNAME.Value = "" Then MsgBox ("You did not enter a Surname")
Sheet27.Select 'Members sheet
Exit Sub
End If
'Turn off screen updating during next process
Application.ScreenUpdating = False
'Select worksheet to input new name and resort in alphabetical order
Sheet2.Select ' November sheet
ActiveSheet.Unprotect
'Select top name cell (D8), create blank row below with same formatting
Range("D8").Activate
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.Copy
ActiveCell.Offset(1).EntireRow.PasteSpecial xlPasteFormats
'Select blank name cell and insert new name
Range("D9").Activate
ActiveCell.Value = strName
' Copy formula down for new name entry
Range("Z8").End(xlToLeft).Select
Selection.Copy
Range("Z8").End(xlToLeft).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
The VBA code then goes on to sort the names in alphabetical order. If it would help to have the full code please let me know.