Need help

[rizwan_planning]

hello all
good morning,
i am new to VBA

I want to genrate vba based on precent complete.
Let consider we have two cells C12 (done quantity) & D12(budget quantity)
E12=C12/D12

at start E12 is 0
if c12 is changed then A12 should reflact the date on which E12 become greater than 0
similarly B12 should reflact the date on which E12 become 100%

I had genrated the code

-------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range

Dim lastrow As Long
Dim c As Range, rng As Range
'change Sheet1 to suit
On Error GoTo ErrHandler
With ThisWorkbook.Worksheets("Sheet2")
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Each c In .Range("C1:C" & lastrow)
If UCase(c.Text) = "FALSE" Then
If rng Is Nothing Then
Set rng = .Range("C" & c.Row).Resize(, 20)
Else
Set rng = Union(rng, .Range("C" & c.Row).Resize(, 20))
End If
End If
Next c
End With

Set rChange = Intersect(Target, rng)
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell = "0" Then
If rCell > "0.1" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With

Else
rCell.Offset(-1, 1).Clear
End If
End If
Next
End If

ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
------------------------
i need the logic for this section
If rCell = "0" Then
If rCell > "0.1" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With

Else
rCell.Offset(-1, 1).Clear
End If
End If
Next

please help me
Thanks in advance
regards
R khan

 

[par60056]

Welcome to the forum.

This should be pretty easy to do but I am having a problem following your code. Your original message says that E=C/D but in the code you are looking at Column C for the text FALSE. Are the changes being done on Sheet1 and you are looking for the FALSE on Sheet2?

When posting code, it is helpful to use the CODE tags. Put CODE and /CODE inside [ ] around the code.

I think the code maybe overly complicated.

To see if the changed cell intersects where Cx="FALSE" you can do something like:
if (Ucase(Sheets("Sheet2").cells(target.row,3)="FALSE" then

I need a better description of the complete process to produce working code.

 

[onlyadrafter]

Hello,

This code assumes columns J (10), is available to put a value in.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("C:C")) Is Nothing Then
        If Cells(Target.Row, 10).Value = 0 Then
            Cells(Target.Row, 1).Value = Now()
            Cells(Target.Row, 10).ClearContents
        End If
        If Cells(Target.Row, 5).Value = 1 Then
            Cells(Target.Row, 2).Value = Now()
            Cells(Target.Row, 10).ClearContents
        End If
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Columns("C:C")) Is Nothing Then
        Cells(Target.Row, 5).Copy
        Cells(Target.Row, 10).PasteSpecial (xlPasteValues)
        Application.CutCopyMode = False
    End If
    Target.Select
    Application.EnableEvents = True
End Sub

The code needs to go into the relevant sheet code window, not a standard module.

Does it work as expected?

 

[rizwan_planning]

Hi par,

Thanks for reply.
The condition is such

If in column c if cell contain certain value "False or True”. If cell matches criteria "False" it goes to that row.

I that row I had formula which give percent complete. If value in that cell increase from 0 then it should give the date and time on which it change on row above 2.
Similarly if it reaches 100% then it should give value of date at which it reached on cell below 2 rows

Up to selecting row it work properly but after I can put logic for date change

please help
Regards,
Rizwan Khan