Posts by Jong

First link you should visit is:
http://peltiertech.com/Excel/ChartsHowTo/

Once you read all that , you can check out the following:

http://www.contextures.com/JPChartIndex.htm
http://www.j-walk.com/ss/excel/usertips/charttips.htm

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:

'Creates the command bar to hold macros
Sub CreateCommandBar()
    Dim cb As CommandBar
    Dim cc As CommandBarButton
    Dim NewButton As CommandBarButton


    Set cb = Application.CommandBars.Add(CommandBarName, msoBarTop, False, True)
    With cb
        
        Set NewButton = cb.Controls.Add(msoControlButton, , , , True)
        With NewButton
            .Caption = "Start New Analysis"
            .OnAction = "'" & ThisWorkbook.Name & "'!MACRONAME"
            .TooltipText = "DESCRIPTION OF WHAT MACRO DOES"
            .Style = msoButtonIconAndCaption
            .FaceId = 240
        End With




       Set cc = Nothing
        .Visible = True
        .Left = 0
        .Top = 100
    End With
    
    Set cb = Nothing
End Sub

It is possible if you do it this way:

Worksheets("MySheet").Activate
    Msgbox "A1:" & Range("A1") & "  A2:" & Range("A2") & "  A3:" & Range("A3") _
         & "  A4:" & Range("A4") & "  A5:" & Range("A5")

Easy stuff here

you can do the following:

mystring = Replace(mystring, "/", "-")

This will replace every "/" characters in your string with "-"

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.

Private PrevCell As String




Private Sub Worksheet_Activate()
    PrevCell = ""
End Sub




Private Sub Worksheet_Change(ByVal Target As Range)
    PrevCell = ""
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    
    If Target.Address = "$E$7" And PrevCell = "$E$6" Then
        Me.Range("H10").Select
        PrevCell = "$H$10"
    ElseIf Target.Address = "$H$11" And PrevCell = "$H$10" Then
        Me.Range("F18").Select
        PrevCell = "$F$18"
    Else
        PrevCell = Target.Address
    End If


End Sub

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
        Me.Range("H10").Select
    ElseIf Target.Address = "$H$11" Then
        Me.Range("F18").Select
    End If
    
End Sub

Simple enough.

What you should do is something like this:

Dim myString as String
Dim RowPtr as integer


RowPtr = 1
myString = InputFromInputBox


for each cell in Worksheets("MySheetName").Range("X6:X1698")


   if cell.value = myString then
        Worksheets("MySheetName").Range("A" & cell.Row).EntireRow.Copy
        Worksheets("MyOtherSheetName:).Range("A" & RowPtr).EntireRow.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       RowPtr = RowPtr + 1
   end if


next cell

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

type "MSGBOX" in your VBA editor, highlight it, then hit F1.

It will bring up all you need to know.

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
            cb.Delete
        Next cb
    On Error GoTo 0
End Function

To delete a specific button, you can do the following by indexing it
Application.CommandBars(CommandBarName).Delete

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

Dim myRegex as RegExp


Dim Count as integer


Set myRegex = new RegExp
myRegex.Global = true
myRegex.Pattern = "^WebOPRT"


for each cell in Worksheet("SheetName").Range("E1:E500")
    if myRegex.Test(cell.value) = true then
        Count = Count + 1
    end if
next cell

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.

If you are allowed to change the formatting, you could run a script that will automatically format it for you whenever you open the workbook.

Sub Auto_Open()
    Cells.select
    Selection.Numberformat = xlNumber
end sub

Format the cell as "Number" instead of General

If you select the cells with the problem, click on Format > Cells

In the "Number" tab, select NUMBER FORMAT

This should format all the numbers correctly for you.

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)

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

Edited.. didn't read your question correctly