Posts by MrkFrrl

    Re: For Each [element] In [collection]

    Yep, you and Carl were right. Looks like I've overlooked that all this time. Thanks,

    Quote from norie

    That code isn't right.

    You have an unqualified range reference, so VBA will always look at the active sheet rather than sht.

    The code Carl has posted should fix the problem.

    This has always been one thing that has bothered me--the way my Excel handles the For Each loop. I have to ask if my version of Excel is somehow defective or deficient???

    From what I've read elsewhere, I can use the For Each method to loop through a collection (in this particular case, a worksheet's sheets), having the same conditions applied to each. Now, if I apply For Each to cells within a range, it will work fine; there are no problems with that.

    HOWEVER, when I try to loop through a workbook, it will only apply the conditions to the ActiveSheet, though it will maintain the total number of sheets within the loop. I can adjust the macro to accomodate for this, but I always "thought" that it's supposed to do this for you--that is, apply the same things to each sheet automatically.

    Below is an example of a loop I'd like to have put the number 1 on each sheet. If I remove the text statements, it will do it just fine. But, as-is, with the For Each loop, it will not.

    Am I missing something? Could someone else try this code as-is (without removing the text-statements) and see if it puts the number 1 in each cell B2? Is it just my particular Excel?

    Re: find first empty cell in range and copy data

    This ought to at least get you started. There are more variables here than I originally thought, and I got tired of doing it all. But at least you've got something to work on.


    Re: Data Validations And Filtercopy Macro

    You're just asking if the first and third conditions (IF-statements) could be used without using the second? I don't see why not.

    However, I'm not too sure here what condition you'd like applicable in this case, as you didn't specify any.

    Here's a general example with the code given:

    So if cell A1 has the contents of the cell as anything other than "DoNotSkip" (without quotes), the code will ignore the second IF-statement.

    Re: Refine Proper Case Macro

    Two ways:

    1.) You could make the left-character Upper and the right characters Lower. You might also have to use something like WorksheetFunction.Find or InStr to determine certain characters, depending on what you've got in your cell references.

    2.) Or do something like this for just proper case:

    Sub ProperCase()
    Dim s$
    s = Range("a2").Value
    s = WorksheetFunction.Proper(s)
    MsgBox s
    End Sub

    So whatever you put in cell A2 has the first letter capitalized in the MessageBox.

    Re: Improve Color Index Conditional Formatting Macro

    Quote from JRSmith729

    When I tried using the With/End With expression like this, nothing happened to the text, but the macro ran successfully and colored the fill as expected...

    What am I missing to get both the FILL color, AND the FONT color of the first 2 characters of the text to MATCH? Do I need to place the With/End With expression WITHIN each individual case argument in the original code sample at the beginning of this post?.

    The way I showed you should work. I don't know why it wouldn't for you. Maybe it has something to do with the way you set up your code.

    Quote from JRSmith729

    Also, I am getting a compile error (End If without block If) when I run my version of the recommended code from MrkFrrl for changing the font color based on left column similar values. Here's what I tried:

    Sub HideRepetitiveText()
        Dim i As Integer
            For i = 1 To 200
            If ActiveCell.Value = "" Then Exit Sub
            If ActiveCell.Value = ActiveCell.Offset(0, -1).Range("A1").Value Then ActiveCell.Font.ColorIndex = ActiveCell.Interior.ColorIndex
            End If
        End If
        ActiveCell.Offset(1, 0).Range("A1").Select
    Next i
    End Sub

    That's because you altered what I showed you. You're running the If-statement straght into the Then statement, and also then showing an End If at the end. As an example, you can have an If-Then statement as one of these two ways, but not both:

    If ActiveCell.Value = 7 then ActiveCell.Value = 8
    ' or like this
    If ActiveCell.Value = 7 then
    ActiveCell.Value = 8
    End If

    In your example, you're trying to do both, which is causing your error.

    Quote from JRSmith729

    I don't follow why the "ActiveCell.Offset(#,#).Range(name).XXXXX" are referencing "A1." Is that just a place holder within the code to name the cell to the left "A1" is the code really looking at cell A1? Does that reference need to be changed to suit my situation (my target area begins at column F, so intuitively the first "offset(0,-1)" should be column E)?

    I hope I'm making sense.

    You don't need to change the A1 reference value in the activecell.offset method; it's essentially a place-holder.

    Re: Improve Color Index Conditional Formatting Macro

    OK. Now I've looked at your workbook. You'd have the macro, say, select the one range, go down as many spaces as needed, then repeat itself.

    There's probably a simpler way to do this, but I don't want to get too wrapped up in your situation, which won't do you much good since you won't learn anything from it then.

    Re: Improve Color Index Conditional Formatting Macro

    Quote from JRSmith729

    I actually caught your previous code without the "-4142" line. Should I discard the old sample? I noticed the change, but couldn't tell you what it means.

    Yes, discard the old sample. I changed the code because I hadn't thought of "what if" there is no formatting applied and the two values are the same. The -4142 refers to no-formatting to the cell; the value for the font here is 2 (white). So if it comes across two values that are the same and there is no formatting (color) applied to the cell, the font-color thus becomes white and "invisible". BTW, another method to make the text "invisible" would be to go to the cell's formatting and change it to ;;; . This would make it that way no matter what the cell's color.

    Selection.NumberFormat = ";;;"

    And to make it viewable again:

    Selection.NumberFormat = "General"

    Re: Improve Color Index Conditional Formatting Macro

    Quote from JRSmith729

    I have one question about the second code sample you provided. In your comments you warn against large sets of cells and blanks. I have about 7500 cells in the range "Calendar" (52 weeks x about 150 rows). These cells are populated with a VLOOKUP returning "" when no match is found, but every cell in the "Calendar" range has either a formula result of "" or a hard-coded text/numeric value. With this information:

    1) Will this approach for populating my spreadsheet create any problems for your code, or should I be alright?

    If the formula returns "", the loop may end early, as it's checking the activecell.value and telling it to exit the sub. You can either delete the line with Exit Sub; or you can leave it intact and then have your formula return "0". If the latter, from the menu, you'd go to Tools -> Options and choose to hide all 0's.

    Quote from JRSmith729

    2) I'm always a bit intimidated when I see code with the Integer (i = # to ##) type of logic because I'm only really comfortable modifying existing code samples. I'm not yet fluent enough to write code "from scratch" on my own and this type of logic is above my level of comprehension right now. Will I need to input a Range("B2").Select argument for ALL 7500 cells in the target area, or just 52 for the full width of one row (allowing the code to somehow pass from there onto the next row)? Can I just enter Range("Calendar").Select and capture all cells in my (dynamic) Named Range?

    The Range("B2").Select refers to the very first cell that you choose to compare. You should not need to change this cell value more than once. It will be the first cell in your column that you want the conditional changes applied. The macro then goes down to the next row and compares those two values, then repeats itself.

    I haven't looked at your worksheet, so I don't know what goes where.

    Quote from JRSmith729

    3) In general, what does the whole "Integer (i - # to ##)" expression actually DO? Why repeat the code 100 times? Why not 10 or 68 times?

    The For i = 1 to 75 (or whatever the last integer was) is just telling the loop to repeat itself this many times. You say your target has 7500 cells in any given column that you want these changes applied to. So, you might say For i = 1 to 10000 (leaving whatever room for any extra cells added at a later date to compensate for this).

    Re: Improve Color Index Conditional Formatting Macro

    Quote from JRSmith729

    1) Can anyone help modify this code to ALSO highlight the first 4 characters (LEFT(c.value,4)) of the entry to match the fill-color (thereby rendering that first part of the text "invisible", as it will just blend into the color fill behind it)?

    With ActiveCell.Characters(Start:=1, Length:=4).Font
            .ColorIndex = iColor ' same as other listed in Select Case
        End With

    Quote from JRSmith729

    2) Can anyone help add code that would recognize repeated data and change the font color so that the cell contents become "invisible" in cases where they match exactly to the cell on the left?

    Re: Write Flexible Formula

    Sub VariableFormula()
    Dim a As Long, b As Long, c As Long, d As Long
    ' syntax for cells is row, column
    a = 2
    c = 4
    b = 3
    d = 3
    ' same as C2 + C4
        ActiveCell.FormulaR1C1 = Cells(a, b) + Cells(c, d)
    End Sub

    Re: Transfering Data Between Worksheets

    I think the function [vl]*[/vl] is what you want to use. Hit Shift-F3 simultaneously in the cell to bring up the Insert Function box. Put in Vlookup in the Search box, then, after you've found it in the list, click on the link "help on this function." If that's not what you want, write back.

    Quote from BrianNC

    I am a novice so please excuse this simple question.

    I have two seperate worksheets that will carry the same data (Member Name and Member ID). The first worksheet will have both of these columns filled. But on the second worksheet, when the user types in the members name, i would like their corresponding ID from the original worksheet to automatically appear in the colmn next to the name. Is this possible?


    Re: Write Flexible Formula

    Quote from tgit

    Can I write the 'C2 and C4' in the above formula dynamically?

    OK. Say that I want the Sum formula to appear in cell C1. I'm looking at 1-row directly below it for the first number (cell C2), and 3-rows below it for the second number (cell C4). But the column is the same, so that doesn't need to change. Therefore, I'd write:

    ActiveCell.FormulaR1C1 = "=R[1]C+R[3]C"

    If I were looking at the rows above to be added, I'd use a negative-number.

    Now, if I shift the ActiveCell to cell D1 and run the code, it will then give me the value of D2+D4. Is that what you're asking?

    Re: Capture Sheet Delete Event

    Sub DeleteSheet()
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    End Sub

    Quote from Richnl

    I wonder how to capture de event when one choose to delete a sheet

    Re: Write Flexible Formula

    Quote from tgit

    Can I write [a] formula in the sum field based on Lrow and Lcolumn variables[?]

    Yes, you can; and you'll probably do a fine job at it too. : D

    I really don't have a clue what you want to know.

    Re: Editing External Workbook With Code?

    What is this in your code? Where has "w" been defined? Doesn't look right to me as shown.

    For Each w In Workbooks 
                If w.Name <> ThisWorkbook.Name Then 
                    w.Close savechanges:=True 
                End If 
            Next w

    I guess "w" is a workbook?

    As to directly doing something, I think this might be what you're talking about:

    Workbooks("WorkBookName.xls").Sheets("SheetName").Range("CellName").Value = "Value to be put in cell"
    ' using a variable for the Window, as previousl mentioned, it would be -
    Workbooks(MyWrkBk).Sheets("SheetName").Range("CellName").Value = "Value to be put in cell"

    I don't believe that the above will work with .Select directly, though you could use the Workbooks().Sheets().Select then Range().Select.
    It might work with .Copy .

    Quote from sleepeeg3

    Is there a way I can define the ActiveWorkbook at the same as defining the range?
    Something like "Workbooks("H:\Documents\Troubleshooting Docs\Macros\misc_practice.xls").Worksheets("Region").Range("..."

    Re: Workbook Password Protect 2 Levels

    Would something like this work in the workbook module?

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        ActiveWorkbook.WritePassword = "test"
        ActiveWorkbook.ReadOnlyRecommended = True
    End Sub

    Re: Color Cells Based On Owner &amp; Time (gannt Chart)g

    This should get you started. It will do the JF and FT's.

    Just copy the data for the FT's down for each successive color.

    Use your macro recorder to determine the color-number needed.