VBA - trying to change value of a cell in another sheet in a table using worksheet_change and cells.find

[ozasis]

Hi all,

I'll outline the details of my set up in dot point form:-

• I have a sheet called "IAR"
  • It has a table also called "IAR"
  • I have a named range on this sheet called "IARcol" which references the formula "=IAR[[#All],[Job No]]"
• I have another sheet called "notes"
  • It has a table called "notes"
  • I have a named range on the sheet called "NotesExpectedDate" which references "=Notes[[#All],[NewExpectedDate]]"

I have the following VBA code on the "notes" sheet:-

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SearchCell As Range
    Dim IARcolumn As Range
    
    Set IARcolumn = Sheets("IAR").Range("IARcol")
                    
    Set SearchCell = Sheets("IAR").IARcolumn.Find(What:=Left(Target.Offset(0, -5), 6).Value, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    If Not Intersect(Target, Range("NotesExpectedDate")) Is Nothing Then
        SearchCell.Offset(0, 23).Value = Target.Value
        
    End If
    
End Sub

I cannot for the life of me get it working. I have tried fiddling with bits and pieces, but it just gives me different vba errors.

Any help would be greatly appreciated, as I'm relatively new at creating my own code from scratch. Normally I just modify other people's work to suit, but couldn't find any examples similar to what I require...

Cheers!

 

[Momentman]

On what line do you get the error?

 

[ozasis]

I was originally getting the error on the set searchcell line, however I have since broken that variable into multiple variables, and I now get the error when setting the first object...

Run time error 424, object required

at line:-

Set SearchValue = Left(Target.Offset(0, -5), 6).Value

 

[Momentman]

You dont need the set statement for this particular line, you could just assign

SearchValue = Left(Target.Offset(0, -5), 6).Value

 

[ozasis]

Now gives runtime error 91

Object variable or with block variable not set

 

[Momentman]

You declared searchcell as a range but now you are trying to set it to a value, there's obviously a conflict

 

[ozasis]

target.value is a date, but the cell offset 5 to the left is a text cell. I tried setting searchvalue as a string and using "Left(Target.Offset(0, -5), 6).Value", but I get "Object Required" as an error.

 

[Momentman]

Its difficult to visualise what you really want to do 'cos but looking at this line, dont you have more arguments than you need with the left function

The left typically functions as left(a,b)you dont need the .value, just

Left(Target.Offset(0, -5), 6)

Can i see the current code you are using, maybe we can analyze/debug it one line at a time and see whats really causing the problem

 

[ozasis]

It's probably a bit much to post here... I probably need to show you the whole file...

 

[ozasis]

http://we.tl/CwOjSmBJuJ