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]

i modified the code a bit. The line that references offset(0,23) is what column? In any case you can change as necessary

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim IARcolumn As Range
    Dim wks As Worksheet
    
    Set wks = Worksheets("IAR")
    
    If Not Intersect(Target, Range("NotesExpectedDate")) Is Nothing Then
        
        SearchValue = Left(Target.Offset(0, -5), 6)
        Set IARcolumn = Sheets("IAR").Range("IARcol")
        SearchCell = IARcolumn.Find(SearchValue, wks.Cells(3, 1)).Row
        wks.Range("A" & SearchCell).Offset(0, 23).Value = Target.Value 'Am not sure what column offset 23 is, change as necessary
    End If
    
    If Not Intersect(Target, Range("NotesResponsible")) Is Nothing Then


        'Target.Offset(0, -1).Value = "YES"


    End If
    
End Sub

 

[ozasis]

Thanks for helping. There don't seem to be any more errors, however it doesn't seem that the following line is actually changing the value of any cell in sheets("IAR")

wks.Range("A" & SearchCell).Offset(0, 23).Value = Target.Value

I've checked that the variables are working ok, but it doesn't seem to be working... I did some checking to see if everything was working ok by adding some msgbox's with varibale data:-

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim IARcolumn As Range
    Dim wks As Worksheet
    
    Set wks = Worksheets("IAR")
    
    If Not Intersect(Target, Range("NotesExpectedDate")) Is Nothing Then
        
        SearchValue = Target.Offset(0, -4)
        MsgBox "searching for " & SearchValue
        Set IARcolumn = Sheets("IAR").Range("IARcol")
        SearchCell = IARcolumn.Find(SearchValue, wks.Cells(3, 1)).Row
        MsgBox "searching in row " & SearchCell
        MsgBox "cell to change value is " & wks.Range("A" & SearchCell).Offset(0, 23).Value
        wks.Range("A" & SearchCell).Offset(0, 23).Value = Target.Value 'Am not sure what column offset 23 is, change as necessary
        MsgBox "after being changed it is " & wks.Range("A" & SearchCell).Offset(0, 23).Value
        MsgBox "Data that was going to be input was " & Target.Value
    End If
    
    If Not Intersect(Target, Range("NotesResponsible")) Is Nothing Then
        'Target.Offset(0, -1).Value = "YES"
    End If
    
End Sub

 

[ozasis]

Ignore me!!! I had a hidden column that was offset 23 columns!

All working flawlessly! Thanks for all your help Momentman!

 

[Momentman]

Sorry I had been away

Of course, your column X/Y was hidden, I noticed it when i ran the code

You are welcome