Posts by markc

markc · Mar 30th 2005

Hi all

Still having problems trying to activate a worksheet
Most likely it is the active worksheet, I just need to make sure it is at
run-time

Bugs out here:
In the GetData procedure
Error msg : Subscript out of range

Code

Worksheets("Sheet1").Activate

I also tried
Worksheet("Sheet1").Activate and
Sheets("Sheet1").Activate

The actual name of the sheet will always be "strip_pdf_text" w/o quotes
as defined by a .vbs process to convert from .pdf to .txt and then to .tsv.

The file contains only 1 sheet

Here is the entire code:

Thanks
-marc

Code

Sub AR_Analyze()
    Application.ScreenUpdating = False
    Call Clear_Old
    Call OpenSelectedFile
    Call Save_Active
    Call GetData
    Application.ScreenUpdating = True
End Sub


Sub Clear_Old()
    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10:F65536").Clear
End Sub


Sub OpenSelectedFile()
    Dim myFile As Variant
    myFile = Application.GetOpenFilename("E*xcel Files (*.xl*), *.xl*", _
        , "Pick one:")
    If myFile <> False Then Workbooks.Open (myFile)
End Sub


Sub Save_Active()
    ChDir "C:\AR"
    With ActiveWorkbook
        .SaveAs Filename:="C:\AR\myWorkingFile.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    End With
End Sub


Sub GetData()


    Application.DisplayAlerts = False
    Dim lngRows As Long


    Workbooks("myWorkingFile.xls").Activate
    Worksheets("Sheet1").Activate
    lngRows = Range("A65536").End(xlUp).Row
    
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
End Sub

Display More

markc · Mar 28th 2005

Re: Data in Pivot Table

Thanks Norie / Maqbool -

Now I get it.
I was trying to drag each field to the outer
Would not let me do that
Hence the confusion

Thanks again
-marc

markc · Mar 28th 2005

Re: Data in Pivot Table

Norie -
Still Can't get it

I start with the data field
I drag shopper payment to the data field
Count of Shopper payment field appears at A3
The word "Total" is at B4
And the count of payments down colB

When I drag other data fields they also continue
down colB

Can I move these other data fields over to Col C and D?
Or am I stuck with them in ColB?

I updated my example

Thanks
-marc

markc · Mar 28th 2005

Re: Subscript out of range error

The latter,

So how do I reference it?

Thanks
-marc

markc · Mar 27th 2005

Re: Data in Pivot Table

Thanks Norie -

I don't see total

Just these:
Row fields
Column Fields
Data Items
Page Fields

Thanks
-marc

markc · Mar 27th 2005

Re: Subscript out of range error

Code

With Workbooks("myFile.xls").Worksheets("Sheet1")

Thanks
-marc

markc · Mar 27th 2005

Hi all

I can't seem to figure out how to "unstack"
my data in pivot table.

I would like
Shopper payment
Cost
and Net to appear
with their respective records
Col C, D, and E

When I drag the fields to Data, they stack going down Col C

Thanks
-marc

markc · Mar 27th 2005

Re: Subscript out of range error

Thanks Will -

Here is everything I did / tried

OpenSelectedFile Procedure :

Code

Sub OpenSelectedFile()
Dim myFile As Variant
myFile = Application.GetOpenFilename("E*xcel Files (*.xl*), *.xl*", _
    , "Pick one:")
If myFile <> False Then Workbooks.Open (myFile)
End Sub

Then changed GetData Procedure
This error'd with Subscript out of range

Code

Sub GetData()


    Application.DisplayAlerts = False
    Dim lngRows As Long
    
    With Workbooks("myFile").Worksheets("Sheet1")
        lngRows = .Range("A65536").End(xlUp).Row
    End With
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
End Sub

Display More

Then tried adding the .xls extension
Same error message

Code

Sub GetData()


    Application.DisplayAlerts = False
    Dim lngRows As Long
    
With Workbooks("myFile.xls").Worksheets("Sheet1")
        lngRows = .Range("A65536").End(xlUp).Row
    End With
   'With myFile.Worksheets("strip_pdf_text")
        'lngRows = .Range("A65536").End(xlUp).Row
    'End With
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
End Sub

Display More

Thanks
-marc

markc · Mar 24th 2005

Re: Subscript out of range error

Hi Dennis
That didn't work either.
The name of the sheet is not sheet1
Is that the problem?
I do not know the name of the sheet
The user may change it

Thanks
-marc

Quote from XL-Dennis

marc,

In addition to what CM Will points out You may also try:

[vba]
With ActiveWorkbook.Worksheets("Sheet1")
lngRows = .Range("A65536").End(xlUp).Row
End With
[/vba]

The With-statement is necessary if the sheet You refer to is not the active sheet.

Display More

markc · Mar 24th 2005

Re: Subscript out of range error

Hi Will

I used the code to open a workbook with a single sheet named
"strip_pdf_text".

Here is the procedure for opening the file

Code

Sub OpenSelectedFile()
Dim LeFile As Variant
LeFile = Application.GetOpenFilename("E*xcel Files (*.xl*), *.xl*", _
    , "Pick one:")
If LeFile <> False Then Workbooks.Open (LeFile)
End Sub

markc · Mar 24th 2005

Hi all

My procedure allows user to browse for file for copy/paste
So I don't know the name of the file the user is choosing

I'm trying to use this code to handle whatever the user
selects.

Code

lngRows = ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row

But The code is erroring out at this point
Run time error 9
Subscript out of range

Seems like I am using an invalid method of ActiveWorkbook.
Is there a better way to handle this?
Thanks
-marc

Full code:

Code

Sub AR_Analyze()
    Application.ScreenUpdating = False
    Call Clear_Old
    Call OpenSelectedFile
    Call GetData
    Application.ScreenUpdating = True
End Sub


Sub Clear_Old()
    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10:F65536").Clear
End Sub


Sub GetData()


    Application.DisplayAlerts = False
    


Dim lngRows As Long


    lngRows = ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
End Sub

Display More

markc · Mar 24th 2005

Re: Wildcard search / replace

Thanks Dennis -

Created the clean file
But the output is gibberish
Attached

Thanks
marc

markc · Mar 23rd 2005

Re: Wildcard search / replace

Thanks Dennis -

Code is hanging here
Error is RunTime error 62
Input past end of file

Code

stContents = Input(LOF(fNum), fNum)

Currently last line used is 1640, but that will expand/contract weekly

Thanks much
-marc

markc · Mar 23rd 2005

Re: Wildcard search / replace

Hi Dennis -

Nothing. E-mail out to outside party for their backup
For student privacy want to remove SSN

Thanks
-marc

markc · Mar 22nd 2005

Hi all -

I have a text file I open in xl
I need to replce SSN's of this format
999 99 9999 with xxx xx xxxx

Of course the 999 99 9999 just a sample and actual
numbers will vary

Does always occur first 11 char's of the string

Sample workbook attached
Thanks
-marc

markc · Mar 15th 2005

Re: Paste special method failed...

Hi Norie -

Here is the corrected code :

Code

Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Still receive the "Paste special..." error

To run
Alt + F8 to activate macro menu
Click macro
Run

The controlling macro actually calls several subprocedures
of which the above is a part.

Complete:

Code

Sub AR_Analyze()
    Application.ScreenUpdating = False
    Call Clear_Old
    Call OpenSelectedFile
    Call GetData
    Application.ScreenUpdating = True
End Sub


Sub Clear_Old()
    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10:F65536").Clear
End Sub


Sub GetData()


    Application.DisplayAlerts = False
    


Dim lngRows As Long


    lngRows = ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_ANALYZE.xls").Activate
    Sheets("SDX_AR").Select
    Range("A10").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
End Sub

Display More

OpenSelectedFile procedure is in a separate module.

Thanks
-marc

markc · Mar 15th 2005

Re: Paste special method failed...

Timbo -

Tried your code
Came back Subscript out of range
Highlighted here

Code

Workbooks("AR_ANALYZE").Activate

I tried commenting the line out
Came back with paste special method failed

Also, most of what I've read on the forum suggests
not using Select

Indeed I ran into some problems with slow code execution
due to select command.

See my previous post Re: slow code execution.
Thanks
-marc

markc · Mar 15th 2005

Hi all -

I keep receiving
"Paste Special method of range class failed"

I've tried re-writing the code several different ways,
none of which works.

Can you give me a pointer at what I'm doing wrong?

Thanks
-marc

Code

Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10").PasteSpecial _
     Paste:=xlValues, Operation:=xlNone, _
     SkipBlanks:=False, Transpose:=False

markc · Mar 14th 2005

Hi all -

I am receiving message
"There is a large amount of data..."

When I try to copy data from source
Close Source
Paste to destination

I read up on Google and thought my code should fix the problem
But I still receive same message and code error's out

Thanks
-marc

Code

Sub AR_Analyze()
    Application.ScreenUpdating = False
    Call Clear_Old
    Call OpenSelectedFile
    Call GetData
    Application.ScreenUpdating = True
End Sub


Sub Clear_Old()
    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10:F65536").Clear
End Sub


Sub GetData()


Dim lngRows As Long


    lngRows = ActiveWorkbook.Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    Worksheets("Sheet1").Range("A2:F" & lngRows).Copy
    ActiveWorkbook.Close SaveChanges:=False
    
    Workbooks("AR_Analyze.xls").Worksheets("SDX_AR").Range("A10").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Display More

markc · Mar 11th 2005

Re: Object visible on mouse hover (click)

Thanks

I'll update my knowledge level to complete moron

Thanks
-marc