Posts by markc

Re: sum single occurrence from column criteria other column

Thanks Norie -
No.

The total shipping cost in each highlighted instance should be $10.00

Thanks
-marc

Re: sum single occurrence from column criteria other column

Sorry 'bout that
Workbook attached
I highlighted the rows with recurring shipping charges

Thanks
-marc

Hi all -
Using xl2003.

Need to sum a single occurrence of recurring output in ColJ based on criteria from Col H

Example

ColG ColH ColJ
10 5427 10
0 5427 10

The example has 2 lines, but this is variable from 1 to infinite
Cannot delete any rows as other columns contain unique data

TIA
-marc

Re: Import data from txt file

Thanks Andy

I sent an e-mail to the folks who send me the txt file
(SQL server)

I'll have to wait to see what they have to say

I opened in xl and I see that lines 6 and 7 should really be part of line 5
Assumed since I don't see the order # at the front of the string
Am I correct?

Thanks
-marc

Re: Import data from txt file

Thanks Andy -
Can I see the <CR> is some way?
I searched for but nothing found
Can you tell me line and space number?

Maybe if I go back to the folks who send me the file
and tell them a specific example they can figure something out

Thanks
-marc

Hi all
Using xl2003

I need to import certain fields from a text file
Tried using ImportTextFile from Chip pearson [code at end]
Results were kind of sporadic

Just need to pull
Order_Number
tax
ship
qty
price
price variation

and dump to the activesheet.activecell
[Sample of txt file attached]

TIA
-marc

Public Sub ImportTextFile(FName As String, Sep As String)
'Chip Pearson 01.24.2005


Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer


Application.ScreenUpdating = False
'On Error GoTo EndMacro:


SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row


Open FName For Input Access Read As #1


While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
Wend


EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1


End Sub


Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String


FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
End If


Sep = InputBox("Enter a single delimiter character.", _
        "Import Text File")
ImportTextFile CStr(FName), Sep


End Sub

Hi all
Using xl2003

How do I add divider bars to group my icons?

Thanks
-marc

Re: cutomize toolbars use icon instead of text

Thanks Will

Works great, which I'm sure you realize

A little bit cheezy that I have to first create
a cutom button utilizing faceid, copy the button image, paste the button
image.

The standard paletteof 42 icons is far too limiting.

Thanks again
-marc

Hi all
Using xl2003

I want to customize a toolbar
I go through all the steps
And drag Advanced Filter... to the toolbar
Problem is, this takes up valuable real estate

How can I replace with an icon?
I have jwalk's faceid.xla if that is of any assistance

Thanks
-marc

Re: Workbook name as string

Thanks!
That did it.

-marc

Re: Workbook name as string

Thanks all

Here's my current code

Option Explicit
Sub myTest()


Dim wb1 As Workbook
Dim strFile As String
 
strFile = "C:\Documents and Settings\mystuff\My Documents\chicago\Revenue" & _
          "\FAS_Tools\Jan_05\Progress_Inv_Dtl_Scrub_Mstr_bak.xls"


Windows(strFile).Activate
Worksheets("Order_Nmbrs2").Range("G2").Activate
 
Range("G2") = "HI"


End Sub

This returns a run-time error '9':
Subscript out of range

All I'm trying to do is to make sure that
Progress_Inv_Dtl_Scrub_Mstr_bak.xls
has the focus prior to importing large amount of data from
csv file

Here is the offending line:
Windows(strFile).Activate

I also tried:
Windows("strFile").Activate

Thanks
-marc

Re: Workbook name as string

Thanks TJ
Yes it is
I just want some fault tolerance in case
I try to run and the wb does not have the focus.

Need to make sure the sql import [DoTheImport]
isiported exactly where specified so no ill consequence

Thanks
-marc

Hi all
Using xl 2003

Trying to refer to workbook with variable after
assiging full name as string :

Option Explicit
Sub Reconcile_detail()




Dim wb1 As String


Application.ScreenUpdating = False


wb1 = "C:\Documents and Settings\mystuff\My Documents\Chicago" & _
      "\Revenue\REV_Tools\Jan_05\Progress_Inv_Dtl_Scrub_Mstr.xls"


Workbooks.Activate wb1
Worksheets("Order_Nmbrs2").Activate
Range("G2").Activate
Call DoTheImport


'Need to determine the columns to delete
'objExcelSheet.range("A:H,L:Q").Delete
'Columns("D").Delete


Application.ScreenUpdating = True


End Sub

Problem is this line, can't figure out why

Workbooks.Activate wb1

I've tried writing it 10 different ways, no help.
Can anyone give me a point in the right direction?

Thanks
-marc

Re: toggle window visibility

Thanks Ray and Dangelor.
Works great

Ray -
Yup, want to keep all macros avail, but take personal.xls out of the equation when tiling windows. And by using just one macro to handle the visible/invisible, saves hotkey shortcut memorization

Thanks
-marc

Re: Morefunc nbtext

Dennis/Zack -

Thanks.
Seems Laurent is aware of the issue
His website:

Quote

Last update : "log10" error fixed (NBTEXT function)

I deleted all morefunc files, downloaded new morefunc.exe, reinstalled
nbtext still hangs Excel. (2003 11.6355.6360 SP1)

Have not tried any of the other functions
Will do so and post back

Thanks
-marc

Hi all.

Using xl 2003

Downloaded and installed Morefunc.exe from Laurent Longre

http://longre.free.fr/english/

Tried to insert the nbtext function
XL simply hangs
Formula bar shows = nbtext()
I cannot click on anything or proceed in anyway

Has anyone run across this?
Thanks
-marc

Re: toggle window visibility

Me again -

Recorded macro and saw part of my problem
Revised :

Sub Toggle_Personal_Vis()
    Workbooks("Personal.xls").Activate
    If ActiveWindow.Visible = False Then
        ActiveWindow.Visible = True Else:
        ActiveWindow.Visible = False
    End If
End Sub

Still not working
Compile error syntax error
I've tried many variations moving the Else statement everywhere and line continuation. Nothing seems to work

Thanks
-marc

Hi all.

In addition to storing frquently used macros in personal.xls
I also store frequently used formulas, lists, and other doodads

Sometimes I want to see everything in personal, but sometimes when tiling I want personal hidden from view.

I created a macro and assigned ctrl+shift+p to it, but can't seem to get it to work.

Sub Toggle_Personal_Vis()
    If Workbooks("Personal.xls").Visible = False Then
    Workbooks("Personal.xls").Visible = True
        Else: Workbooks("Personal.xls").Visible = False
    End If
End Sub

But not working.
I also tried Alt+F8 and running the macro - no luck
Do I need fully distinguished path? c:\program files\....

Thanks
-marc

Re: output to txt file

Alan -

Quote

Are you saying 'that' only two lines were output and you were expecting 7?

Yes.

Quote

With respect to my comment on using archive or archive2. Once you have run the process and you are happy with the results then I would delete the old archive.txt and rename the file archive2.txt to archive.txt. Then append future archive data to that. This will limit the ongoing growth of the archive file. For an audit trail at the moment I would not change the process.

Sounds good. Thanks.

Files are attached
Thanks
-marc

Re: output to txt file

Thanks Alan
Awesome as always

Quote

Whether you continue to use the original archive file or the archive2.txt file is up to you

If I use archive.txt, code errors out here

'    On Error GoTo DiskError
     'Open input and output files
    intFileIn = FreeFile()
    Open strArchiveFilename2 For Input As #intFileIn

If I use archive2.txt, the code executes

I copied a few lines of interspersed records into archive.txt
I then opened with xl as csv,sorted and manually checked for uniqueness

In my sample of order 6024, I found 7 unique lines.
Col AE = qty
Col AG = unit price
Col AO = SKU

The Catering sales people or customer used SKU ...171 twice, but the quantity was different. Don't know why they would do this, but then I'm just an accounting/data geek.

I searched for 6024 on the full archive file
Textpad returned occurrences at these rows
2404
2408
2409
2412
2413
2414
2415
7 lines as expected
No other groupings were found during the manual search

I ran the code, 2 lines were written out to "orders"
SKU460-180-184 line 5 of sample
SKU460-180-181 line 6 of sample

Sorry so long
Thanks again for all of your efforts
-marc