Posts by Joebbshop

    Re: Countif based on matching criteria

    Cool. After I re-read the posts and looked at the data, I figured out the desired results. Code I included previoulsy produces a very similar result to yours, but indicates when there is a duplicate match or when there is no match.

    Re: Countif based on matching criteria

    Here's a snippit of the data in the workbook you just posted.

    I think I must be slow, but I still don't understand what you're trying to do. In the example you just gave, you said that for 2217, the answer should be 414. However, the cell in column A that holds 2217 doesn't match the cell one column over in column B... shouldn't the macro return null?

    Given the data below, can you write out in words what you're trying to do for a few rows till I get the idea of what you want?

    Maybe this is what you want... for each cell in column A, find the cell in column B that matches the code in the activecell in Column A. Then return the code from Column C corresponding with the found cell in column B and stick it in the Activecell's Row.

    Re: Conditional Formatting > 3

    symnow, welcome to OzGrid!

    I'm not sure what you're trying to do... I understand the part about wanting to color a cell based on what's in the cell, but I don't understand the logic. Plus, I get some errors.

    Here's a written version of what the computer is thinking...

    1. something changed, I'm going to fire this event sub (Worksheet_Change)

    2. if Target (the cell that changed) is inside the range C5 to M44, I'm going to go inside the If statement. Otherwise, I'm going to go to the End If statement which is at the end of the sub.

    3. At the Select Case, I'll do something based on Target (the cell that changed). (Note, I don't know if it's proper or necessary, but I think it's easier to understand if you changed it to Select Case Target.Value. This makes it explict that you are testing the value of the cell that changed. otherwise, what property of Target are you deciding on? As a range, Target has lots of properties. Color? Font Type? Excel has to guess.)

    3.1 Is the value "X", if so icolor is 10.

    3.2 Is the value "N/A", if so icolor is 2 (note it's checking that the value is N/A... not that there's an N/A error in the cell.)

    3.3 Third case is where you start getting funky and where I can't figure out what you're trying to do.
    - Today() is a worksheet function and isn't used in VBA. The equivalent is Now or Date.
    - You were testing Target, but now you start testing the value of cr... not legal inside a Select Case.
    - cr only holds a value (as a Date type, all it can hold is a date value), so Excel doesn't understand why you have the ".Value".
    - just a few lines earlier you set the value of cr to equal today... so if cr = Today, the test if cr >= Today will ALWAYS be true.

    3.4 Same as 3.3. Even if the code didn't error out by now, it would never get to this line because 3.3 would grab all the remaining possibliities.

    I'm guessing this is what you wanted the thing to do.

    Re: Macros -if statement deletion or ignore

    Welcome to the forum! This place is great!

    So, let's walk through the code... forgive me if this is too basic. But, I can't tell from your question what you're looking for and how much you understand.

    If Worksheets(sSheet_IS).Cells(1, iQtyCol).Value <> "" Then

    This line is looking at a cell in row 1, column iQtyCol, in the sheet referred to by sSheet_IS. If it's not blank, it continues on inside the If statement. If it is blank then it skips all the way past the End If... probably continuing with the import process.

    If MsgBox("Do You Wish To Continue Import?", vbOKCancel, "Data For This Date And Shift Has Been Previously Imported") = vbCancel Then 
            Exit Sub 
        End If

    At this point, we know that the cell it's checking is not blank... there must be data there already. So, the script checks to see if you want to continue. If you hit the cancel button, indicating you don't want to continue the import, then it activates the SALES sheet and quits. If you didn't hit cancel, thus indicating you want to re-import, then it goes down past this End If and starts clearing out the old data.

        Columns(sQtyCol & ":" & sQtyCol).ClearContents 
        Columns(sNetCol & ":" & sNetCol).ClearContents 
        Columns(sGrossCol & ":" & sGrossCol).ClearContents

    At this point, we know there is existing data and we know the user wants to overwrite it with a new import. In the three chunks like the piece copied above, entire columns are cleared of their contents but not formats.

    The whole pupose of this piece of code appears to be to make space for the data you are about to import. If you remove it, the script will import the new data, overwriting any data that's there already. If the data there already is longer than the new data you're importing, the old data will dangle below the new data and cause problems. (Example: old import had 1000 rows, new import has 950... without this code to clear out the old data, 50 rows of old data will hang out at the bottom.) For this reason, I'd leave this chunk of code in your script.

    Hope this helps.

    Re: Countif based on matching criteria

    I don't really understand what you're trying to do, but I think the problem is in the line that reads:


    ce.Offset(0, 13).Value = ce.Offset(0, 2)

    offset moves to the right or down as many cells as you specify. So, in this case ce refers to a cell in column A. If the test is true, then the value in column N (offset 13 columns to the right of column A) is set equal to the value in column C (offset 2 columns to the right of column A). This is different than Cells... the following line would refer to the cell in column B in the same row as the match was found:


    So, I think you're problem will be solved by changing the 2 to a 1 like so:

    ce.Offset(0, 13).Value = ce.Offset(0, 1)

    Re: Cell find and row color

    Try this.

    Sub ColorSpecialRow()
        Dim curRange As Range
        Dim SearchString As String
        Set curRange = Columns("B")
        SearchString = "Insert all new lines above this line by insert button"
        curRange.Find(SearchString).EntireRow.Font.ColorIndex = 35
    End Sub

    Re: Invisible text even when printing, VBA

    To be honest, I'm fairly new at VBA also and don't understand what you're code is doing. Is there text in the cells that says "one" "two" "three"...?

    Perhaps you can describe the spreadsheet and what you want to happen, or upload a portion of the spreadsheet so I have a better idea.

    Part of my confusion is that if it get to the "Case Else" you never say what the fcolor should be.

    But, to use the numberformat, instead of the two lines just before the next, you'd enter

    Target.NumberFormat = ";;;"

    Re: Macro works on notebook but not on desktop

    Not sure if this is it, but the Integer data type only works to a max positive value of 32,767. If you potentially have 65k rows, you'll need to pick a different data type, like Long.

    If this isn't it, which line is it giving you an error on? Step through the code using F8.

    Re: Click top cell in a column to sort.

    Hmmm... Yes, it's right to change the HeaderRow. Don't know what the problem could be.

    Could you attach your worksheet?

    So, your column header is actually in row 2, right? That's not where the data starts is it?

    Re: Click top cell in a column to sort.

    I'm no guru, but how does this work for you?

    Right-click on the sheet's tab and select "view code," then paste the following. To use, double-click on the header cell you want to sort by.

    Re: Attached Sample - Calendar

    I gave cell H1 on the "Events" sheet the name "CurYear." To confirm this address or to change which cell is "CurYear," on the menu bar go to Insert, Name, Define. Then click on CurYear and it will show which cell it refers to. To change this, simply type in a new address you want to use as the CurYear. You can do this with the "StdHeight" named cell as well. excel_for_fun, did that help?

    yesnomaybe, thanks for the "ATP" reminder. I guess I just leave those add-ins on all the time cause I use them frequently.

    Let me know if you have any more suggestions. I toyed with the idea of having "to-do" items in addition to the "events." But, that seemed like it was taking a simple little form and making it too complicated.

    Re: Attached Sample - Calendar

    In the attached revision to the calendar I posted before, I added a check to skip over the dates that are not in the current year. Those dates that are skipped over are colored gray.

    Look for this code...

    If Year(CurDate) <> Range("CurYear").Value Then
                Cells(curRow, 1).Interior.ColorIndex = 15
                GoTo NextRowTag
                'carry on... this is a current year event
            End If

    I also added a "standard minimum rowheight" routine to get things a little more regular.

    Also, as a side note, if you want to show holidays that happen on the same date each year, you can use the DATE function. For example, I added my birthday into the table.

    Hope this help.

    Re: Sheet uneditable and undeletable

    Problem was that the "PriorSheet" variable wasn't set initially since it was only firing when you activated a sheet. Try this...

    Add a module and paste this code:

    In the "ThisWorkbook" object, add this code below. This will initialize the "PriorSheet" variable as soon as you open the workbook.

    Private Sub Workbook_Open()
        Call SetPriorSheet
    End Sub

    For each sheet you want this protection for, add this code:

    Private Sub Worksheet_Activate()
        Call ProtectSheet
    End Sub
    Private Sub Worksheet_Deactivate()
        Call UnprotectSheet
    End Sub

    You may still have problems if you select multiple sheets... I haven't fully tested, obviously. Hope this gets you closer though.

    Of course, if you just wanted to make the sheet uneditable and undeletable and stay that way, you could just do it manually. To make the sheet uneditable, go to Tools, Protection, Protect Sheet. To make it undeletable, go to Tools, Protection, Protect Workbook and select "Structure". That second step would keep all the sheets from being deleted. The code above would just protect the sheets you care about from being deleted.

    Re: Don't hide row with at least one cell with color

    Only hides if your COUNTA cell reports 0 and if there are no colored cells in the cells to the right.

    Note that I changed the variable definations... i refers to a column, j refers to a row.

    Experts: how do you do this using a range rather than using the Cells(j,i) method?