Incrementing complex data

[Stephen]

A macro to increment a cell's value in Excel 2007 is as easy as:


----------------------------
Sub Test()


x = ActiveCell.FormulaR1C1
x = x + 1

ActiveCell.FormulaR1C1 = x


End Sub
----------------------------


I have two questions here...


The first question is, what is the easiest way to make this work on every
cell of a particular column for a selected set of cells? For example, if I
select B310, and I want to increment every cell in Column C that is
selected? Does this require a complex looping setup, or is there an easy
way to set this up?


And the second question relates to the fact that the above code only works
if the cells contain simple data, such as integers, or a date. How can this
be made to work on complex data?


For example, if a cell contains data such as "1200x456", and we want to
increment whatever comes after the "x"? (So "1200x456" would become
"1200x457") Is this too much of a pain to make it worthwhile to code?

 

[Mike H]

Hi,

Your first question, B3 to d10 selected increment column C by 1. Note I
included no error trapping for text.

Sub Versive()
Set myrange = Selection
For Each c In myrange
If c.Column = 3 Then
c.Value = c.Value + 1
End If
Next
End Sub

I don't uderstand the second part "1200x456" Is this text or a
multiplication formula and you left out the = sign?

Mike

 

[mdmackillop]

You're not totally clear in your requirements, so here are 3 simpl
options to play with

Code
-------------------
'Increase last number by 1 in all cells
Sub Incr1()
Dim c As Range, t As Variant
For Each c In Selection
If c.Column = 3 Then
If InStr(1, c, "x") > 0 Then
t = Split(c, "x")
c.Value = t(0) & "x" & t(1) + 1
End If
End If
Next
End Sub

'Changee last number in all cells
Sub Incr2()
Dim c As Range, t As Variant
For Each c In Selection
If c.Column = 3 Then
If InStr(1, c, "x") > 0 Then
t = Split(c, "x")
c.Value = t(0) & "x" & InputBox("Change value " & t(1), "Increment")
End If
End If
Next
End Sub


'Use row number to increment values
Sub Incr3()
Dim c As Range, t As Variant, Rw As Long
'Get number of first row of selection
Rw = Selection(1).Row
For Each c In Selection
If c.Column = 3 Then
If InStr(1, c, "x") > 0 Then
t = Split(c, "x")
'Increment by difference in row numbers
c.Value = t(0) & "x" & t(1) + c.Row - Rw
End If
End If
Next
End Sub