if I input =3000/3 in cell A1, then the value in cell B1 is "/3"

[clarksonneo]

Hi,

In a column, I will input money received.

For example, I may input:
= 3000/3
= 4000/2

Excel will calculate them as 1000 and 2000 respectively.

What I want is that, for example,
if I input =3000/3 in cell A1, then the value in cell B1 is "/3"
if I input =4000/2 in cell A2 then the value in cell B2 is "/2"


Could you please me the macro for it?

Thanks

[Bob Phillips]

Why not just input

'/3

[clarksonneo]

Why not just input

'/3

because i want to learn more vba skills

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F1" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .HasFormula Then

If InStr(.Formula, "/") > 0 Then

.Value = Right$(.Formula, Len(.Formula) - InStr(.Formula, "/") + 1)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[clarksonneo]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F1" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .HasFormula Then

If InStr(.Formula, "/") > 0 Then

.Value = Right$(.Formula, Len(.Formula) - InStr(.Formula, "/") + 1)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub[/vba]
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

thanks for your answer
however, it doesn't work.
after I input = 1000/2 in cell b1, there is nothing input in cell b2.

in cell a1, I input = 1000/2.
i hope that, after I input =1000/2 in cell a1, /2 will be automatically input in cell b1




also
[vba]
Const WS_RANGE As String = "F1" '<<<< change to suit [/vba]
what do I need to change?

[mancubus]

[vba]
Const WS_RANGE As String = "F1" '<<<< change to suit
[/vba]

Change "F1" string to suit your real range.

eg. "A1:ZZ500"

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100" '<<<< change to suit

On Error Goto ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .HasFormula Then

If InStr(.Formula, "/") > 0 Then

.offset(0,1).Value = Right$(.Formula, Len(.Formula) - InStr(.Formula, "/") + 1)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub [/vba]

[clarksonneo]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A100" '<<<< change to suit

On Error Goto ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .HasFormula Then

If InStr(.Formula, "/") > 0 Then

.offset(0,1).Value = Right$(.Formula, Len(.Formula) - InStr(.Formula, "/") + 1)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub [/vba]

thank you
it works now

can I ask further questions?

at the 4th line, application.EnableEvents = False
why do you disable all events?

at the 3rd line, On Error Goto ws_exit
what error will you expect ?

[Bob Phillips]

I disable events so that the update of column B will not trigger another execution of this code.

I am not expecting an error, but it is a fool who says there will never be one - defensive programming.

[clarksonneo]

I disable events so that the update of column B will not trigger another execution of this code.

I am not expecting an error, but it is a fool who says there will never be one - defensive programming.

"I disable events so that the update of column B will not trigger another execution of this code."

hi, could you please explain a bit further?

what is the another execution mentioned in your answer?

thanks

[Bob Phillips]

Well, what happens is that you change A1 say, and the code starts to execute, and Target points to A1. When it gets to the line where it updates column B, this will cause the code to restart, but this time will point to B1. Not in this case, but it is possible that this cascade of events could go on forever, so you disable events to stop this cascade, and just have it execute the once.