PDA

View Full Version : [SOLVED] Solved: Change selected cells to common range



pototoj
03-11-2013, 05:48 AM
HI I am quite sure many can answer this. But please change below code so it are working in Sheet3 range A!:A22.

thanks in advance

Potj.


Sub Minusone()
Dim cell As Range
For Each cell In Selection
If Application.IsNumber(cell) Then
cell.Value = cell.Value - 1
End If
Next cell
End Sub

patel
03-11-2013, 06:46 AM
Sub Minusone()
Dim cell As Range
For Each cell In range("A1:A22")
If Application.IsNumber(cell) Then
cell.Value = cell.Value - 1
End If
Next cell
End Sub

very very hard !

pototoj
03-11-2013, 07:27 AM
HI Thanks sorry i am not so familiar with macroes. How will it look, if you have to put it in a specific sheet?
Potj

patel
03-11-2013, 08:18 AM
Sub Minusone()
Dim cell As Range
For Each cell In Sheets("NameOfSheet").Range("A1:A22")
If Application.IsNumber(cell) Then
cell.Value = cell.Value - 1
End If
Next cell
End Sub

SamT
03-11-2013, 08:19 AM
Sub Minusone()
Dim cell As Range
For Each cell In Sheets("Sheet3").Range("A1:A22")
If Application.IsNumber(cell) Then
cell.Value = cell.Value - 1
End If
Next cell
End Sub
Even harder

pototoj
03-11-2013, 09:57 AM
HI Thanks to both of you. It works perfect. Sorry for my limited sklls in macroes. But all have its beginning. Thanks very much

Potj

SamT
03-11-2013, 10:48 AM
Expanded and preferred style:


Sub Minusone()
'This style of coding is very useful in long codes. It is advised
'to get used to it, so it becomes second nature.
'cell is very close to vba keyword, so use Cel instead
Dim Cel As Range 'Cel is used only for one cell in a range
Dim WkSht as Worksheet
Dim Rng As Range
Set WkSht = Sheets("Sheet3")
Set Rng = WkSht.Range("A1:A22")
For Each Cel In Rng
If Application.IsNumber(cell) Then
Cel.Value = Cel.Value - 1
End If
Next Cel
End Sub



'My own Standard Naming conventions
Dim WkSht as Worksheet 'WkSht is my standard variable for generic Worksheets
Dim Sht As Sheet ' Sht is my standard for generic Sheets
Dim ChtSht As Chart 'ChtSht is my standard for generic Chart Sheets
Dim Rng As Range 'Rng is my standard for generic Ranges
Dim Col As Long, Rw As Long 'Generic Column and Row
Dim i 'Standard loop counter for simple, single level loops
Dim NameIndex 'Loop counters for complex and multilevel loops
Const rcNameOs As Long 'Working with many columns, you will use many
' Cell.Offset(r, c) structures. the r or c prefix is for Row (r) or Col(c) offset.
'The Os suffix says that this is an Offset Constant.
'I assign the offset values to named Constants
' so my code is self documenting and for ease of changing the code if I
' have to change the corresponding table in any way.
Dim NamedVar ' Where var can be WkSht, Rng, or other when I use named Sheets,
'named Ranges, or other specific objects. Think about and develop your
'own standard naming system. It makes understanding your code much easier
'after you haven't looked at it in a while.

pototoj
03-11-2013, 01:23 PM
Thanks Sam i took a note about this. So thanks allot

Potj