Posts by Rennie

    Re: Find First Non-empty Cell And Paste A Value Into Preceding Cells


    Assuming your data is in Range A1:A5 the following macro will replace the blank cells with "cc"s


    aa bb
    aa bb
    aa bb
    aa bb cc
    aa bb cc



    Hope this helps.


    Thanks,
    Rennie

    When the freeform tool is used to draw a shape the "Name Box" in Excel displays a default name. e.g. Freeform 6.


    Is it possible to change the default name given in the "Name Box". i.e to change "Freeform 6" to "FunnyShape"?


    I have tried usign insert>name>define this seems to work but I am unable to access it using a macro.


    Appreciate any Help.

    I seem to have lost the dropdown list when using data validation.


    Everything else on the validation seems to work. i.e. it is correctly
    not allowing amounts that are not correct.


    I have checked if the correct boxes are ticked on the Data Validation>Settings sheet> both (Ignore Blank and In-Cell Dropdown) and they are ticked.


    Is there anything else I can try.


    Appreciate any help.


    Thanks,
    Rennie

    Is it possible at all to modify a collection of sheets by using the sheet name.


    I have a workbook that has about 20 sheets. There are 5 Kinds of sheets in each workbook. ie. 5 Assumption Sheets ("(1) Assump","(2) Assump","(3) Assump"...etc). Each kind is identical to each other.


    I would like to know if I can use a wild card to select the group of sheets and edit them. I.e "*Assump" in a macro.


    Thanks,
    Rennie

    Hi Weasel,


    I have worked it out (After 2 Hours!). Below is the code I came up with. Thank you verymuch for your help.


    Best regards,
    Rennie


    '
    Sub FormulaCells()
    '
    ' FormulaCells Macro
    ' Macro recorded 24/10/2003 by rthangiah
    '


    '
    Do Until ActiveCell.Text = "X"
    If Range(ActiveCell.Value).HasFormula = True Then
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Yes"
    Else
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "No"
    End If
    ActiveCell.Offset(1, -2).Range("A1").Select
    Loop
    End Sub

    Sorry Weasel,


    Let me explain. I inserted a new sheet in the work book and did insesrt>name>paste>paste list. This is how I got the list of 200 names.


    Thank you for the code I'll test it out.


    Rennie

    Thank you again Weasel.


    Can I have this listed in a column alongside my pasted list?


    Eg.


    Name Range Formula?
    ACL ='Call Statistics'!$B$10
    ACW ='Call Statistics'!$C$10


    Thanks,
    Rennie

    Thank you very much Weasel.


    I have pated a list of all the names (about 200) in a new sheet. What I want to do is to run a macro that will state if the name listed has a formula.


    Name Range Formula?
    1 It's a formula
    2 Not a formula


    Etc.


    I have attempted to modify your code to do this without any luck. Can you please help.


    Your kindness is much appreciated.


    Thanks,
    Rennie

    Assuming you have information in A3,B3,C3,D3 the following formula will work.


    This is only a bad alternative to the other excellent replies you have got! :).


    Hope this helps,


    Thanks,
    Rennie


    =IF(CELL("Type",A3)="b","",A3&"/")&IF(CELL("Type",B3)="b","",B3&"/")&IF(CELL("Type",C3)="b","",C3&"/")&IF(CELL("Type",D3)="b","",D3)

    Hi everyone,


    I am trying to test a list of named cells to determine if they contain formulas or text values.


    Can anyone help me write a macro to do this.


    My attempt at this returned a type mismatch error.


    Sub TestCell()
    If ActiveCell.Type.xlCellTypeFormulas Then
    ActiveCell.FormulaR1C1 = "Formula"
    Else
    ActiveCell.FormulaR1C1 = "Not a Formula"
    End If
    End Sub



    Thanks,
    Rennie

    Thank you again Neale. You were right the range should have read "DateRange".


    The following code workes.


    Thank you again for you help.



    Sub ColourCells()
    Sheets("Time").Select
    For Each rMyCell In Range("DateRange")

    If rMyCell.Value = 1 Then
    rMyCell.Interior.ColorIndex = 6
    ElseIf rMyCell.Value = 2 Then
    rMyCell.Interior.ColorIndex = 7
    ElseIf rMyCell.Value = 3 Then
    rMyCell.Interior.ColorIndex = 8
    ElseIf rMyCell.Value = 4 Then
    rMyCell.Interior.ColorIndex = 9
    ElseIf rMyCell.Value = 5 Then
    rMyCell.Interior.ColorIndex = 10
    ElseIf rMyCell.Value = 6 Then
    rMyCell.Interior.ColorIndex = 11
    Else
    rMyCell.Interior.ColorIndex = xlNone
    End If

    Next rMyCell


    End Sub

    Thank you for your reply Neale. I have chenged the macro to the following. I still get the same error message.




    Dim rMyCell


    Sub ColourCells()
    For Each rMyCell In Range("DataRange")

    If rMyCell.Value = "1" Then
    Interior.ColorIndex = 6
    ElseIf rMyCell.Value = "2" Then
    Interior.ColorIndex = 7
    ElseIf rMyCell.Value = "3" Then
    Interior.ColorIndex = 8
    ElseIf rMyCell.Value = "4" Then
    Interior.ColorIndex = 9
    ElseIf rMyCell.Value = "5" Then
    Interior.ColorIndex = 10
    ElseIf rMyCell.Value = "6" Then
    Interior.ColorIndex = 11
    Else
    Interior.ColorIndex = xlNone
    End If

    Next rMyCell


    End Sub

    Hi All,


    I am trying to use the following macro to format cells depending on cell value. When I run it I get the error message "Method'Range' of object' Global'Failed.


    Can anyone help.


    Thanks,
    Rennie



    Sub ColourCells()


    Dim rMyCell As Range
    For Each rMyCell In Range("DataRange")

    If rMyCell.Value = "1" Then
    Selection.Interior.ColorIndex = 6
    ElseIf rMyCell.Value = "2" Then
    Selection.Interior.ColorIndex = 7
    ElseIf rMyCell.Value = "3" Then
    Selection.Interior.ColorIndex = 8
    ElseIf rMyCell.Value = "4" Then
    Selection.Interior.ColorIndex = 9
    ElseIf rMyCell.Value = "5" Then
    Selection.Interior.ColorIndex = 10
    ElseIf rMyCell.Value = "6" Then
    Selection.Interior.ColorIndex = 11
    Else
    Selection.Interior.ColorIndex = xlNone
    End If

    Next rMyCell


    End Sub

    Hi Brandtrock,


    Thank you very much for your response. Your ideas have certainly pointed me towards the right track.


    With your help I have managed to achieve what I wanted.


    Please see the attached file.


    Thanks again.
    Rennie

    Hi,


    Sorry for not posting this any urlier. The thought process behind the solution was to make the text string in the cell a numerical value.


    I did this by eliminating the "," and the "." usign the replace function.


    The excel help has comprehensive descriptions of what each fuction does with examples. Please have a look at these.


    Hope this helps.


    Rennie