Posts by Jong

    My preferred way to create buttons for macros in excel is to create a new commandbar. Your buttons will be incorporated directly in the Excel toolbar space. This works great.

    Here is some code to help you if you decide to do it this way:

    It is possible if you do it this way:

        Msgbox "A1:" & Range("A1") & "  A2:" & Range("A2") & "  A3:" & Range("A3") _
             & "  A4:" & Range("A4") & "  A5:" & Range("A5")

    My above post works, although it also swaps cells if you click on the E7 cells, and so on... Here is a modified version that will work a little better, it will automatically switch cells if the previous selected cell is E6. Same principle, paste this code in your worksheet object.

    I think i may be able to help you with this one. What you could do is setup an event handler in the worksheet you want this to happen.

    In your VBA editor, paste the following code in the worksheet object you want this ENTER sequence to happen on.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Address = "$E$7" Then
        ElseIf Target.Address = "$H$11" Then
        End If
    End Sub

    Simple enough.

    What you should do is something like this:

    The above code was not tested.

    If you copy/paste Special, you can specify to retain column width.

    Right click, and select Paste Special .. This will have quite a few paste options, one of which allows you to keep column width.

    If you are doing this through code, you can also use the .PasteSpecial property.

    .PasteSpecial Paste:=xlPasteColumnWidths

    To get a nice shade of grey, you can alter the default color palette in excel. Excel holds only 56 colors at a time.

    To alter the palette, do the following:

    ActiveWorkbook.Colors(INDEX) = RGB(R, G, B)

    For example, You can change Index 40 to read a nice shade of gray by doing:

    ActiveWorkbook.Colors(40) = RGB(240, 240, 240)

    Then you can use the ".ColorIndex" property to use your new color.

    I know how to clear a "personal" menu bar (one that was created).

    Here is the code:

    Function DeleteCommandBar()
        On Error Resume Next
            For Each cb In Application.CommandBars
            Next cb
        On Error GoTo 0
    End Function

    To delete a specific button, you can do the following by indexing it

    regardless of the matter, this project will require quite a bit of code to achieve. My suggestion is to split everything up into manageable functions.

    Have a function to search for a date range.
    Another to copy the info.
    Another to format the info.
    etc... depending on what you need

    If you need any extra help on a particular function that your working on, I will be glad to help further. The question is not quite narrow enough to write down some code for you.

    Let us know what problems you encounter, and we'll push you in the right direction.

    I forgot to mention, if you choose to use Regular Expressions, you will need to add a reference to "Microsoft VBScript REgular Expressions"

    In your VBA Editor, Under "Tools > References" you can add it there.

    Here's how you can do this with Regular Expressions

    Here's another way of doing this without RegExp

    Dim Count as integer
    for each cell in Worksheet("SheetName").Range("E1:E500")
        if inStr(cell.value, "WebOPRT") <> 0 then
            Count = Count + 1
        end if
    next cell

    something like this maybe?

    Something like this?

    'Range A2:Z8000 assumes where account information is held
    For each cell in Worksheets("Sheet 2").Range("A2:Z8000")
         if cell.value = "" then
             cell.value = Worksheets("Sheet 1").Range(cell.Address)
         end if
    next cell

    This is assuming that both worksheets have the same format. (Each account is held in the same Row Number, and all columns correspond to the same column ID).

    Your Sheet names must be specified correctly.
    The Data to be checked should be specified in the Range field.

    Yes, the IsNumeric Function will only be TRUE if the entire cell is a number.

    If there is a comma in the cell, it will be False.

    In your case, you cannot use the IsNumeric function, due to the fact that your cells contain commas. Now, if you know for a fact that the cell will either be empty, or contain numbers , then you can just check for a blank. If the cell is blank, don't count it. If the cell contains data, count it.

    Dim Counter As Integer 
    Counter = 0 
    For Each cell In Worksheets("MySheet").Range("D2:D351") 
        If cell.value <> "" Then 
            Counter = Counter + 1 
        End If 
    Next cell 
    MsgBox "There are a total of " & Counter & " Numbers in range D2:D351"

    Weird... Try doing the following (modified from my above code)