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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Thanks sheetspread

I've tried that already as well as .range and it still gives me the same runtime error.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,181
Messages
6,170,582
Members
452,339
Latest member
mrlong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top