Macro User form

[howard]

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

 Set ws = Worksheets("Fassets")

It would be appreciated if someone could kindly amend my 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

 

[Anthony47]

Does worksheet "Fassets" belong to the same workbook that contains the userform?

 

[howard]

Thanks for the reply.

"Fassets" does belong to the same workbook that contains the userform

 

[Anthony47]

I'm sorry, I asked for the wrong question, that is:
Does worksheet "Fassets" belong to the same workbook that contains the macro?

If Yes, try using Set ws = ThisWorkbook.Worksheets("Fassets")
If No, then try Set ws = ActiveWorkbook.Worksheets("Fassets")

 

[Fluff]

Also check that the sheet name does not have any leading/trailing spaces.

 

[howard]

thanks Anthony- the worksheet "Fassets" does belong to the same workbook that contains the macro

I have used your code and it works perfectly

 

[howard]

thanks for your input Fluff

I checked that before you replied as I thought it may be the problem, but it had no trailing or leading spaces

 

[Anthony47]

thanks Anthony- the worksheet "Fassets" does belong to the same workbook that contains the macro

I have used your code and it works perfectly

Thank you for the feedback

Bye