I have a user form to enter data in certain columns on sheet "Fassets"
I get subscript out of range and the code below is highlighted
It would be appreciated if someone could kindly amend my code
I get subscript out of range and the code below is highlighted
Code:
Set ws = Worksheets("Fassets")
It would be appreciated if someone could kindly amend my code
Code:
Option Explicit
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