Oct 27 2022 02:29 AM
Hi,
appreciate if anyone could help me to convert the formula below to vba code:
=IF(ISBLANK(I40),"",IF(I40<1,K40,K40+I40-1))
If cell I40 is blank, then M40 is empty, if cell I40 is <1, then M40=K40, otherwise M40=K40+I40-1.
Thank you very much.
Oct 27 2022 02:50 AM
Sub Makro1()
Dim i As Long
For i = 40 To 60
Range("M" & i).Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-4]),"""",IF(RC[-4]<1,RC[-2],RC[-2]+RC[-4]-1))"
Next i
End Sub
Maybe with these lines of code. In the attached file you can click the button in cell P40 to run the macro.
Oct 27 2022 03:15 AM
Hi @OliverScheurich , thanks for your prompt reply, actually i just need a simple vba which is whenever i insert a digit on cell I40, only will run the formula behind, instead of clicking a button to run the macro. I think is using Private Sub.
If cell I40 is empty, then M40 is empty, if i insert a digit on cell I40 is <1, then M40=K40, otherwise M40=K40+I40-1.
Im newbie to this community wondering how to upload my excel file :D
Oct 27 2022 03:25 AM
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("I40"), Target) Is Nothing Then
Range("M40").FormulaR1C1 = _
"=IF(ISBLANK(RC[-4]),"""",IF(RC[-4]<1,RC[-2],RC[-2]+RC[-4]-1))"
Else
End If
End Sub
You can try this code in the attached file. The result in cell M40 changes according to the entries in cell I40.
Oct 27 2022 07:10 PM
Oct 28 2022 01:11 AM
Hi @Quadruple_Pawn,
I have tried below formula but it seems not working, could you please help?
Thank you very much.
'view_range.Select
If Not Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Count > 1 Then
Exit Sub
End If
' clean the value
Debug.Print Target.Address
If Trim(Target.Value) <> "" Then
If Range("I" & Target.Row).Value < 1 Then
Range("M" & Target.Row).Value = Range("K" & Target.Row).Value
End If
If Range("I" & Target.Row).Value <= 1 Then
Range("M" & Target.Row).Value = ((Range("K" & Target.Row).Value) + (Range("I" & Target.Row).Value) - 1)
End If
End If
End If
Oct 28 2022 02:48 AM
SolutionPrivate Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Count > 1 Then
Exit Sub
End If
' clean the value
Debug.Print Target.Address
If Trim(Target.Value) <> "" Then
If Range("I" & Target.Row).Value < 1 Then
Range("M" & Target.Row).Value = Range("K" & Target.Row).Value
Else
Range("M" & Target.Row).Value = ((Range("K" & Target.Row).Value) + (Range("I" & Target.Row).Value) - 1)
End If
Else
Range("M" & Target.Row).Value = ""
End If
End If
End Sub
This code works in my sheet.
Oct 28 2022 03:43 AM
Oct 31 2022 03:39 AM
Can u please help me to convert the column P & Q to vba as well? Because the error pop up msg as below is annoying....it come out whenever i open this excel...
Im not sure if the formula is correct, but i would like to have The formula on column P is, IF column R = Completed or Make appointment, column P = column O - column N +1, else, column P is empty.
which means when column N= 30 oct, column O = 31 Oct, actual duration of day on column P =2 days (include the first day).
For column Q,
IF column R = Completed or Make appointment, column Q = column O - column K /7 and round up, else, column P is empty.
which means when column K= 30 oct, column O = 31 Oct, actual duration of weeks on column Q =1 week (include the first day); when column K= 20 oct, column O = 27 Oct, actual duration of weeks on column Q =1 week; when column K= 20 oct, column O = 28 Oct, actual duration of weeks on column Q =2 weeks.
For the above 2 formulas, No matter how the column R is change after once Completed or Make appointment is selected, column O P Q will remain unchange.
Attached below with the file for your perusal.
Thank you very much!!
Oct 31 2022 02:37 PM
There are cells with circular references in sheet "CompletedTask". For example in cell O84 there is a formula which contains the cell O84.
=IF(R84="Completed", IF(O84="",NOW(), O84), "")
This means the error message is not related to columns P & Q. You only have to correct the formulas in column O which have circular references.
Oct 31 2022 07:22 PM
Nov 01 2022 05:59 AM
You are welcome. I'm not sure what you want to do with columns P & Q. Maybe you want to start a new discussion and ask the experts of the Community.
Nov 01 2022 11:17 PM
Nov 02 2022 01:33 AM
Hi @OliverScheurich ,
I have tried to follow your step to clear all the formula on column O, but the error msg still popping out...
Then i look for the circular reference on formula tab but there is no more...
Could you please check for me please?
Im uploading the excel file for your perusal...
THank you very much...
Oct 28 2022 02:48 AM
SolutionPrivate Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("I:I"), Target) Is Nothing Then
If Target.Count > 1 Then
Exit Sub
End If
' clean the value
Debug.Print Target.Address
If Trim(Target.Value) <> "" Then
If Range("I" & Target.Row).Value < 1 Then
Range("M" & Target.Row).Value = Range("K" & Target.Row).Value
Else
Range("M" & Target.Row).Value = ((Range("K" & Target.Row).Value) + (Range("I" & Target.Row).Value) - 1)
End If
Else
Range("M" & Target.Row).Value = ""
End If
End If
End Sub
This code works in my sheet.