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
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