User Form -Subscript out of range when running macro

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,776
Office Version
  1. 2019
Platform
  1. Windows
I have a user form and where I enter the Data manually, it works perfect. However, if I copy and paste data into the description area and cost area, then I get a run time error –‘subscript out of range” and code below is highlighted

It would be appreciated if someone could kindly amend my code


Code:
 Set ws = Worksheets("Fassets")


Code:
 Private Sub CMDAdd_Click()
    Dim I As Integer
    Dim iRow As Long
    Dim ws As Worksheet
     
    Set ws = Worksheets("Fassets")
    
  
     
     'check for a Asset Type
    If Len(Me.TxtAssetype.Value) = 0 Then
        Me.TxtAssetype.SetFocus
        MsgBox "Please select the Asset Type from the drop down menu"
        Exit Sub
    End If
     
      If Len(Me.TxtDes.Value) = 0 Then
        Me.TxtDes.SetFocus
        MsgBox "Please Enter Description"
        Exit Sub
    End If
     
     If InStr(Me.Txtcost, ".") = 0 Then
    MsgBox "Please Enter Decimal place", vbCritical
    Me.Txtcost.SetFocus
    Exit Sub
End If

Me.Txtcost = Format(Me.Txtcost, "#,##0.00")
     
     'copy the data to the database
     'use protect and unprotect lines,
     '     with your password
     '     if worksheet is protected
    With ws
         'find row to use
            For I = 1 To TextBox1.Value
        iRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
         '  .Unprotect Password:="password"
        .Cells(iRow, 1).Value = Me.TxtAssetype.Value
        .Cells(iRow, 4).Value = Me.TxtDes.Value
        .Cells(iRow, 5).Value = Me.TxtDept.Value
        .Cells(iRow, 9).Value = Format(TxtDate1.Value, "mm/dd/yyyy")
        .Cells(iRow, 12).Value = Me.Txtcost.Value
       
         Next I
         '  .Protect Password:="password"
    End With
    
     'clear the data
    With Me
        .TxtAssetype.Value = ""
        .TxtDes.Value = ""
        .TxtDept.Value = ""
        .TxtDate1.Value = ""
        .Txtcost.Value = ""
      
    End With

Sheets("Fassets").Select
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & LR).FormulaR1C1 = "=VLOOKUP(RC[-1],Table!R2C1:R22C3,2,FALSE)"
Range("K2:K" & LR).FormulaR1C1 = "=RC[1]"
Range("M2:M" & LR).FormulaR1C1 = "0"
Range("P2:P" & LR).FormulaR1C1 = "=Codes!RC[4]&""=100"""
Range("J2:J" & LR).FormulaR1C1 = "=VLOOKUP(RC[-9],Table!R2C1:R23C3,3,FALSE)"

If Range("A3").Value = Empty Then
  Exit Sub
        Else
     


 Range("R2").copy Destination:=Range("R3:R" & LR)
 End If
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,776
Office Version
  1. 2019
Platform
  1. Windows
I do have a sheet named "Fassets"

I have now added the following line of code and it works so not sure why error occurs without this

Code:
 On Error Resume Next
    Set ws = Worksheets("Fassets")
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,998
Without the "On Error Resume Next" ... try this:

Set ws = Sheets("Fassets")
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,776
Office Version
  1. 2019
Platform
  1. Windows
Thanks very much for the help. Code works perfectly
 

Watch MrExcel Video

Forum statistics

Threads
1,129,542
Messages
5,636,913
Members
416,950
Latest member
soychristophe

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
Top