CODEWITHSUNDEEP
excelvbarange
Swaike
Swaike

Reputation: 1

Changing the value in a range of cells

I have a sheet where I want to change the value in a range of cells in a different column than my selection. I use VBA because it's part of a code that sends information to a certain printer. I would like to let the macro also show that the selection has been printed.

To clarify:
- When I select F4:F10, I want the cells G4:G10 changed to a text value "Yes".
- When I select F4:F15, I want the cells G4:G15 changed to a text value "Yes".

I've searched the forums and I found/made the following:

Sheets("INPUT").Select
ActiveCell.Offset(0, 1).FormulaR1C1 = "Yes"

That works perfectly fine, however it only changes the value of one cell and not a range of cells.

Is there a way to change a range of cells to "Yes" instead of just one cell?

Upvotes: 0

Views: 628

Answers (4)

Swaike
Swaike

Reputation: 1

I got it working perfectly thanks to the above replies. Much appreciated!

I'm not sure how to close this thread or if that's nescessary, but "Case closed"

Upvotes: 0

Marcucciboy2
Marcucciboy2

Reputation: 3257

Okay, here's my shot at this problem. I'm not quite sure what triggers your current code so I thought it made sense to catch it via the Worksheet.SelectionChange Event.

This will update the value of all G4:G10 only if you've selected all of F4:F10 and likewise will only change G4:G15 if you select all of F4:F15.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim intersectedAll As Boolean
    Dim cell As Range


    Dim rng1 As Range
    Set rng1 = Range("F4:F10")

    If Target.count = rng1.count Then
        intersectedAll = True

        For Each cell In rng1
            If Intersect(Target, rng1) Is Nothing Then
                intersectedAll = False
            End If
        Next cell

        If intersectedAll = True Then
            Target.offset(0, 1).value = "Yes"
            intersectedAll = False
        End If
    End If


    Dim rng2 As Range
    Set rng2 = Range("F4:F15")

    If Target.count = rng2.count Then
        intersectedAll = True

        For Each cell In rng2
            If Intersect(Target, rng2) Is Nothing Then
                intersectedAll = False
            End If
        Next cell

        If intersectedAll = True Then
            Target.offset(0, 1).value = "Yes"
            intersectedAll = False
        End If
    End If

End Sub

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26660

In general, try to avoid using Selection where possible. Here's an alternate that prompts the user to select the range, and then updates the corresponding cells in the column to the right:

Sub tgr()

    Const sUpdatedValue As String = "Yes"

    Dim rSelected As Range
    Dim rArea As Range

    On Error Resume Next
    Set rSelected = Application.InputBox(Prompt:="Select Range of Cells." & Chr(10) & _
                                                 "Note that the cells in the column to the RIGHT of the selection will be updated.", _
                                         Title:="Range Selection", _
                                         Default:=Selection.Address, _
                                         Type:=8)
    On Error GoTo 0
    If rSelected Is Nothing Then Exit Sub   'Pressed cancel

    For Each rArea In rSelected.Areas
        rArea.Offset(, 1).Value = sUpdatedValue
    Next rArea

End Sub

Upvotes: 0

QHarr
QHarr

Reputation: 84465

Try 

Selection.Offset(0,1).Value = "Yes"

This is where Selection is your range selected. This is offset by 1 column and has value of "Yes" assigned in one go.

Upvotes: 1

Related Questions