Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
Is it possible to type some text into a cell on page 2 and force the changes
to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
Assuming your notes are values of the column P cells, rather than comments on
those cells, this should work. Right click on the Sheet2 tab name, and select "View Code" from the popup list. Paste the following lines into the empty code screen that comes up. Whenever you make a change to column P on sheet 2, it will change column P on sheet 1 to be the same as what you entered, for the records with the same number in column B. Private Sub Worksheet_Change(ByVal Target As Range) Dim myNote As String, myID As Long Dim FirstRow As Long, RowCount As Long, myRow As Long If Target.Column = 16 Then myNote = Target.Value myID = Cells(Target.Row, 2) FirstRow = Sheets(1).UsedRange.Row RowCount = Sheets(1).UsedRange.Rows.Count For myRow = FirstRow To FirstRow + RowCount If Sheets(1).Cells(myRow, 2) = myID Then Sheets(1).Cells(myRow, 16).Value = myNote End If Next myRow End If End Sub "Looping through" wrote: Is it possible to type some text into a cell on page 2 and force the changes to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
You dont need to loop..Either use Range.Find or
worksheetfunction.Match...Select the sheet tab 2 which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. PS: The 1st sheet is referred as 'Sheet1;. Change to suit your requirement Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet, rngTemp As Range If Not Application.Intersect(Target, Range("P1:P100")) Is Nothing Then If Target.Cells.Count = 1 And Trim(Range("B" & Target.Row)) < "" Then Set ws = Worksheets("Sheet1"): Application.EnableEvents = False Set rngTemp = ws.Columns(2).Find(Range("B" & Target.Row)) If Not rngTemp Is Nothing Then ws.Range("P" & rngTemp.Row) = Target.Text End If End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Looping through" wrote: Is it possible to type some text into a cell on page 2 and force the changes to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
this will copy every entry on sheet2 to the same cell on sheet1. There's
nothing clever here. Right-click sheet2's tab, select View Code and paste this code..its pretty apparent what it does Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("sheet1").Range(Target.Address).Value = Target.Value End Sub "Looping through" wrote: Is it possible to type some text into a cell on page 2 and force the changes to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
Hi Patrick, I know you are disturbed by yesterdays post..and I see someone
has re-posted that as a reply...Well; here OPs requirement is slightly different from what you have suggested....and hence need a bit more coding..Thanks "Patrick Molloy" wrote: this will copy every entry on sheet2 to the same cell on sheet1. There's nothing clever here. Right-click sheet2's tab, select View Code and paste this code..its pretty apparent what it does Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("sheet1").Range(Target.Address).Value = Target.Value End Sub "Looping through" wrote: Is it possible to type some text into a cell on page 2 and force the changes to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
cheers
"Jacob Skaria" wrote: Hi Patrick, I know you are disturbed by yesterdays post..and I see someone has re-posted that as a reply...Well; here OPs requirement is slightly different from what you have suggested....and hence need a bit more coding..Thanks "Patrick Molloy" wrote: this will copy every entry on sheet2 to the same cell on sheet1. There's nothing clever here. Right-click sheet2's tab, select View Code and paste this code..its pretty apparent what it does Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("sheet1").Range(Target.Address).Value = Target.Value End Sub "Looping through" wrote: Is it possible to type some text into a cell on page 2 and force the changes to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cells and force most recent change
Jacob, Your the best...
"Jacob Skaria" wrote: You dont need to loop..Either use Range.Find or worksheetfunction.Match...Select the sheet tab 2 which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. PS: The 1st sheet is referred as 'Sheet1;. Change to suit your requirement Private Sub Worksheet_Change(ByVal Target As Range) Dim ws As Worksheet, rngTemp As Range If Not Application.Intersect(Target, Range("P1:P100")) Is Nothing Then If Target.Cells.Count = 1 And Trim(Range("B" & Target.Row)) < "" Then Set ws = Worksheets("Sheet1"): Application.EnableEvents = False Set rngTemp = ws.Columns(2).Find(Range("B" & Target.Row)) If Not rngTemp Is Nothing Then ws.Range("P" & rngTemp.Row) = Target.Text End If End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Looping through" wrote: Is it possible to type some text into a cell on page 2 and force the changes to a matching cell in sheet 1. I.E Sheet 2 line # 386, Note (Cell P386) = "10/1/09 - Should get order. 9/15/09 - Rep to follow up next week" Sheet 1 line 1000, Note (Cell P1000 = "9/15/09 - Rep to follow up next week" I want to be able to duplicate the notes in both worksheets without having to go find the referenced line item and re-type the same info twice. My hope is to keep everything consistant between sheets. FYI... the line items will share a common # in Cell B. Any help or direction will be great |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to force excel not to change date? | Excel Discussion (Misc queries) | |||
Force file name change | Excel Programming | |||
Compare multiple dates, can enumerate cell based on most recent date | Excel Worksheet Functions | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
Force format change in excel | Excel Worksheet Functions |