Adding alphanumeric data when a particular alphabet is recognised within the cells

[fairin renish]

i am looking for a vba code that would check if any of my cell contents have alphanumeric data in the form “A1010” and return a value “E1017” in the cell below i.e. adding 7 to the numbers and switching “A” to “E” in the cell below.

subsequently it must insert a straight line below the “E1017” cell pointing towards the next “A1
Trying to create a project for an internal logistic process.

Would really appreciate your help

Best regards

 

[fairin renish]

This works great, thank you soo much.

is there a way to change this part

If Left(Cells(i, j).Value, 1) = "A" Then
Cells(i + 1, j).Value = "E" & Right(Cells(i, j).Value, Len(Cells(i, j).Value) - 1) * 1 + 6

INTO Active cell, with that when i press "Return" from the previous it moves to the next cell inserting the content.

 

[maabadi]

1. What means press "Return"?
2. What you want do exactly?

 

[fairin renish]

1. What means press "Return"?
2. What you want do exactly?

e.g. need the macro to return the value "E1016" on the next cell automatically on pressing "Enter" after finishing entering the first cell value "A1010"

 

[maabadi]

For this situation you should use worksheet change event not normal macro then:
1. At Excel window right click on sheet name that you have these data (or at Visual basic code at Left Pannel Project) )
2. Select View code then Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim i As Long, j As Long
    If Not Intersect(Target, Range("D150:J250")) Is Nothing Then
     If Target.Count = 1 Then
     Application.EnableEvents = False
       If Left(Target.Value, 1) = "A" Then
        Target.Offset(1, 0).Value = "E" & Right(Target.Value, Len(Target.Value) - 1) * 1 + 6
       End If
     Application.EnableEvents = True
      End If
    End If
End Sub