Posts by markc

    Re: Code bombs out at If-End if

    Thanks all -

    I changed the For - Next as follows

    I receive a type mismatch error here:

    myValue = C.Value 'If new value assign to myValue

    Barry - VBE added the ":" automatically when I typed
    I did think I needed some line continuation characters
    to break the IF-Then-Else-EndIf.
    I will remember for the future


    Hi all -

    Having a little trouble with this code in the nested
    IF Else End if constructs.

    Could you point me in the right direction please?
    Code untested so if you see anything else I would appreciate any pointers

    Thnks much

    Re: personal.xls is now invisible

    Thanks Dangelor

    Looked at the Windows menu previously
    Didn't see anything because I didn't click 2 x chevron
    to expand full menu

    I think the folks at Redmond lay awake at night thinking
    of new ways to torture me. :)


    Hi all -

    All of a sudden I cannot see any of the sheets in personal.xls
    It's open all macros are available but I cannot see any of the sheets

    I store frequently used formulas and notes.

    Thanks much

    Hi all

    I entered today's date 6/29/05 in D10
    My formula at B10


    I formatted as MMMM
    B10 returns January
    I attmepted a wide range of dates at D10
    All returned January
    I [F9] after every entry just in case

    What am I doing wrong?


    Hi all -

    I have a very slow array formula
    3627 rows x 9 columns
    Plus lookups running
    A very slow process

    Here's formula:


    ctrl+shift+enter of course

    How can i re-write to evaluate two criteria, when condition met
    add values in 3rd column?


    Hi all -

    I have some code in a cmd button click event

    Private Sub cmdFinancialReport_Click()
        Call Hide_Worksheets
        Call View_Financial_Report
    End Sub

    Called procedures :

    All but 1 sheet are hidden
    Debug halts at ws.Visible = xlSheetHidden


    Hi all -

    I would like to set visible/hidden property of a sheet by name not by index.
    Seems index could change as I modify my workbook but name should be consistent.

    Here's what I have

    Worksheets("Financial_Summary").Visible = True

    Thanks much

    Re: Delete row if many different text strings encountered

    Thanks Batman -

    Here's my revised code :

    The array is defined as:

    "Division", "Area", "Region", "District", "Sub-Dist", "Customer Total:", "UnitTotal:", "Customer", "Name"

    Yet some the lines that I would expect to be deleted are still present
    What am I doing wrong?

    workbook attached


    Hi all

    Using a macro to move trailing negative to front of the number
    However it is converting my numbers

    The code:

    Sub ChangeToNeg()
    For Each Cell In Selection
        If Right$(Cell.Value, 1) = "-" Or Right$(Cell.Value, 2) = "CR" Then
            Cell.Value = -Val(Cell.Value)
        End If
    End Sub

    But losing some number values
    Please see attached
    ColA,B show result after Chng to Neg


    Hi all

    The fine folks at Able2Extract
    were finally able to resolve my .pdf to .xls dilemma
    However, now I am left with far more data than I need

    I found this great little piece of vba on UseNet

    Sub Remove_Rows()
        For Each c In Range("B2:B300")
            If InStr(c.Value, "remove row") > 0 Then c.EntireRow.Delete Shift:=xlUp
        Next c
    End Sub

    But I need to edit for many different string possibilities
    Seems inefficient to start a new loop after this one completes
    How would I re-write to check for many different strings

    Range unknown # of rows maybe max 30,000
    # of cols maybe 12

    These may change in future pdf reports
    AS may strings to be searched and rows deleted


    Hi all
    I have tried many different ways to convert pdf to excel
    the best seems to be to highlight the data in adobe acrobat using the table/fomatted text tool >> right click >> save as ANSI.txt >> open with Excel

    However, will only let me highlight a singel page in the document at a time
    My current document is 200 pages.

    Does anyone know how to beat this limitation or create a batch process?

    btw, I have tried these utilities

    They all failed at negative numbers formatted as such
    ( 200,000) The right parend is output to Col A
    The remainder is output to col B

    In addition, labels such as TJE 098789 Roth are output to three columns instead of 1


    Re: Filter / Advanced Filter not working

    JF -

    Someone e-mail'd me the Do-Not-Pay list
    I did not check it for duplicate records
    As soon as I did, I found that 7 records had been duplicated

    What a waste, I've been looking at this for the better part of the day.

    Thanks for your help.

    Re: Filter / Advanced Filter not working

    Thanks JF -


    Try copying the value from once cell (col 11) in the "Not_paid" table and past it (paste special values) into the other rows so that they are all identical and then try again.

    I did. I typed "M-test" (w/o quotes) and copied down so same
    Indeed, drop-down in auto-filter mode confirms by offering only these choices:
    Top 10...

    After rebuilding tables and lookups and recalc'ng, I manually searched
    through the 5,707 records for the Invoice Numbers in question (there are 19 expected results)

    I verified M-test is returned 19 times. However, both auto-filter and countif return 12 records.

    Countif formula :

    Where I copy/pasted "M-test" to $H1 (w/o quotes)
    The range $I$6:$I$5712 contains the vlookup formula to compare
    the invoice table to the do_not_pay table.

    The spreadsheet is 8.8 MB.
    I mad a copy and stripped it to the bare bones leaving the 2 tables in question with meaningful data. Even under these conditions and zip'd came out about 600 KB. Too big to upload.

    I'd be happy to e-mail if you would like to take a look at it


    Hi all

    I am running a vlookup on a table of 5709 records against a table of 19 records.

    Here is my formula :

    Column 11 on Not_Paid table has same value for every record : M-Test

    I searched Table_5709, indeed I see 19 records that return M-Test. This is expected.

    However, when I filter the table by M-Test or used Advance Filter, only 12 records are returned. Some of the records included in the filter are near the top of the table, some are near the bottom of the list. The seven that are missing are in the middle.

    I ran DeleteRow on Cell Macro to make sure have continuous range of data.

    Any ideas?


    Re: Building a link formula by concatenating the parts

    Thanks Kris

    It is returning #REF! error when I pasted to the correct cell
    I then opened the file it is referencing and hit [F9]

    The expected value was then displayed.

    I then deleted the formula, closed the referred to spreadsheet
    and re-entered the formula.

    I received the #REF! error again even after [F9].

    Is there any way around this, other than opening each referred to spreadsheet? There are ~40 spreadsheets to be referred to.


    Hi all

    I would like to build a flexible link formula where user
    can change any 1 of the parts and formula will recalculate based on new choices

    Here is the link created by enter = in cell on wrk1 and clicking cell in wrk2


    I broke it into parts and built a concatenation formula

    B28 is path
    C28 is file
    G5 is tab
    G7 is Col
    G6 is row

    Is there a way to accomplish?
    If no this method, a different way?
    User friendly is best.
    Users don't necessarily know row links
    I was thinking of a drop down of word choices
    Then use vlookup to get number based on word chosen

    My spreadsheet attached

    Re: print cells to right footer

    Thanks Jack -

    A simple deposit log with values updated daily
    And ever changing user-defined print range

    The range for right footer
    includes 2 signatory lines for
    2 accounting team members


    Hi all

    Im trying to get cells $F$65532:$H$65536 to print to
    right footer

    Here's what I have right now, but not working
    Only 1 sheet in the book

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        With ActiveSheet.PageSetup
            .RightFooter = Sheets(1).Range("$F$65532:$H$65536")
        End With
    End Sub