Posts by markc

Re: Code bombs out at If-End if

Thanks all -

I changed the For - Next as follows

For Each C In Range("A1:A" & lngRows)
        If IsEmpty(C) Then 'Some rows in col are empty some are not
            If myValue = 0 Then 'If cell is empty and myvalue is 0 do nothing
                C = ""
            Else
                C.Value = myValue 'Otherwise assign myValue to ActiveCell
            End If
        Else
            myValue = C.Value 'If new value assign to myValue
        End If
    Next C

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

Thanks
-marc

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
-marc

Sub myfillprocess()
    Dim myValue As Long
    Dim C As Range
    Dim lngRows As Long
    
    'Setup Environment
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    'Get number of rows
    lngRows = Worksheets("Data").Range("F65536").End(xlUp).Row
    
    'Where to start
    Worksheets("Data").Activate
    Range("A1").Select
    
    'Initialize
    myValue = 0
    Cells(1, 1).Activate
    
    For Each C In Range("A1:A" & lngRows)
        If IsEmpty(ActiveCell) Then             'Some rows in col are empty some are not
            If myValue = 0 Then                 'If cell is empty and myvalue is 0 do nothing
                ActiveCell = ""
            Else: ActiveCell.Value = myValue    'Otherwise assign myValue to ActiveCell
        Else: myValue = ActiveCell.Value        'If new value assign to myValue
            End If
        End If
    Next C
    
    'Calculate
    Worksheets(2).Calculate
    
    'Reset environment
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
End Sub

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.

Thanks
-marc

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
-marc

Hi all

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

=month(D10)

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?

Thanks
-marc

Hi all -

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

Here's formula:

=SUM(IF(PL_Tables!$W$4:$W$3671=$B7,IF(PL_Tables!$X$4:$X$3671=$C7,PL_Tables!$Z$4:$Z$3671,0),0))

ctrl+shift+enter of course

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

TIA
-marc

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 :

Sub Hide_Worksheets()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        'sh.Visible = True
        ws.Visible = xlSheetHidden
    Next ws


End Sub




Sub View_Financial_Report()
    Sheet3.Visible = xlSheetVisible
End Sub

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

Thanks
-marc

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
-marc

Hi all -

Is there a way to unload all loaded forms at once
as part of clean up procedure before close?

Thanks much
-marc

Re: Delete row if many different text strings encountered

Thanks Batman -

Here's my revised code :

Sub DeleteRow_OnText()
    Dim rngData
    Dim lngRows As Long
    Dim lngCurrRow As Long
    Dim avarStrings As Variant
    Dim intStringNo As Integer
     
    Application.ScreenUpdating = False
    avarStrings = Array("Division", "Area", "Region", "District", "Sub-Dist", "Customer Total:", "UnitTotal:", "Customer", "Name")
    lngRows = ActiveSheet.Range("B65536").End(xlUp).Row
     
    Set rngData = ActiveSheet.Range("B1:I" & lngRows)
    lngCurrRow = lngRows
    Do Until lngCurrRow = 0
        For intStringNo = LBound(avarStrings) To UBound(avarStrings)
            If InStr(1, rngData(lngCurrRow, 1), avarStrings(intStringNo)) > 0 Then
                rngData(lngCurrRow, 1).EntireRow.Delete
                Exit For
            End If
        Next
        lngCurrRow = lngCurrRow - 1
    Loop
End Sub

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

Thanks
-marc

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
Next
End Sub

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

Thanks
-marc

Hi all

The fine folks at Able2Extract
http://<br>http://www.investintech.com/<br>
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

TIA
-marc

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
Ghostscript
Able2Extract
pdf2txt

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

TIA
marc

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.
-marc

Re: Filter / Advanced Filter not working

Thanks JF -

Quote

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:
All
Top 10...
Custom...
0
M-test

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 :
=COUNTIF($I$6:$I$5712,$H1)

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

TIA
-marc

Hi all

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

Here is my formula :
=IF(ISERROR(VLOOKUP(A5698,Not_Paid,11,FALSE)),0,(VLOOKUP(A5698,Not_Paid,11,FALSE)))

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?

Thanks
-marc

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.

Thanks
-marc

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

='C:\ME_FY06\[ME-019.xls]REP-WKLY'!D$16

I broke it into parts and built a concatenation formula

=CONCATENATE("='",$B28,"[",C28,"]",G$5,"'!",G$7,G$6)
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
Thanks
-marc

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

Thanks!
-marc

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

TIA
-marc