Hi All
I am trying to find the value/content in a cell address, but I run into run-time error 5. I don't know what is wrong, any ideas.
My code is below:
Private Sub ClaimCMB_Click()
Dim Filename2 As String
Dim Filename3 As String
Dim FilePath2 As String
Dim CSF As Workbook
Dim CS As Worksheet
Dim RD As Worksheet
Dim lr As Integer
Dim Rg As Range
Dim mtch As Double
Dim Rtrn1 As Variant
Dim Rtrn2 As Variant
Dim scat1 As String
Dim scat1a As String
Dim scat1b As String
Filename2 = "..blah blah.xlsx"
Filename3 = "..blah blah1.xlsm"
FilePath2 = "c:\blah\blah\blah"
Set CSF = Workbooks.Open(FilePath2 & "\" & Filename2)
Set RD = Workbooks(Filename3).Sheets("admin")
Set CS = Workbooks(Filename2).Sheets("Sheet1")
Set Rg = RD.Range("a2:j69")
lr = CS.Range("A" & CS.Rows.Count).End(xlUp).Row
CS.Range("C3").value = TextBox1.value
CS.Range("c5").value = Me.TextBox142.value 'fname
CS.Range("c4").value = Me.TextBox139.value ' sname
CS.Range("c7").value = Me.RState.value
CS.Range("c6").value = Me.CIRTS.value
If TextBox83.value <> "" Then ' pasting item 1 to schedule
CS.Range("b10").value = TextBox83.value
Set fSCat = Rg.Find(What:=TextBox83.value, LookIn:=xlFormulas, lookat:=xlWhole) 'find support item
scat1 = fSCat.Address
scat1a = Range(scat1).Offset(0, -4).Address
'scat1b = scat1a.Address
CS.Cells(lr + 1, 3).value = Cells(scat1a).value ' run-time error 5 is occurring here
Thanks
I am trying to find the value/content in a cell address, but I run into run-time error 5. I don't know what is wrong, any ideas.
My code is below:
Private Sub ClaimCMB_Click()
Dim Filename2 As String
Dim Filename3 As String
Dim FilePath2 As String
Dim CSF As Workbook
Dim CS As Worksheet
Dim RD As Worksheet
Dim lr As Integer
Dim Rg As Range
Dim mtch As Double
Dim Rtrn1 As Variant
Dim Rtrn2 As Variant
Dim scat1 As String
Dim scat1a As String
Dim scat1b As String
Filename2 = "..blah blah.xlsx"
Filename3 = "..blah blah1.xlsm"
FilePath2 = "c:\blah\blah\blah"
Set CSF = Workbooks.Open(FilePath2 & "\" & Filename2)
Set RD = Workbooks(Filename3).Sheets("admin")
Set CS = Workbooks(Filename2).Sheets("Sheet1")
Set Rg = RD.Range("a2:j69")
lr = CS.Range("A" & CS.Rows.Count).End(xlUp).Row
CS.Range("C3").value = TextBox1.value
CS.Range("c5").value = Me.TextBox142.value 'fname
CS.Range("c4").value = Me.TextBox139.value ' sname
CS.Range("c7").value = Me.RState.value
CS.Range("c6").value = Me.CIRTS.value
If TextBox83.value <> "" Then ' pasting item 1 to schedule
CS.Range("b10").value = TextBox83.value
Set fSCat = Rg.Find(What:=TextBox83.value, LookIn:=xlFormulas, lookat:=xlWhole) 'find support item
scat1 = fSCat.Address
scat1a = Range(scat1).Offset(0, -4).Address
'scat1b = scat1a.Address
CS.Cells(lr + 1, 3).value = Cells(scat1a).value ' run-time error 5 is occurring here
Thanks