How to work with the range method with only one specific cell?

Question

I would like the cells I have selected in the spreadsheet to receive the +1 increment. The code below works fine when I have a range, but when I have only one cells selected the code adds +1 to every cell in the spreadsheet.

    Sub Macro_MAIS_1()
'
' Macro_MAIS_1 Macro
'

'

Dim AlocationWorksheet As Worksheet
Dim ActSheet As Worksheet
Dim SelRange As Range

Dim iCells As Integer

On Error GoTo Fim

Set AlocationWorksheet = Worksheets("ALOCAÇÃO")
AlocationWorksheet.Unprotect
Set ActSheet = ActiveSheet
Set SelRange = Selection.SpecialCells(xlCellTypeVisible)


iCells = SelRange.Cells.Count


    Range("O7").Select
    Selection.Copy

    SelRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False
    Exit Sub
        
Fim:
    MsgBox Selection.Address
    Range("O7").Select
    Selection.Copy
    
    SelRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
        :=False, Transpose:=False

End Sub

Why do you get the cell count if you don't use it? You would probably benefit from [avoiding the use of select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. Try removing everything after you set `SelRange` then use `SelRange.Value = Range("O7").Value` — cybernetic.nomad, Jul 29 '21 at 21:48

pgSystemTester · Accepted Answer · 2021-07-30T03:54:40.500

I would avoid using a selection, but this should work. If you have text you'll run into trouble and need to write out some checks. You also should not be counting all cells, as you might have an overflow of values. Check rows and columns, but not both.

Sub addPlusOne()
Dim aRange As Range, i As Long, j As Long
    Set aRange = Selection
    
If aRange.Rows.Count > 1 Or aRange.Columns.Count > 1 Then
    Dim zRng()
    zRng = aRange.Value
        
        For i = LBound(zRng) To UBound(zRng)
            For j = LBound(zRng, 2) To UBound(zRng, 2)
                zRng(i, j) = zRng(i, j) + 1
            Next j
        Next i
    
    aRange.Value = zRng
Else
    aRange.Value = aRange.Value + 1
End If

End Sub

EDIT: OP commented that they want to use visible selection. While this isn't best practice, this will work.

Sub plusOneOnSelection()
Dim aCell As Range

For Each aCell In Selection.SpecialCells(xlCellTypeVisible).Cells
    If IsNumeric(aCell) Then aCell.Value = aCell.Value + 1
Next aCell


End Sub

The code is good, but I found an important difference. If I have a hidden line in the range, the code won't fill all the cells. Hidden lines must not be fills, but all visible cells in the range must be. That's why I was using the "Selection.SpecialCells(xlCellTypeVisible)" — caezar, Jul 30 '21 at 03:43
As the comments and my answer state, there's probably a better way to do what you're trying to do. I'll post a workaround code in 5 minutes, but it'll be slower and isn't taking your project in a good direction. — pgSystemTester, Jul 30 '21 at 03:48

How to work with the range method with only one specific cell?

1 Answers1