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,081,454
Messages
5,358,771
Members
400,513
Latest member
sdrowsick

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top