Re: Count time intervals of first sampling
This works just fine. Like you said, I changed the code to sample just the first 100 rows. This will be enough to give the sample rate.
Thank you very much.
Oscar
Re: Count time intervals of first sampling
This works just fine. Like you said, I changed the code to sample just the first 100 rows. This will be enough to give the sample rate.
Thank you very much.
Oscar
Re: Count time intervals of first sampling
ShosMeister,
The time samplings can change thats why I'm looking for something to count what it is, it won't always be 8. What you presented does work, but is there anyway to just count the first sampling. In other words if the first sampling is 20 times a minute, I would only need to look at cell A2 and count how many times that value happened. Currently it is counting each set of samplings which would all be the same after counting the first set. Just trying to cut down on the execution time because the A column can 100s of thousands of lines.
thank you
Oscar
Re: Count time intervals of first sampling
Hi Craig,
I need this in a macro because it will be used with larger macro that starts out with no sheet to enter the funstion on.
Oscar
Hello,
I have a program that takes data samplings every so many second or minutes or what is specified. I'm looking for a vba macro that will count how many samplings are taking in a minutes time or an hours time. If you look a the attached file you see the first time on row 2 happened 8 times. So this could be 60 times a minute or 2 times a minute. I guess what I need is a vba looks at the time in cell A2 and counts how many times it samples. In the example file, I would want to count 8 times then put that count into a variable x = timecountsample.
thank you
Oscar
Re: Combining multiple cells that are not adjacent to each other
Wait, now it works. I cut and pasted the code again and it's working. Thanks Rory.
Oscar
Re: Combining multiple cells that are not adjacent to each other
Thanks. That sort of works, except it is grabbing all the cells in between M2 thru C2, and C2 thru A2. I'm trying to have the order in cell Q to be A2 - C2 - M2 - N2 - O2 - P2.
Oscar
Hello all,
I’ve been using a macro that someone on this site so graciously provided (sorry don’t remember who). It works great for combining cells that are adjacent to each but now I need add another that is not. For example, it currently combines cells P2, O2, N2, M2 together with a – in between. I Need it to combined P2, O2, N2, M2, and C2 and A2. All ending up in cell Q2 with a – between them.
Thanks
Oscar
Sub CombineData()
Const sSep As String = " - " ' change as desired
Dim cell As Range
For Each cell In Intersect(Columns("Q"), ActiveSheet.UsedRange.EntireRow)
cell.Value = Cat(Range(cell.Offset(, -4), Cells(cell.Row, Columns.Count).End(xlToLeft)), sSep)
Next cell
End Sub
Function Cat(vInp As Variant, _
Optional sSep As String = " - ", _
Optional bCatEmpty As Boolean = False) As String
' Catenates the elements of vInp separated by sSep
' Empty values and null strings are ignored unless bCatEmpty is True
Dim vItem As Variant
Dim sItem As String
If bCatEmpty Then
For Each vItem In vInp
Cat = Cat & CStr(vItem) & sSep
Next vItem
Else
For Each vItem In vInp
sItem = CStr(vItem)
If Len(sItem) Then Cat = Cat & sItem & sSep
Next vItem
End If
Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function
Display More
I'm trying to use the import data to pull from a closed csv file. I see the column select but no place to select which rows. Is this even possible, I only need the first 500 rows of all columns to be placed in a new worksheet.
thx
oscar
Re: Copy Specific Files Into Seperate Worksheets
I'm currently pulling all csv files from a directory into a workbook, place in seperate worksheets. I don't need all the files and would like to call three specific files by name.
Sub Macro8()
'
'
Dim strPath As String
Dim strFile As String
'
strPath = "C:\temp\"
strFile = Dir(strPath & "*.csv")
Do While strFile <> ""
With ActiveWorkbook.Worksheets.Add
With .QueryTables.Add(Connection:="TEXT;" & strPath & strFile, _
Destination:=. Range("A1"))
.Parent.Name = Replace(strFile, ".csv", "")
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End With
strFile = Dir
Loop
End Sub
Display More
I have been using the attached to pull multiple csv files into seperate sheets. This takes all files from a user specified directory and dumps them into a workbook. I only need three specific files. How do I request specific files instead of a wild card.
Re: Two Criteria Find & Concatenate Adjacent Cells
Thanks for the reply Dave. The code works great but not exactly what I was trying to do. I'm sure I not expaining myself very well, so I'll try again.
I don't need to concatenate anymore because I will do this before hand.
I've attached my worksheet showing five columns. I would like to search through column A looking for the first Isle ID of 1, then search through column B looking for a Shelf ID of 1. After two criteria have been met, I need to copy and paste the contents of column E in the cell directly ajacent from the search criteria of Isle 1 and Shelf 1, which happens to be 1-1, to a fixed location. If there is no location then skip. I need this to loop through until all location have been search. The attached worksheet is only a small portion of my data but Isle ID's do not go beyond 20 Isles and Shelf ID does not go beyond 14. This changes daily so I need to create a physical map of our parts locations every morning.
Thanks much
Oscar
Sorry if this is a stupid question, but I'm very new to VBA.
I have four columns of data. The first two are my criteria data and the last two are the data I'm trying to capture. So I need to search column A if the number one exist and column B has a number six then contcatenate the ajacent cells in column C and D and put the output in cell H1.
Thanks for any help.
Oscar
Re: Search A Header Or Specific Row For Specific Text Then Select That Column
I could, but I want to put this in a macro and have it select and copy all the values below the text and paste to another workbook.
Oscar
Re: Search A Header Or Specific Row For Specific Text Then Select That Column
I'll attach an example file, maybe i'm not using the right terminology
Row A contains specific text in each column. My problem is, the order of columns changes depending on the version of vendor software i'm using. Instead of selecting a fixed column range, i.e. F1:F10, I would like to run a macro to select a column by the text string in row A i.e. "Write Req/s", then copy to another workbook.
Thx
Oscar
Is there a way to search the header of a file or specific row for a specific string of text, then select the entire column below that string?
Thx
Oscar
Is there a way to fill a cell in excel with data from an access query. I just need one number from one query, from one field.
Thx
Oscar
Re: Percentile Function From Query In Access
Thanks Zimitry, I can work with that.
Re: Percentile Function From Query In Access
Great. Can I call this from a query?
Re: Percentile Function From Query In Access
Well, I'll give you an example. I'm trying to duplicate the =Percentile function in excel but for access. The following is an example of my table and field in access.
CPUPercentage
2
4
5
3
4
3
3
4
3
4
4
3
4
With excel, the 75th percentile is 4 and the 95th is 4.4. I would like to duplicate this function with access.
Thx
Oscar
I have a table in access with a field with 2000 rows of data. Each row contains a percentage number of how much cpu is being used. The numbers are small between .5 thru 8. I'm try to pull the 75th percentile number from this field with a query. Any help would be appreciated. I need something simple because I'm very new at this.
Thx
Oscar