User Form -Subscript out of range when running macro

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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")
 
Upvote 0
Without the "On Error Resume Next" ... try this:

Set ws = Sheets("Fassets")
 
Upvote 0
Thanks very much for the help. Code works perfectly
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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