cell address and cell value

[Celica]

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

 

[sheetspread]

Instead of the second Cells, write Range

 

[Celica]

Thanks sheetspread

I've tried that already as well as .range and it still gives me the same runtime error.

 

[Rick Rothstein]

Thanks sheetspread

I've tried that already as well as .range and it still gives me the same runtime error.

Why are you putting a dot in front of the keyword "Range"?

 

[sheetspread]

Thanks sheetspread

I've tried that already as well as .range and it still gives me the same runtime error.

Then dim the variable as a range

 

[Celica]

I am now having a run-time error 91, with "scat1a=Range(scat1).offset(0,-4).address

Rick, I tried range and .range to see if I can correct runtime error5, that's why.

 

[tygrrboi]

Why not lose the strings all together?



Set fSCat = Rg.Find(What:=TextBox83.value, LookIn:=xlFormulas, lookat:=xlWhole) 'find support item

CS.Cells(lr + 1, 3).value = fSCat.Offset(,-4).value


or


Set fSCat = Rg.Find(What:=TextBox83.value, LookIn:=xlFormulas, lookat:=xlWhole) 'find support item
If fSCat.Column < 5 then
msgbox "Row zero or less does not exist"
exit sub
end if
CS.Cells(lr + 1, 3).value = fSCat.Offset(,-4).value

 

[sheetspread]

I am now having a run-time error 91, with "scat1a=Range(scat1).offset(0,-4).address

Put a Set in front

Also don't type Range(scat1), just scat1

 

[Celica]

sheetspread I've also already tried scat1 and it didn't like it that was why I decided to go with cell/range.

I've change it to what tygrrboi, suggested and it worked!! I achieves what I wanted.

Many many THANKS for your speedy responses and for helping me solve this hair pulling dilemma!

Celica