Posts by Alterego



    THank You,


    This did help my Cause. I attached the spreadsheet in case anyone wanted to look at it.

    I have asked this before, but I added it to an old topic so it's probably not getting looked at. I have code to read several timesheets in a certain dir and compile another spreadsheet from the data in the spreadsheets. The problem I have is when the macro looks up the cell range, and there is no data to report, it puts 0 values in the cells. Is there a way to just skip the process if there is no data to report? In otherwards, leaving the 0's out of the spreadsheet. I'll past some snipits here.


    Part 1:


    For x = A To Z
    Hours_REG = Application.WorksheetFunction.Sum(sht.Range("D" + Format(x)), _
    sht.Range("H" + Format(x)), _
    sht.Range("L" + Format(x)), _
    sht.Range("P" + Format(x)), _
    sht.Range("T" + Format(x)), _
    sht.Range("X" + Format(x)), _
    sht.Range("AB" + Format(x)))

    OTHOURS_X1 = Application.WorksheetFunction.Sum(sht.Range("E" + Format(x)), _
    sht.Range("I" + Format(x)), _
    sht.Range("M" + Format(x)), _
    sht.Range("Q" + Format(x)), _
    sht.Range("U" + Format(x)), _
    sht.Range("Y" + Format(x)), _
    sht.Range("AC" + Format(x)))

    OTHOURS_X2 = Application.WorksheetFunction.Sum(sht.Range("F" + Format(x)), _
    sht.Range("J" + Format(x)), _
    sht.Range("N" + Format(x)), _
    sht.Range("R" + Format(x)), _
    sht.Range("V" + Format(x)), _
    sht.Range("Z" + Format(x)), _
    sht.Range("AD" + Format(x)))

    OTHOURS_X3 = Application.WorksheetFunction.Sum(sht.Range("G" + Format(x)), _
    sht.Range("K" + Format(x)), _
    sht.Range("O" + Format(x)), _
    sht.Range("S" + Format(x)), _
    sht.Range("W" + Format(x)), _
    sht.Range("AA" + Format(x)), _
    sht.Range("AE" + Format(x)))


    Part 2:


    If Hours > 0 Then
    row = ListPointer
    Sheet3.Cells(row, 1) = eName
    Sheet3.Cells(row, 2) = eNumber
    Sheet3.Cells(row, 3) = eCentre
    Sheet3.Cells(row, 4) = Week
    Sheet3.Cells(row, 5) = Activity
    Sheet3.Cells(row, 6) = Hours_REG
    Sheet3.Cells(row, 7) = FileName
    Sheet3.Cells(row, 8) = OTHOURS_X1
    Sheet3.Cells(row, 9) = OTHOURS_X2
    Sheet3.Cells(row, 10) = OTHOURS_X3
    ListPointer = ListPointer + 1
    End If
    Next x
    End If


    Is there a way to edit this code to not show 0 values?


    BTW, how do I attach an excel file to this post?

    I was just thinking about why the 0's are there in the 1st place. Maybe we can prevent the fire before it happens. Here's the code that refers to that area of the spreadsheet. Maybe there's a way to stop the 0's from happeneing.


    Here is the 1st Part:


    Hours_REG = Application.WorksheetFunction.Sum(sht.Range("D" + Format(x)), _
    sht.Range("H" + Format(x)), _
    sht.Range("L" + Format(x)), _
    sht.Range("P" + Format(x)), _
    sht.Range("T" + Format(x)), _
    sht.Range("X" + Format(x)), _
    sht.Range("AB" + Format(x)))

    OTHOURS_X1 = Application.WorksheetFunction.Sum(sht.Range("E" + Format(x)), _
    sht.Range("I" + Format(x)), _
    sht.Range("M" + Format(x)), _
    sht.Range("Q" + Format(x)), _
    sht.Range("U" + Format(x)), _
    sht.Range("Y" + Format(x)), _
    sht.Range("AC" + Format(x)))

    OTHOURS_X2 = Application.WorksheetFunction.Sum(sht.Range("F" + Format(x)), _
    sht.Range("J" + Format(x)), _
    sht.Range("N" + Format(x)), _
    sht.Range("R" + Format(x)), _
    sht.Range("V" + Format(x)), _
    sht.Range("Z" + Format(x)), _
    sht.Range("AD" + Format(x)))

    OTHOURS_X3 = Application.WorksheetFunction.Sum(sht.Range("G" + Format(x)), _
    sht.Range("K" + Format(x)), _
    sht.Range("O" + Format(x)), _
    sht.Range("S" + Format(x)), _
    sht.Range("W" + Format(x)), _
    sht.Range("AA" + Format(x)), _
    sht.Range("AE" + Format(x)))


    Here is the 2nd part:


    If Hours > 0 Then
    row = ListPointer
    Sheet3.Cells(row, 1) = eName
    Sheet3.Cells(row, 2) = eNumber
    Sheet3.Cells(row, 3) = eCentre
    Sheet3.Cells(row, 4) = Week
    Sheet3.Cells(row, 5) = Activity
    Sheet3.Cells(row, 6) = Hours_REG
    Sheet3.Cells(row, 7) = FileName
    Sheet3.Cells(row, 8) = OTHOURS_X1
    Sheet3.Cells(row, 9) = OTHOURS_X2
    Sheet3.Cells(row, 10) = OTHOURS_X3
    ListPointer = ListPointer + 1
    End If


    Maybe the problem can be solved within this code. I appreciate the help. You guys have helped me along with my projects, and beleive me, I appreciate it.









    :cheers:

    Hi all,


    I have written a macro that will go to a directory and read all of the spreadsheets in that directory. The macro will then generate a report from the data taken from about 400 spreadsheets in the directory.


    Now for the question:
    I want to clear the contents of all cells with a "0" value. I don't know how long the spreadsheet will be so I can't put a specific cell range. I was wondering the correct syntax for infinite cell range between 3 colums. Would it be IE:
    If cells.range("H2:J*") = 0 then
    cells.clear
    end if


    Does this seem right? Any help would be great.


    /Dave

    Hi,


    I have programmed one of my spreadsheets to Autosend in Outlook to a specific Address when I close the spreadsheet. Now what I would also like is to auto save as well. I can add code to save to a specific directory, but I would like the browse window to open so I could save to a certain dir. Kind of like using the saveas option in the file menu. I know it's easy to just go File/Save As, but this is a spreadsheet that I have designed for multiple users, (65 in Total) and to avoid any screw ups, I would like to add the autosave when closing the timesheet. Can anyone tell me how to get the save as browse window to open?


    Thank You

    Quote

    Originally posted by Rennie
    Another way of doing this not using macros is as follows. In the adjasent column to your data type in an if formula -if(cell=0,"T",1) and copy it down to your data range. Then select the formula range and edit>goto>special>text. This will select all the rows that have 0 values. Now edit>delete>row.


    Hope this helps.
    Rennie


    Thank You very Much. This solved my problem.


    :cheers:

    The macro ran, but nothing happened. What I have is 2 Colums A and B. Column A has the material names and Column B has the dollar value. If the dollar value = 0, then I want to delete that row. I could manually do this, but as you can see, there's 3500 lines to go through. To do this for 10 accounts will be a huge pain in the rear end. If you have any other ideas, I'm open to suggestions.


    :cheers:

    Hi, I was trying to write a macro for a spreadsheet I have. What I would like to do is, between a range of cells, if the cell value is 0 then delete the entire row. Now I have been working on code for this, but I have failed every time with the macro deleting all rows. Here are some snipits from my code.



    Sub delete()
    For Each C In Worksheets("Sheet2").Range("B4:B3556")
    If C.Value = 0 Then
    Rows.delete
    End If
    Next C
    End Sub


    Any help with this would be Great.


    :guitar: