Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing complex data
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 B3:D10, 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing complex data
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 "Stephen" wrote: 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 B3:D10, 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incrementing complex data
You're not totally clear in your requirements, so here are 3 simple 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 -------------------- -- mdmackillop ------------------------------------------------------------------------ mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64367 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
data range is too complex. | Charts and Charting in Excel | |||
Incrementing Data based on user input | Excel Discussion (Misc queries) | |||
complex data validation | Excel Discussion (Misc queries) | |||
Complex Data Validations | Excel Programming |