-
detect changed cell in a column
Hi everyone!
A have a worksheet with the following data:
AZ5=4 , BA5=3, BB5=2, BC5=1
in column AP , I have a function in specific cells who copies the values from above cells:
AP42=3, AP47=3, AP52=2, AP57=1, AP60=1 and so on.. (about 100 cells copies data from cells above)
What I want: when I change values in AZ5,BA5,BB5,BC5 the cells from column AP (AP42,AP47,AP52,AP57,AP60... with the function "copiereNrTura" in it) to copy values, and changed cell (AP42,AP47,AP52,AP57,AP60..) to trigger another procedure who will write something in another cells (A42:AI42,A47:AI47,A52:AI52,A57:AI57,A60:AI60..). How I will do that??
Many thanks for your replies!
What I have:
(this is the function who copies values form AZ5,BA5,BB5,BC5):
[vba]
Function copiereNrTura(adresaTura As Range)
copiereNrTura = adresaTura.Value
End Function
[/vba]
How I will trigger the canged cell to run the procedure to write in (A17:AI17, A42:AI42,...)?
-
A simplified example
[vba] Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "A1", "A2", "A3"
Macro1 Target
Case "A4", "A5", "A6"
Macro2 Target
Case Else
Macro3
End Select
Application.EnableEvents = True
End Sub
Sub Macro1(Target As Range)
Target.Offset(, 3) = Target
End Sub
Sub Macro2(Target As Range)
Target.Offset(, 5) = Target
End Sub
Sub Macro3()
MsgBox "Nothing to do"
End Sub
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Thank you for reply
Another thing I want to do :
In a cell I have a "data validation list" (R11) . When I select an item from the drop-down list of DV (data validation) I want to trigger a subrutine to write a value in another cell (X11) but it isn't work. Only thing that is working is the message box that appears but the range X11 remains empty.
[VBA]
Private Sub Worksheet_Change(ByVal target As Range)
Select Case target.Address(False, False)
Case "W11"
Range("Y11").Value = 8
Case "R11"
MsgBox "It's not working!"
Range("X11").Value = 4
'Intersect(target.EntireRow, Cells(11, "X")).Value = 4
'target.Offset(0, 6)=4
End Select
End Sub
[/VBA]
The italic lines are commented and are another option I have tried but still not working. I'm using Excel 2002
-
The basic code is OK, but you need to prevent the code from looping; changing X11 triggers the code
Try
[VBA]Private Sub Worksheet_Change(ByVal target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case target.Address(False, False)
Case "W11"
Range("Y11").Value = 8
Case "R11"
MsgBox "It's not working!"
Range("X11").Value = 4
'Intersect(target.EntireRow, Cells(11, "X")).Value = 4
'target.Offset(0, 6)=4
End Select
Exits:
Application.EnableEvents = True
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Still not working.. Just the message box appears but
[VBA]Range("X11").Value = 4[/VBA] has no effect, the cell is empty.
-
Hmmm , I read it now somewhere to an article on the internet that my file to be corrupted...
-
I copied in a new workbook but same problem...
-
I've tested this in 2003 and 2007. Can you post your workbook?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules