VBA Code for If Else?
Results 1 to 3 of 3

Thread: VBA Code for If Else?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code for If Else?

    I have the following code, in my worksheet known as “Out”, and what this code does is ever time I double click on a cell under column AB it Clears the Contents from that row, 25 cells to the left of column AB and the first 3 cells to the right of column AO on the same row. plus it paste the 1st cell value, which is my column C, into my worksheet known as “In”. So basically it’s moving names from sheet Out to sheet In.

    Now what I would like the following code to do is every time I double click on a cell under column AB which then Clears the Contents from on that row, 25 cells to the left etc, I then want the code to drop a “-“ sign in to my 1st cell, which is my column C, on the row that’s been double click. Can this be done by adding an If Else type statement into the code?
    If anyone can help it would be appreciated, thank you



    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Modified 6/18/2019 3:09 PM EDT
    If Target.Column = 28 And Target.Row > 1 Then
    Cancel = True
    Dim Lastrow As Long
    Lastrow = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Target.Offset(, -25).Resize(, 1).Copy Sheets(3).Cells(Lastrow, 1)
    Target.Offset(, -25).Resize(, 25).SpecialCells(xlCellTypeConstants).ClearContents
    Range("AO" & Target.Row).Resize(, 3).ClearContents
    End If
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code for If Else?

    Why not just add this line, before the End If
    Code:
    Range("AO" & Target.Row).Resize(, 3).ClearContents
    Target.Offset(, -25).Value = "-"
    End If
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code for If Else?

    Quote Originally Posted by Fluff View Post
    Why not just add this line, before the End If
    Code:
    Range("AO" & Target.Row).Resize(, 3).ClearContents
    Target.Offset(, -25).Value = "-"
    End If
    Thank you I will try

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •