A Macro is called when value in column changes in Excel
2.5 هزار بار بازدید -
5 سال پیش
-
A Macro is called when
A Macro is called when value in column changes in Excel. Every time the value change in a column, the macro will run. The macro is triggered by a change In column values.
For more help visit my website http://www.easyexcelanswers.com or email me at [email protected].
Contact me regarding customizing this template for your needs.
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
I use a Blue condenser Microphone to record my videos, here is the link
https://amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
https://app.linkmink.com/a/crowdcast/83
If you need to buy Office 2019 follow
https://amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
https://www.Tubebuddy.com/easyexcelan...
Follow me on Facebook
Facebook: easyexcel.answers
TWEET THIS VIDEO A Macro is called when value in colum...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
http://www.youtube.com/timedtext_cs_p...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
code
Sub changestatnumber()
Dim stat As Range
Dim r As Integer
r = 7
Set stat = Sheet1.Range("A7:A71")
For Each cell In stat
If stat.Cells(r, 1).Value (symbol for not equal) stat.Cells(r - 1, 1).Value Then
Call enterhrs(r)
End If
r = r + 1
Next
End Sub
Sub enterhrs(r)
Dim x As Integer
Sheet1.Cells(r, 4).Select
'shift one
x = 1
For x = 1 To 1
ActiveCell.Value = "N"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 13)).Value = "N"
Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 20)).Value = "OFF"
Range(ActiveCell.Offset(0, 21), ActiveCell.Offset(0, 34)).Value = "D"
Range(ActiveCell.Offset(0, 35), ActiveCell.Offset(0, 41)).Value = "OFF"
ActiveCell.Offset(0, 42).Select
Next x
Sheet1.Cells(r + 1, 4).Select
x = 1
For x = 1 To 1
ActiveCell.Value = "D"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 6)).Value = "D"
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 13)).Value = "OFF"
Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 27)).Value = "N"
Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 34)).Value = "OFF"
Range(ActiveCell.Offset(0, 35), ActiveCell.Offset(0, 41)).Value = "D"
ActiveCell.Offset(0, 42).Select
Next x
Sheet1.Cells(r + 2, 4).Select
x = 1
For x = 1 To 1
ActiveCell.Value = "OFF"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 6)).Value = "OFF"
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 20)).Value = "D"
Range(ActiveCell.Offset(0, 21), ActiveCell.Offset(0, 27)).Value = "OFF"
Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 41)).Value = "N"
ActiveCell.Offset(0, 42).Select
Next x
End Sub
For more help visit my website http://www.easyexcelanswers.com or email me at [email protected].
Contact me regarding customizing this template for your needs.
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
I use a Blue condenser Microphone to record my videos, here is the link
https://amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
https://app.linkmink.com/a/crowdcast/83
If you need to buy Office 2019 follow
https://amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
https://www.Tubebuddy.com/easyexcelan...
Follow me on Facebook
Facebook: easyexcel.answers
TWEET THIS VIDEO A Macro is called when value in colum...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
http://www.youtube.com/timedtext_cs_p...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
code
Sub changestatnumber()
Dim stat As Range
Dim r As Integer
r = 7
Set stat = Sheet1.Range("A7:A71")
For Each cell In stat
If stat.Cells(r, 1).Value (symbol for not equal) stat.Cells(r - 1, 1).Value Then
Call enterhrs(r)
End If
r = r + 1
Next
End Sub
Sub enterhrs(r)
Dim x As Integer
Sheet1.Cells(r, 4).Select
'shift one
x = 1
For x = 1 To 1
ActiveCell.Value = "N"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 13)).Value = "N"
Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 20)).Value = "OFF"
Range(ActiveCell.Offset(0, 21), ActiveCell.Offset(0, 34)).Value = "D"
Range(ActiveCell.Offset(0, 35), ActiveCell.Offset(0, 41)).Value = "OFF"
ActiveCell.Offset(0, 42).Select
Next x
Sheet1.Cells(r + 1, 4).Select
x = 1
For x = 1 To 1
ActiveCell.Value = "D"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 6)).Value = "D"
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 13)).Value = "OFF"
Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 27)).Value = "N"
Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 34)).Value = "OFF"
Range(ActiveCell.Offset(0, 35), ActiveCell.Offset(0, 41)).Value = "D"
ActiveCell.Offset(0, 42).Select
Next x
Sheet1.Cells(r + 2, 4).Select
x = 1
For x = 1 To 1
ActiveCell.Value = "OFF"
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 6)).Value = "OFF"
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 20)).Value = "D"
Range(ActiveCell.Offset(0, 21), ActiveCell.Offset(0, 27)).Value = "OFF"
Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 41)).Value = "N"
ActiveCell.Offset(0, 42).Select
Next x
End Sub
5 سال پیش
در تاریخ 1398/11/13 منتشر شده
است.
2,541
بـار بازدید شده