Triggers events

[Bourbon]

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

 

[Brad]

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

 

[Bourbon]

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