dellehurley
Board Regular
- Joined
- Sep 26, 2009
- Messages
- 171
- Office Version
- 365
- Platform
- Windows
Hi
I have created the userform to add information to a database. A command button looks in a database for the same number. If the number already exists another userform should open allowing the user to edit the information.
When I play the code on the command button it works BUT when I open the userform the traditional way the second userform does not open. I think this is because the code was getting confused about which userforms or sheet I was referring to. So I editted the information but now I get 'runtine error 1004. We can't do that to a merged cell. I cannot find a merged cells.
I don't know how to name the sheet and have my code work too, any ideas ...please????
fyi. the sheet has been renamed to shNm
error is in this line (full sub below) Range(Cells(i, 1), Cells(i, 2)).Copy Range("E20:F20")
Many Thanks
Dannielle
I have created the userform to add information to a database. A command button looks in a database for the same number. If the number already exists another userform should open allowing the user to edit the information.
When I play the code on the command button it works BUT when I open the userform the traditional way the second userform does not open. I think this is because the code was getting confused about which userforms or sheet I was referring to. So I editted the information but now I get 'runtine error 1004. We can't do that to a merged cell. I cannot find a merged cells.
I don't know how to name the sheet and have my code work too, any ideas ...please????
fyi. the sheet has been renamed to shNm
error is in this line (full sub below) Range(Cells(i, 1), Cells(i, 2)).Copy Range("E20:F20")
VBA Code:
Private Sub cmdAdd1_Click()
Dim A As Variant
Dim RINSearch As Long
Dim iRow As Long
Dim LastRow As Long
Dim i As Integer
Dim FlNm As String
FlNm = FrmForm.txtRIN1.Value
iRow = shNm.Range("A" & shNm.Rows.Count).End(xlUp).Row + 1 ' identifying first empty row
LastRow = shNm.Range("A" & shNm.Rows.Count).End(xlUp).Row ' identifying the last row
RINSearch = Application.WorksheetFunction.CountIf(shNm.Range("B:B"), FlNm)
If RINSearch >= 1 Then
MsgBox "This RIN already exists"
For i = 2 To LastRow
If shNm.Cells(i, 2) = FlNm Then
shNm.Range("E20:F20").Clear
Range(Cells(i, 1), Cells(i, 2)).Copy Range("E20:F20")
Range(Cells(i, 1), Cells(i, 2)).Delete Shift:=xlShiftUp
FrmForm2.txtNameED.Value = shNm.Range("E20").Value
FrmForm2.txtRinED.Value = shNm.Range("F20").Value
FrmForm2.Show
End If
Next i
Else
shNm.Cells(iRow, 1) = FrmForm.cmbFullName1.Value
shNm.Cells(iRow, 2) = FrmForm.txtRIN1.Value
MsgBox "Name and RIN added to Database"
End If
End Sub
Dannielle