cell address and cell value

Celica

New Member
Joined
Jul 6, 2016
Messages
9
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
 

Celica

New Member
Joined
Jul 6, 2016
Messages
9
Thanks sheetspread

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

Celica

New Member
Joined
Jul 6, 2016
Messages
9
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

Well-known Member
Joined
Sep 8, 2015
Messages
1,196
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
 
Last edited:

Celica

New Member
Joined
Jul 6, 2016
Messages
9
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
 

Forum statistics

Threads
1,085,058
Messages
5,381,478
Members
401,743
Latest member
james909090

Some videos you may like

This Week's Hot Topics

Top