VBA merge cell error OR sheet identification issue

dellehurley

Board Regular
Joined
Sep 26, 2009
Messages
171
Office Version
  1. 365
Platform
  1. 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")
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
Many Thanks
Dannielle
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
The code it going to be working on the wrong sheet if the sheet set to shNm is not active when the code runs.

To avoid any doubt you need to use
VBA Code:
With wsNm
    .Range(Cells(i, 1), Cells(i, 2)).Copy .Range("E20:F20")
    .Range(Cells(i, 1), Cells(i, 2)).Delete Shift:=xlShiftUp
End With
Note the dots added in front of Range.
 
Upvote 0
The code it going to be working on the wrong sheet if the sheet set to shNm is not active when the code runs.

To avoid any doubt you need to use
VBA Code:
With wsNm
    .Range(Cells(i, 1), Cells(i, 2)).Copy .Range("E20:F20")
    .Range(Cells(i, 1), Cells(i, 2)).Delete Shift:=xlShiftUp
End With
Note the dots added in front of Range.
It worked thank you.
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,518
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top