PDA

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



clarksonneo
05-12-2011, 05:55 AM
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
05-12-2011, 06:20 AM
Why not just input

'/3

clarksonneo
05-12-2011, 06:21 AM
Why not just input

'/3

because i want to learn more vba skills

Bob Phillips
05-12-2011, 06:31 AM
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

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
05-12-2011, 07:39 AM
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
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

Const WS_RANGE As String = "F1" '<<<< change to suit
what do I need to change?

mancubus
05-12-2011, 07:51 AM
Const WS_RANGE As String = "F1" '<<<< change to suit


Change "F1" string to suit your real range.

eg. "A1:ZZ500"

Bob Phillips
05-12-2011, 07:55 AM
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

clarksonneo
05-12-2011, 08:22 AM
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
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
05-12-2011, 08:27 AM
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
05-13-2011, 04:29 AM
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
05-13-2011, 04:47 AM
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.