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

 

[maabadi]

1. what is your search area? (e.g. Column B from B8 to down)
2. you want search for one letter or more at the first of alphanumeric data? OR your criteria is ....

 

[fairin renish]

1. what is your search area? (e.g. Column B from B8 to down)
2. you want search for one letter or more at the first of alphanumeric data? OR your criteria is ....

yes exactly, the search is basically from Columns D to J and rows 150 to 250.

exactly search for the letter "A" in the first position of the cell text and if found true it should insert the data "E1017" in the next cell adding 7 to the numeric value and coverting the A to E


eg.

"A1010"
"E1017"

 

[fairin renish]

yes exactly, the search is basically from Columns D to J and rows 150 to 250.

exactly search for the letter "A" in the first position of the cell text and if found true it should insert the data "E1017" in the next cell adding 7 to the numeric value and coverting the A to E


eg.

"A1010"
"E1017"

The subsequent straight line after the "E1017" cell could be 7 cells long approx.

 

[maabadi]

I insert Cells to if you have data at below cell isn't repaced by macro.
If your first digit after "A" always Not Zero Try this:

Sub Macro2()
Dim i As Long, Lr As Long, j As Long
For j = 4 To 10
For i = 250 To 150 Step -1
If Left(Cells(i, j).Value, 1) = "A" Then
Cells(i + 1, j).Insert shift:=xlDown
Cells(i + 1, j).Value = "E" & Right(Cells(i, j).Value, Len(Cells(i, j).Value) - 1) * 1 + 6
End If
Next i
Next j

End Sub

 

[fairin renish]

Thanks for the quick reply

Its never zero , unfortunatelty it doesnt seem to work.


ive attached a sample copy of the table here.

I want excel to insert the subsequent "E xxxx" with a straight line succeding it below

 

[fairin renish]

Thanks for the quick reply

Its never zero , unfortunatelty it doesnt seem to work.


ive attached a sample copy of the table here.View attachment 35657
I want excel to insert the subsequent "E xxxx" with a straight line succeding it below

The code works in the sense that when i execute the vba code in the subsequent cell below manually, is there a possibility to automate the execution of the macros in the subsequent cell ?

 

[fairin renish]

The code works in the sense that when i execute the vba code in the subsequent cell below manually, is there a possibility to automate the execution of the macros in the subsequent cell ?

And also insert a "Shape" in this case a straight line pointing downwards

 

[fairin renish]

It also keeps adding the "E" to the subsequent cells previously added

 

[maabadi]

With this shapes you don't need Inserting cells.
I don't understand what you want for straight lines
Also you don't need mark as solution until take complete answer without problems.
try this and tell problems:

Sub Macro2()
Dim i As Long, Lr As Long, j As Long
For j = 4 To 10
For i = 250 To 150 Step -1
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
End If
Next i
Next j

End Sub