CODEWITHSUNDEEP
excelvbarange
user3773028
user3773028

Reputation:

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

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

Upvotes: 1

Views: 435

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

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

Upvotes: 0

Related Questions