Paul,
thanks a lot is working as I wanted, it will take me some time to figure it up
how is working, but its so gooood.
Thanks
Pawel
________________________________
From: Paul Schreiner <schreiner_p...@att.net>
To: excel-macros@googlegroups.com
Sent: Friday, November 2, 2012 2:12 PM
Subject: Re: $$Excel-Macros$$ execute macro on delate
I have a couple of solutions for you.
(with lessons along the way)
First, in the Change Event, Target CAN be more than one cell.
For example, if you decide to clear all values in the column,
you'd select the range and hit "delete".
Then, Target becomes an ARRAY of cells.
What I like to do is process each cell individually.
By using:
Dim Targ as Range
For Each Targ in Target
you can loop through all the cells in the range "Target".
Whether there's a single cell, or a range of cells.
Second, you can test the value of the Target cell.
If it's blank, then delete the value in column "D" of the same row as the
Target cell.
Otherwise, run your "Weekstock" macro.
Like:
====================================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim Targ As Range
' The variable KeyCells contains the cells that will
' cause an macro run when they are changed.
Set KeyCells = Range("c3:c300")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
For Each Targ In Target
'--------------------------------------
If Targ.Value <> "" Then 'Do this if the cell is not blank
'--------------------------------------
' designated cells has been changed.
Call WeekStock
'--------------------------------------
Else 'Do this if the cell is blank
'--------------------------------------
Cells(Targ.Row, "D").Value = "" 'Remove value in Column "D"
End If
Next Targ
End If
End Sub
====================================================================
That should address your first issue.
To address the second issue:
You're using the "Intersect" method.
While this works, there's a simpler method.
Simply test the target column to see if it is 3 (for C)
However, since you have multiple "input" columns,
I'd suggest testing to see if the column heading says "input", then proceed
accordingly.
Combinine these two techniques, you get:
==============================================================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Targ As Range
For Each Targ In Target
If (UCase(Cells(2, Targ.Column).Value) = "INPUT") Then
'--------------------------------------
If Targ.Value <> "" Then 'Do this if the cell is not blank
'--------------------------------------
Call WeekStock
'--------------------------------------
Else 'Do this if the cell is blank
'--------------------------------------
Cells(Targ.Row, "D").Value = "" 'Remove value in Column "D"
End If
End If
Next Targ
End Sub
================================================================================
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: pawel lupinski <lupins...@yahoo.com>
To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
Sent: Fri, November 2, 2012 9:20:29 AM
Subject: Re: $$Excel-Macros$$ execute macro on delate
Hi All,
I should attached file with it. about te file - this simple calculator for
stock week cover. So input figure in columns "input" and calculate you number
of weeks cover.
With this file I have 2 problems to resolve:
I've made simple macro to calculate week covers (when you inset value in column
C in column D macro calculate weeks:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an macro run when they are changed.
Set KeyCells = Range("c3:c300")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' designated cells has been changed.
Call WeekStock
End If
End Sub
Sub WeekStock()
Dim a As Integer, number As Integer
a = ActiveCell.Column + 3
Do Until number = 12
If Cells(ActiveCell.Row - 1, a + 5 * number).Value < 0 Then Exit Do
number = number + 1
Loop
Cells(ActiveCell.Row - 1, ActiveCell.Column + 1).Value = number
End Sub
Problem #1 (delete):
when I delete value in cell - macro calculate me number of weeks cover for row
above.
and I don't know how to right macro that on click will delete value in next
column -> so remove value in cell (column C) and remove value in cell (column
D).
Problem #2 (ranges):
As you see I've only one range ("C3:C300"), but I'd like to have multiple e.g.
column H, M, R ... etc.
Sorry I'm new to VBA and I'll be appreciated for your help
Regards,
Pawel
From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of pawel lupinski
Sent: 02 November 2012 5:35
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ execute macro on delate
Hi All,
I need your help on this. I'd like to run macro when I delate value from the
cell. So I pushing "delate" and macro run. I couldn't find anything on the web
so if you can help me on this.
regards,
Pawel
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
