Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggers events
I think this is what is called an event trigger, but how to use?
Columms C to L have data in them from rows 30 to row 68. Each columm i a different company (thus there are 10 companies). Each columm i numbered 1 to 10 (thus C has number 1 in row 28, D has number 2 in ro 28, etc)...up to number 10 in columm L row 28. Columm M and N are empty but I want to trigger an event in ro 28....when I enter for example, number 3(in row 28M) and number 6(i row 28N), it will copy and paste the data from the correspondin columms that have does numbers in row 28 into columm M and N... Any thoughts? Thanks, B -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggers events
Hi Bourbon,
You described your data listing well, but it sounds wierd, let me know if I misunderstood. Put this code in the worksheet module where you want the functionality created. Private Sub Worksheet_Change(ByVal Target As Range) ' Identify which cell was changed. Select Case Target.Address Case "$M$28" Call subRetrieveData(Target) Case "$N$28" Call subRetrieveData(Target) End Select End Sub Private Sub subRetrieveData(rngTarget As Range) ' This procedure will paste the appropriate rows of data ' to the cell that called it. If rngTarget.Value = "" Then Call subClear(rngTarget) Exit Sub End If Dim shtCurrent As Worksheet Set shtCurrent = rngTarget.Worksheet Dim rngCompanyNumbers As Range Set rngCompanyNumbers = shtCurrent.Range("C28", "L28") Dim rngCurrentColumn As Range Set rngCurrentColumn = rngCompanyNumbers.Find (rngTarget.Value, , xlValues, xlWhole) If rngCurrentColumn Is Nothing Then MsgBox "Company not found." Call subClear(rngTarget) Exit Sub End If Dim rngSourceData As Range Set rngSourceData = shtCurrent.Range (rngCurrentColumn.Offset(2, 0).Address, rngCurrentColumn.Offset(40, 0).Address) Dim rngPasteTarget As Range Set rngPasteTarget = rngTarget.Offset(2, 0) rngSourceData.Copy rngPasteTarget.PasteSpecial xlPasteAll rngTarget.Select End Sub Private Sub subClear(rngTarget As Range) ' This procedure will clear the column's data. Dim shtCurrent As Worksheet Set shtCurrent = rngTarget.Worksheet Dim rngPasteTarget As Range Set rngPasteTarget = shtCurrent.Range(rngTarget.Offset (2, 0).Address, rngTarget.Offset(40, 0).Address) rngPasteTarget.Clear End Sub HTH. -Brad -----Original Message----- I think this is what is called an event trigger, but how to use? Columms C to L have data in them from rows 30 to row 68. Each columm is a different company (thus there are 10 companies). Each columm is numbered 1 to 10 (thus C has number 1 in row 28, D has number 2 in row 28, etc)...up to number 10 in columm L row 28. Columm M and N are empty but I want to trigger an event in row 28....when I enter for example, number 3(in row 28M) and number 6(in row 28N), it will copy and paste the data from the corresponding columms that have does numbers in row 28 into columm M and N... Any thoughts? Thanks, B. --- Message posted from http://www.ExcelForum.com/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Triggers events
Nope, it did not work. I have attached a template of what my workshee
looks like, you will undestand what I mean and maybe be able to hel me.. Thanks B Attachment filename: template.xls Download attachment: http://www.excelforum.com/attachment.php?postid=41840 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro triggers Virus Scanner | New Users to Excel | |||
How to activate triggers in Excel | Setting up and Configuration of Excel | |||
Triggers to buy/sell based on histogram | Excel Discussion (Misc queries) | |||
Macro that triggers off a combo box | Excel Discussion (Misc queries) | |||
code for lookup,& triggers. | Excel Programming |