How do i target a specific range of cells from selection?

Question

What i want is to be able to select any amount of cells and press a button that will register information . all information is put horizontally, meaning that if i select L10 and press the button, N10,O10, and P10 will be changed according to what i tell them to.

i've been successful in doing this but it has a slight issue. as long as the information on the selected cells are unique, it works fine. but i want to be able to use column L, which will have random numbers that may frequently be the same as in other cells.

If cel.Value = Range("g16") Then                             

       Range("ff16").Value = True
       Range("p16").Value = Now

          If Range("m16").Value <= 0 Then
             Range("o16").Value = Range("o16").Value & " | " & VarNUMCB

          Else
          End If



  Else
    If cel.Value = Range("e16") Then
       Range("ff16").Value = True
       Range("p16").Value = Now
             If Range("m16").Value <= 0 Then
                Range("o16").Value = Range("o16").Value & " | " & VarNUMCB
             Else
             End If
    Else


    End If
  End If

expected:

L10 Selected,L11 Selected,L18 Selected,L23 Selected -> button is pressed -> Pop-up box asking for signature ->N10,N11,N18,N23 gets ticked, O10,O11,O18,O23 shows signature and P10,P11,P18,P23 shows date and time.

happens: if the value from L happens to be the same as any other random L cell, it will apply the changes to both, which i dont want to.

@tigeravatar what exactly do you need? i can try to show anything. what i expect to happen is, any selected cell other than from column L does not trigger anything. when the selected cell/multiple cells are from column L then it will change N,O and P horizontally. eg. select cell L4 and press button: N4,O4, and P4 will change. — user163342
Edit your question to include sample data before the macro gets run, point out what you would "select" from the sample data, and then show a second set of sample data showing how the data changed after the macro runs (this is your expected results). Makes it significantly easier to help. Right now we don't know what the values should update to, or why duplicate values affects your results. We also don't know what type of data you're working with. Are these numbers? text strings? dates? something else? — tigeravatar
@tigeravatar i don't have access to send screenshots. i know what the issue is but i dont know how to fix it with different code. on the line: ~~~~ If cel.Value = Range("g16") Then ~~~~ what is happening is the selection value will look literally for its value and i don't want to. i want it to look at the actual cell name. this problem makes it so that if my selection happens to be nothing it's considered 0, therefore anything else with a value of 0 also gets changed by the macro. — user163342
Don't need screenshots, just put some simple sample data in as text. And your code doesn't ever actually reference a "selection", you have it hardcoded to look at cells G16 and E16. And if you're worried about the cell being empty, just put in an extra IF statement to validate the cell isn't empty, like IF Len(cel.Value) = 0 Then 'Do Nothing — tigeravatar

tigeravatar tigeravatar · Accepted Answer · 2019-03-28T15:08:41

Maybe something like this is what you're looking for:

Sub tgr()

    Dim rSelected As Range
    Dim rCell As Range
    Dim sSignature As String
    Dim dtTimeStamp As Date

    'Verify that the current selection is a range (and not a chart or something)
    If TypeName(Selection) <> "Range" Then
        MsgBox "Invalid selection. Exiting Macro.", , "Error"
        Exit Sub
    End If

    'Get the signature
    sSignature = InputBox("Provide Signature", "Signature")
    If Len(sSignature) = 0 Then Exit Sub    'Pressed cancel

    'Get the current date and time
    dtTimeStamp = Now

    'Only evaluate selected cells in column L, ignore other selected cells
    Set rSelected = Intersect(Selection.Parent.Columns("L"), Selection)
    If rSelected Is Nothing Then
        MsgBox "Must select cell(s) in column L. Exiting Macro.", , "Error"
        Exit Sub
    End If

    'Loop through each selected L cell
    For Each rCell In rSelected.Cells
        '"Tick" same row, column N
        rCell.Offset(, 2).Value = "Tick"

        'Signature in same row, column O
        rCell.Offset(, 3).Value = sSignature

        'Date and time in same row, column P
        rCell.Offset(, 4).Value = dtTimeStamp
    Next rCell

End Sub

How do i target a specific range of cells from selection?

1 Answers