Hi,

I have a list in column C.

In column J i'm using xl mid function to calculate a middle value in
column C.


Column C:
89111000111112
89111000111113
89111000111114
89111000111115
89111000111116
89111000111117
89111000111118

Column J: (using xl mid function)
001
001
001
001
001
001
001

The formula is dragged in column J to the last value in column C to
have the result.
I would like to have this formula in VB to claculate the same so that
there would be no need to drag the formula each time column C values
are changed.


Thx.

Hi Sinner

The code below would be one way to do it, though it will all depend on
if your numbers in column C being the same length. Also i added an
apostrophe to the start of the value in column J to stop it from
changing to show "1" and dropping the leading 00. This can be removed
from the code by deleting "'" & from the code.

Paste this code into the module for the sheet you want the code to
run.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then

Cells(Target.Row, 10).Value = "'" & Mid(Target.Value, 7, 3)

End If

End Sub

Hope this helps

Steve

Hi Steve,

J1 also changed : ). The values start 2nd row in column C i.e. C2 so
it should start from J2. First one is header.

Thx.


On Feb 21, 5:49*pm, Incidental wrote:
Hi Sinner

The code below would be one way to do it, though it will all depend on
if your numbers in column C being the same length. *Also i added an
apostrophe to the start of the value in column J to stop it from
changing to show "1" and dropping the leading 00. *This can be removed
from the code by deleting "'" & from the code.

Paste this code into the module for the sheet you want the code to
run.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then

* * Cells(Target.Row, 10).Value = "'" & Mid(Target.Value, 7, 3)

End If

End Sub

Hope this helps

Steve

When I select entire list in column C and clear or paste, it gives a
run-time error. type miss match.
Any idea??

Thx.

On Feb 21, 6:42*pm, Sinner wrote:
Hi Steve,

J1 also changed : ). The values start 2nd row in column C i.e. C2 so
it should start from J2. First one is header.

Thx.

On Feb 21, 5:49*pm, Incidental wrote:



Hi Sinner

The code below would be one way to do it, though it will all depend on
if your numbers in column C being the same length. *Also i added an
apostrophe to the start of the value in column J to stop it from
changing to show "1" and dropping the leading 00. *This can be removed
from the code by deleting "'" & from the code.

Paste this code into the module for the sheet you want the code to
run.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 3 Then

* * Cells(Target.Row, 10).Value = "'" & Mid(Target.Value, 7, 3)

End If

End Sub

Hope this helps

Steve- Hide quoted text -

- Show quoted text -

Hi Sinner

Sorry i should have thought, this should keep it right

If Target.Count = 1 Then

If Target.Column = 3 Then

If Target.Row < 1 Then

Cells(Target.Row, 10).Value = "'" & Mid(Target.Value, 7, 3)

End If

End If

End If

Steve

Steve when I select more than one cell and press delete, the contents
in C are cleared but values in J don't seem to disappear.
Any idea?


Thx.

On Feb 21, 7:47*pm, Incidental wrote:
Hi Sinner

Sorry i should have thought, this should keep it right

If Target.Count = 1 Then

* * If Target.Column = 3 Then

* * * * If Target.Row < 1 Then

* * * * Cells(Target.Row, 10).Value = "'" & Mid(Target.Value, 7, 3)

* * * * End If

* * End If

End If

Steve