Change 1 character in cell

[austenr]

If I have something is cell A1 like "DHSPF1Grade" and I want to copy this across to B2 to B40 and increment the number in the text by one each time it is pasted can you do that?

[GTO]

Hi,

Presuming you mean DHSPF1Grade | DHSPF2Grade | DHSPF3Grade | etc...

Is it always going to be the same size range, or could we use the Selection and run it out from the first cell?

[austenr]

No the text will be sifferent string lengths. Was hoping to isolate the number somehow and increment no matter where it appeared. Might be too much to ask for.

[GTO]

Sorry, I wasn't asking about the input string len, rather if it was always going to be x columns.

In a junk copy of your wb, see if this is close.


Option Explicit

Sub AutoComp()
Dim _
rexMatches As Object, _
rSel As Range, _
LeftString As String, _
RightString As String, _
NumberBetween As Long, _
i As Long

Set rSel = Selection

With CreateObject("VBScript.RegExp")
.Global = True
.MultiLine = False
.IgnoreCase = True
.Pattern = "([A-z]+)([0-9]+)([A-z]+)"

If rSel.Rows.Count = 1 _
And rSel.Columns.Count > 1 _
And .Test(ActiveCell.Value) Then

Set rexMatches = .Execute(ActiveCell)

LeftString = rexMatches(0).SubMatches(0)
NumberBetween = CLng(rexMatches(0).SubMatches(1))
RightString = rexMatches(0).SubMatches(2)

For i = 2 To rSel.Cells.Count
rSel(i).Value = LeftString & NumberBetween + i - 1 & RightString
Next
End If
End With
End Sub

I am at baby steps with RegExp, but I think this should grab the pattern you have shown.

Hope that helps,

Mark

[GTO]

Oops. You might want to add this to the IF:
And rSel(1).Column = ActiveCell.Column Then

[mdmackillop]

="DHSPF" & COLUMN() & "Grade"
or
="DHSPF" & ROW() & "Grade"
depending which way you are going.
You can easily adjust by a fixed number if you don't start in Row or Column 1

[Bob Phillips]

=SUBSTITUTE(B2,MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},"")))),
MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},""))))+1)

[austenr]

Not sure what that formula does Bob but I'll give it a shot.

[austenr]

How do you use that formula if you have data in B2?

[Bob Phillips]

You put the formula in C3, and copy across.

[Aussiebear]

=SUBSTITUTE(B2,MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},"")))),
MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),SUMPRODUCT(LEN(B2)-LEN(SUBSTITUTE(B2,{0,1,2,3,4,5,6,7,8,9},""))))+1)

....Stop looking everyone, I've found the "missing" launch code.:devil2:

[mdmackillop]

I have something is cell A1 like "DHSPF1Grade" and I want to copy this across to B2 to B40
This bit confused me.:old:

[mdmackillop]

....Stop looking everyone, I've found the "missing" launch code.:devil2:

Unfortunately it belongs to Beagle 2 (http://www.heralddeparis.com/flaw-theory-over-mars-beagle-loss/15227)

[Bob Phillips]

This bit confused me.:old:

Oops, looks like I conveniently glossed over the A1 bit.

[austenr]

How many years have you been writing these formulas Bob? Or is it a gift? Im sure it would take most of us (well the majority of us) a few days to come up with something that complex. Some of us never like me. :bow:

[Bob Phillips]

Hey Austen,

I like formulas, so I play about with them as much as I can. Obviosuly, the more you use them, the better you get, ptractice makes perfect. I also like to present at conferences, so I have to make sure those skills are honed even sharper.

I had a need some time ago to extract the numeric portion of a text string in a cell; it looked a useful tool, so I saved it in my library. I do this with lots of my formulae solution, saves re-inventing them each time :)