Posts by mike015

Re: Break Apart City State And Zip Into Different Cells

I am afraid that is a little over my head.
I don't have that version of Excel and wouldn't know what could be used as a replacement in that code.

The source for that code was here.
http://www.ozgrid.com/VBA/ExtractNum.htm

I will look into a different solution

Re: Break Apart City State And Zip Into Different Cells

Give this a try,

It has two Functions each in their own modules

The Original combined text in column A
and formulas in columns B:D

Mike
(First time trying to help someone else)

Re: Conditional Slope Of Range

Quote from Domenic

Maybe...

=SUM(N(OFFSET(XPos,MATCH(5.5,XPos,1)-1,{3,0}))*{1,-1})/SUM(N(OFFSET(XPos,MATCH(4.5,XPos,1)-1,{3,0}))*{1,-1})

Hope this helps!

This works well.

Quote from shg

Your data is noisy (plot it), and your recent formula just takes the slope across the end-points of the segment, where the SLOPE function does a linear regression on the interval. I don't think you'll be happy with that solution.

I agree that my current data is extremely noisy. This is not typical of my data. We are using a very low sample rate without any subsampling. generally there is not much difference in the values near the points. using the regression could be benefitial but I don't know that it would be necessary.

Thank you
Mike

Re: Conditional Slope Of Range

Thank you for the solution, It does work.

I came up with a way to do it without having to rearrange the columns, I have other data in those columns.

by defining the following as named ranges

Rise1 =LOOKUP(5.5,'830'!$C$2:$C$115,'830'!$F$2:$F$115)
Rise2 =LOOKUP(5.5,'830'!$C$2:$C$115,'830'!$C$2:$C$115)
Run1 =LOOKUP(4.5,'830'!$C$2:$C$115,'830'!$F$2:$F$115)
Run2 =LOOKUP(4.5,'830'!$C$2:$C$115,'830'!$C$2:$C$115)

and then placing this formula in the worksheet.

=(Rise1-Rise2)/(Run1-Run2)

I am trying to find the slope of column F when Column C is between 4.5-5.5

I can do this by doing four seperate lookup formulas and having the slope function referring to the results.
When I try to combine the four cells into a single cell Excel gives me an error.

=slope((LOOKUP(5.5,XPos,XPsi),LOOKUP(5.5,XPos,Xpos)),(LOOKUP(4.5,XPos,XPsi),LOOKUP(4.5,XPos,XPos)))

Attached is an example of the data, the functioning slope when spread over four cells, and my attempt at combining the formulas(currently with an ' at the beginning to prevent the error)

Is there some VB code that can change all characters in a column to High caps?

Mike[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I ended up finding a simular solution in this post.
http://www.ozgrid.com/forum/showthread.php?t=55208
I was searching caps instead of case.

Re: Paste Named Range

Thank you,

That did work,

I had to fight with an error "400" but was able to get past that. now it works perfectly.

I am using Andy's scrolling chart, and it is working well.
http://www.andypope.info/charts/Scrolling.htm

Is there a way to paste the data the chart is displaying into a new sheet?
In VB I tried to copy the named range and paste it but it did not work.

Mike

Re: Matching Time Range

I have continued to work on this, I have come up with a formula that is working. I put this into sheet1!q2 and filled down. values within range show up and those out of range show as #NA.
.01 comes to around 14min.

INDEX(Sheet2!$A$1:$A$20000,MATCH(IF(P2>VLOOKUP(P2+0.01,Sheet2!$B$1:$B$20000,1,TRUE)+0.01,,VLOOKUP(P2+0.01,Sheet2!$B$2:$B$20000,1,TRUE)),Sheet2!$B$1:$B$20000))

I am open to any suggestions on how to clean this up or convert this to VB.
I am planning on pasting this formula in using a xlup then copying the results pasting as values.

I am looking for a way to match a time (Sheet1!P2) within +/-15 min, adjustable, in the range (Sheet2!B:B)and report the corresponding (Sheet2!A_) value. The final result is to be placed at (Sheet1!Q2).
This is to be applied to all the values in Sheet1!P_

I was thinking of using a nested Index(match) combination but I am unable to get a good result or set the +/- 15 min time span.

If there is no match I will end up deleting the row in sheet1

Thank You
Mike

I found a way to import some data from some csv files, and it works well for me. I don't want to keep the option to refresh the data.
I can go into each tab created from each import and and uncheck the box
Data>Import External Data>Data Range Properties>"save query definition" that removes it for me.
Is there a way to do that with code. It doesn't work with the macro recorder.

[Blocked Image: http://lh4.google.com/image/mike015pgh/RaZFVDKp9zI/AAAAAAAACxk/eWykwKFGGdg/C%3A%5CDocuments%20and%20Settings%5Cz03mrs%5CDesktop%5CSaveQueryDefinitionSM.jpg]

Sub ImportOnline()
'
' Macro1 Macro
' Macro recorded 1/11/2007 by Mike015
'


'
   On Error Resume Next
    
    Dim DayFile As String
    DayFile = InputBox("Enter Date of File (MMDDYY)")
    Sheets.Add
    ActiveSheet.Select
    ActiveSheet.Name = "B-" & DayFile
    Application.ScreenUpdating = False
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\My Documents\B-" & DayFile & ".txt", _
        Destination:=Range("A1"))
        .Name = DayFile
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With


    Application.ScreenUpdating = True
End Sub

I am trying to move to the end of one data set, then move down one row.
What is the line of code that lets me move down one row?
I am sure it is very obvious, but I cannot find it.
Mike

Range("A1").Select
    Selection.End(xlDown).Select
???

A coworker came to me with an issue I am unfamiliar with. Pivot Tables.

He is unable to get the 'Grand total for Rows' to work. He is able to get the totals for colums to work when he is creating a new pivot table. He has other old pivot tables created by someone else and the totaling works.

Are there some special conditions needed to get the grand totals for rows to work?
Thanks
Mike

Re: resample data

Thanks Ger Plante,

Your file works well, Even though I had done loops in school. I haven't been able to fully grasp how they work. One day I will look at them and it will all make sense. today is not that day.

The files do all have a 2second interval. I have a second logger that ran a 5second interval, and naturally didn't have an issue. I will be changing the future recordings at a 3+interval. Trial and error, Ididn't know they would be recording full days, I thought it was going to be one shift a day. I realized I had the rate too high the first day but didn't want to change it until the run completed.

21,500 rows would be fine.

Excel or VBA, as long at it works. I'm not picky that way.

The B column was not significant. It is generated by the logger.

I have several files of data that are from a data logger. The data is broken up by day. Each day has roughly 43000 rows of data, at its sample rate. This has made charting the data a nuisance. Is there a way to resample the data so it fits in the 32000 points excel can chart? In the future I will set a sample rate that will keep the number of points below the 32K per series.

I would like to be able to have the total number of points reduced by averaging the data not by simply deleting one of every four rows.

Thanks
Mike

Re: Creating an External VB Application?

That at got me pointed to what I was wondering. I found this online article. It gave a quick once over of using automation.

I will have to do more reading before delving too far into this project.

Thanks Bob.

http://www.developer.com/net/vb/article.php/1435641

Mike

Re: Creating an External VB Application?

None taken, I agree that working on the user forms would be my first step.

My first thought was to find how transparent of a process it is to take an excel vb and make it into a standalone. I have not tried to do it yet. If there are differences between the coding than I would rather begin in the standalone and debug once than to perfect the excel version and find all of the cell references and formulas are now different due to running outside of excel.

I do not know if externally run VB applications will play nice with excel data, I am expecting that all the formulas that I have now in excel will have to be written in the code.

If writing external code is not practical I will change my plans. I am hoping it is.

My excel VB code is at the point where it is powerful enough to do what I need it to. the problem is that it is not very user friendly.

I had planned to create some user forms to have a cleaner user interface, but I haven't had all that much success with it (and haven't been able to dedicate significant time to it yet either)

Rather than just having a form running in excel my thought was to go all the way and write the code as a stand alone VB application. Having the application pointing to a excel file for the raw data and dumping the manipulated data into a seperate file.

Has anyone done this before?
Thanks
Mike

Re: Streaming video in presentation?

edweeen

I tried to do that but I got an error:
"You cannot use an internet address here. enter a path that points to a location on your computer or on the network"

Is there a way to have a streaming video (*.asx) play in powerpoint?

I tried to add it by clicking the 'add movie from file' button, it said it needed to be stored locally.

If I click insert, object, windows media player. I can add a media player in the presentation, but I would figure that it would need to have the web address added in VB but don't know how.