[RESOLVED] Wildcarding a range of columns

[geelsu]

How is a specific set of columns wild-carded, if it is possible. I'd rather not have to do the following:

If Intersect(ActiveCell, Range("H29, N29, T29, Z29, all the way through HV29 ")) Is Nothing Then
            MsgBox "Must select the date column.", vbCritical + vbOKOnly
            Exit Sub
          End If

The Row value of 29 is not important. I guess if I pseudo-coded it I would have:

If The Column is H or every sixth column starting at H and going up to HV THEN
    do the operation 
ELSE
    Msgbox Can't start here.
End IF

I'm not sure if the Column values should be in an array type construct like
ARRAY=(H,N,T,Z ..... HZ)

Any ideas.

[westconn1]

you could try like

with target    ' or use activecell
  if .column >7 and .column > range("hv1").column +1 and (.column -8) Mod 6 = 0 Then
        'do operation
  end if
end with

[geelsu]

Thanks westconn1. Thank you for your input. I really appreciate it.

Here is what I did. Is this what you intended me to try?

Private Sub Clear_Transaction()
         Dim val As Integer
         With ActiveCell    ' or use activecell
             If .Column > 7 And .Column > Range("hv1").Column + 1 And (.Column - 8) Mod 6 = 0 Then
                 MsgBox "Must select the date column.", vbCritical + vbOKOnly
             End If
         End With

             val = MsgBox("Make sure you first click on the DATE field to start your clear." & ActiveCell.Address & "?", vbYesNo)
             If val = 6 Then '6=Yes
                   Range(ActiveCell, ActiveCell.Offset(0, 3)).ClearContents
             Else
                   'didn't click yes
             End If
End Sub

Right now, if I have done the above correctly I don't get the MsgBox "Must select the date column.", vbCritical + vbOKOnly, but get the second MsgBox no matter what cell I click in. Attached is a typical area I am working with. In this case, I would want to make sure they are in the CN column. Right now if I click in CO33 or CP35, for example, and then click my Clear Transaction button, I'm not getting the first MsgBox instructions. Also, I think, the second Msgbox would become unneeded once I get the first working as I could then just clear the line once in the correct column, though I might change it to a "Are you sure?" type warning.



Am I headed in the correct direction with what you intended?

[geelsu]

Also, and just a thought, since the column has a DATE string in it can that be used as a TAG in some way or a

If DATE exist (are does not exist) in this column then

type construct?

[westconn1]

I don't get the MsgBox "Must select the date column.", vbCritical + vbOKOnly,

the way you have the code, you will only get that first msgbox if you are already in a date column cell, so at that point i would say you then just want a
if msgbox ("Delete this transaction", vbyesno) = vbyes then 'clear cells

you could also use if isempty(activecell) then ' nothing to delete
and /or if not isdate(activecell) then ' wrong place warn user

your construct should be like
if in one of those columns then
delete or not
else msgbox to select one of those columns

NOTE if not already in one of those columns, user will have to click button again after selecting cell in the columns


might be better to work out the date column from the activecell column, then ask the user if they want to delete the transaction containing the activecell in any column

pia for user to have to click the button twice

i would probably try like

With ActiveCell
    addr = .Address
    .Offset(, -(.Column - 8) Mod 6).Resize(, 6).Select
    If MsgBox("Delete selected range", vbYesNo) = vbYes Then    ' or "delete selected transaction"
        Selection.ClearContents
        Else
        Range(addr).Select   ' go back to previous cell
    End If
End With

[geelsu]

Hey Westconn1, Thank you again for your input. We are almost there I think.

Your code works perfectly for clearing a range, but still clears the range no matter where I click ... at least in the command interpreter my Excel is using. I also think I am failing to effectively explain what I need for which I am sorry. Let me try again and, by the way, I do thank you for your patience with me. If you look at the following block of code which DOES NOT work, but ... hopefully ... better explain where I am trying to go maybe you can help me fix it so it does work.

Private Sub Clear_Transaction()

    Dim SafeColumn As Range
    
    ' Define a set of safe columns to BEGIN the clear operation in.
    ' You must be in one of these columns to begin your clear operation.
    ' This code obviously does not work, but provides the concept.
    ' Example 8 is safe, but NOT 9,10,11,12, or 13.  Next safe column is 14 but NOT 15,16,17,18, or 19 and so forth.
      Set SafeColumn = Range("8,14,20,26,32,38,44,50,56,62,68,74,80,86,92,98,104,110,116,122,128,134,140,148,154,160,166,172,178,184,190,196,202,208,214,220,226,232")
      If ActiveCell = one of these Range Values Then      ' Clear the cells.
End Sub

It almost seems like a simple (in concept) mathematical operation would do this i.e by adding 6 after starting at 8 i.e. 8, 8+6, 8+12, etc etc. But I have no idea how to code that.

Now, the below code actually works the way I want it to, only I would need a very very long line of "OR ActiveCell.Column" values, 39 of them to specific.

 

Private Sub Clear_Transaction()

    '    If the active cell is one of the safe columns then clear
    '    This code actually works, but would be extremely long in its final form
     If ActiveCell.Column = 8 Or ActiveCell.Column = 14 Or ActiveCell.Column = 20 Then   '  Would need 36 more of these

      ' This excellent code provide by westconn1 at vbforums.com
        With ActiveCell
        addr = .Address
        .Offset(, -(.Column - 8) Mod 6).Resize(, 6).Select
            If MsgBox("Delete selected range", vbYesNo) = vbYes Then    ' or "delete selected transaction"
                Selection.ClearContents
            Else
                Range(addr).Select   ' go back to previous cell
            End If
        End With
    Else
        Exit Sub
    End If
    
End Sub

Thank you for your help. It really is appreciated.

[vbfbryce]

To find out if the active cell is in a "safe column," try something like this:

Sub clearData()
    Dim col As Integer
    Dim myMod As Integer
    
    col = ActiveCell.Column
    myMod = (col Mod 6) - 2
    If myMod = 0 Then
        'in safe column
        'expand selection, confirm delete
    End If
End Sub

[geelsu]

Hey vbfbryce. Great to hear from you again. Hope all is well.

ARE YOU STINKING KIDDING ME!!!!! That worked perfectly ... so simple, so elegant. Hold on while I run to the top of the building to shout ... ok I'm back.

Here is the final code for any that may want to venture down this path:

Private Sub Clear_Transaction()

' Kudos to westconn1 and vbfbryce at vbforums.com for their excellent input.
    Dim col As Integer
    Dim myMod As Integer
     
    ' If the active cell is one of the safe columns then clear cells on that line.
    col = ActiveCell.Column
    myMod = (col Mod 6) - 2
        If myMod = 0 Then
        With ActiveCell
            addr = .Address
            .Offset(, -(.Column - 8) Mod 6).Resize(, 6).Select
                If MsgBox("Delete selected range", vbYesNo) = vbYes Then  ' or "delete selected transaction"
                    Selection.ClearContents
                Else
                    Range(addr).Select
                End If
            End With
        Else
            MsgBox ("Can not clear the line starting in this cell. Try again.")
            Exit Sub
        End If
    
End Sub

You guys rock!

[vbfbryce]

99.99% of the credit belongs to Pete (but you're welcome!).

[westconn1]

i thought the code, as i posted in #5, was pretty good option

[geelsu]

You are quite right westconn1. I did not mean to diminish your contributions. Your code and idea is the very heart of all this and provided the springboard to success. I would be nowhere with out them. Job well done and, again, thank you, sincerely.