Posts by Ranger

Re: Bind Macro To Key Combination

Hi
Tools|Macros|Macros|Options

Bill

EDIT: Sorry, misread the question, I thought it was binding to a key combination

Re: Ungroup Rows On Cell Click?

Hi financial host,

Using the Worksheet_SelectionChange is one way you can do it.

In the Code, I have used cell E1 as the cell to click. You will have to change this to suit. You will see that it also Activates cell B12, which is a cell within my data range for the Subtotals, again you may have to change this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$E$1" Then
        If Range("IV1").Value = 1 Then
            Range("IV1").Value = 0
            Range("E1").Value = "Click to Add Subtotals"
        Else
            Range("IV1").Value = 1
            Range("E1").Value = "Click to Remove Subtotals"
        End If
        Range("B12").Activate
        If Range("IV1").Value = 0 Then
            Selection.RemoveSubtotal
        Else
            Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(12), _
                Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End If
    End If
End Sub

When the user clicks to Add subtotals, the Text in E1 changes to Click to Remove Subtotals and vice versa.

Bill

Re: Copy Data From Different Rows Into One

Hi Caveman,

Try this:

Dim lRow As Long
Sub AddLevels()
    Dim lEndRow As Long
    lEndRow = Range("A65536").End(xlUp).Row
    lRow = 1
    For n = 1 To lEndRow
        
        Range("A" & lRow).End(xlDown).Activate
        If ActiveCell.Value = "" Then Exit Sub
        lRow = ActiveCell.Row
        Transfer
    Next n
End Sub
Sub Transfer()
        Range(Range("H" & lRow), Range("H" & lRow + 5)).Value = Range("A" & lRow).Value
        Range(Range("I" & lRow).Offset(1, 0), Range("I" & lRow + 5)).Value = Range("B" & lRow).Offset(1, 0).Value
        Range(Range("J" & lRow).Offset(2, 0), Range("J" & lRow + 5)).Value = Range("C" & lRow).Offset(2, 0).Value
        Range(Range("K" & lRow).Offset(3, 0), Range("K" & lRow + 5)).Value = Range("D" & lRow).Offset(3, 0).Value
        Range(Range("L" & lRow).Offset(4, 0), Range("L" & lRow + 5)).Value = Range("E" & lRow).Offset(4, 0).Value
        Range(Range("M" & lRow).Offset(5, 0), Range("M" & lRow + 5)).Value = Range("F" & lRow).Offset(5, 0).Value
    
End Sub

Bill

Re: For Each [element] In [collection]

All that is missing in your own code is:

ActiveSheet.Range("B2").Value = 1

ActiveSheet before the Range statement.

Bill

Re: Indirect Function Inside Sumproduct Formula

Hi marc,

File attached. All that is missing from your formula is ":" between the row and columns.

Bill

Re: Excluding Hidden Cells In Your Data Range

Hi heatz23,

The subtotal function will do it. Subtotal(9,Your range)

Bill

Re: Looking For A Cell In A Group Of Cells

Hi joshlan,

Sub FindIt()
    Dim sAddress As String
    sAddress = Sheet3.Range("E1").Text
    
    For i = 1 To 3
        With Sheet3.Range("Range" & i)
            For Each c In Sheet3.Range("Range" & i)
                If c.Address = sAddress Then MsgBox "Range " & i
            Next c
        End With
    Next i
End Sub

Should do that for you.

Bill

Re: Change Font Color Based On Conditions

Hi romy113,

The following in the Worksheet_Change Event, not SelectionChange event will work.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G2:G2000")) Is Nothing Then
        Exit Sub
    Else
        If Target.Value >= Target.Offset(0, 2).Value Then _
        Target.Font.ColorIndex = 3
        If Target.Value >= Target.Offset(0, 2).Value * 0.8 And _
        Target.Value <= Target.Offset(0, 2).Value Then _
        Range("G" & Target.Row).Font.ColorIndex = 45
    End If
End Sub

Bill

Re: Find And Replace With Loop

Hi Shab,

The dates that I have shown are European style, not US style so 1 is 1st Jan 1900 and 2 is 2nd Jan 1900 etc.

Bill

Re: Find And Replace With Loop

Hi Shab,

2 would relate to 02/01/1900

type in a date in any cell and then format the cell as General and you will see the actual value of the date cell.

Bill

Re: Find And Replace With Loop

Hi Shab620,

01/01/1900 has a value of 1 in Excel. The cells which have 01/01/1900 actually have a value of 1 in them (a date is just a value), not text "01/01/1900", so the code recognises the value of the cell as 1. Therefore changing the code to look for 1 rather than 01/01/1900 deletes the value and replaces it with a blank cell.

HTH

Bill

Re: Find And Replace With Loop

Hi Shab620,

The following code works for it:

Sub jdate()
    Dim lRnge As Long
    target_sheet = "Status Report"
    lRnge = Sheets("Status Report").Range("E65536").End(xlUp).Row
    For i = 8 To lRnge
        If Sheets(target_sheet).Cells(i, 5).Value = 1 Then
            Sheets(target_sheet).Cells(i, 5).Value = ""
        End If
       
    Next i
End Sub

Still looking for a value of 1.

Bill

Re: Find And Replace With Loop

Hi Shab620,

It is looking for a value in the cell not 01/01/1900, the cell value is 1, so change the code to;

If Sheets(target_sheet).Cells(rowcn, 5).Value = 1 Then

and it will work. The only problem is if you have any other cells in that column valued 1.

Bill

Re: Skip Blank When Paste

Hi Tee,

First problem

If you want to do it without code, put 3 in any blank cell, select Copy to copy the cell with 3 in it, then Select the Range with the values in it, Select Paste Special|Multiply and OK

Bill

Re: Skip Blank When Paste

Hi Tee,

For your third problem, you could use the WorkSheet before right click to do it.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Range("A1").Value = WorksheetFunction.Sum(Selection)
    Cancel = True
End Sub

Select the range, then Right Click anywhere in the selected range and the Sum will appear in Cell A1

Bill

Re: Vba: Changing Properties Macro On Control Toolbox Item

Hi wezred,

Are the Option Buttons from the Control Toolbox or the Forms Toolbar?

Bill

EDIT: Sorry, disregard the above, I see you mention in your post that it is Control Toolbox.

Re: Conditional Formatting With Formulas

Hi Canadian diva,

If you put a date in cell K2, then it should not be highlighted, no matter what date you put in it.

=AND($K2="",$J2+2<TODAY())

Says that the cell K2 must be blank as well as the date in J2 being more than 2 days before today() before it is highlighted.

Can you explain more about what is happening?

Bill

Re: Sumif If The Row Data Is Live

Hi richiejjj,

I may be reading this all wrong, but if you put:

=IF(E$16>0,E6,"") in cell E18 and =IF(E$16>0,E7,"") in cell E19, then copy along, does that not do it?

Bill

Re: Conditional Formatting With Formulas

Hi Canadian diva,

=AND($K2="",$J2+2<TODAY())

Means that cell K2 has to be blank AND the date in cell J2 + 2 days is less that today's date. TODAY() is basically just today's date.

It could just as easily have been:

=AND($K2="",$J2<TODAY()-2)

Where the date in J2 has to be less than today's date minus 2 days

Today() now would be 24/1/07, TODAY()-2 would be 22/1/07
if cell J2 date is less than 22/1/07 and K2 is blank then the conditional formatting would come in.

HTH.

Bill

Bill

Re: Sumproduct With Criteria

Hi bluebells,

I have added 8,500 rows of data in a sheet and because it is just one formula, I get an instantaneous result.

Bill