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

ozasis

New Member
Joined
Feb 3, 2014
Messages
8
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:-

Code:
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!
 
i modified the code a bit. The line that references offset(0,23) is what column? In any case you can change as necessary

Code:
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
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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")

Code:
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:-

Code:
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
 
Upvote 0
Ignore me!!! I had a hidden column that was offset 23 columns!

All working flawlessly! Thanks for all your help Momentman! :)
 
Upvote 0
Sorry I had been away

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

You are welcome
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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