Posts by holland1

Hi there,

I have time in Col A and values in Col B. I'm trying to get the average every 15 minutes but the number of row in each 15 minute block is variable.

I'm using this code:

Sub fifteenminaverage()




LastRow = Range("A" & Rows.Count).End(xlUp).Row
output = False
i = 2
Do Until i > LastRow
     If output = False Then
          firstOutput = "B" & i
          maxTime = Range("A" & i).Value + TimeValue("00:15")
          output = True
     End If
     If Range("A" & i).Value > maxTime Then
          i = i - 1
          Range("C" & i).Value = "=AVERAGE(" & firstOutput & ":B" & i & ")"
          output = False
     End If
     i = i + 1
Loop
i = i - 1
Range("C" & i).Value = "=AVERAGE(" & firstOutput & ":B" & i & ")"




End Sub

However, sometimes is does not work and calculates an average with an extra cell?

I've attached a small example. On row 65 the average should of been inserted in row 64. It is out by one.

Would really appreciate it if somebody could advise?

Many thanks

forum.ozgrid.com/index.php?attachment/63168/

Re: Loop through text files in a folder and save as excel files

Hi Luke,

I'm getting an error, perhaps you could help me with it?

The compiler error message is "Expected Function or variable", with highlight on the substring .OpenText.

I found this thread about the issue:

http://www.mrexcel.com/forum/e…s-workbooks-opentext.html

Thanks

Re: Loop through text files in a folder and save as excel files

Thanks Luke. Appreciate your help

Hi,

I have text files in a folder. I want to import each of them them into excel. I then want to save them as an excel file to the same folder.

I've recorded 2 macros below. The first opens the text file and imports it into excel in the format that I want. The second saves it as an excel file.

Would anybody know how I can modify these so it will do the same for all the text files in the folder and not just the one file name specified.

i.e. I just want to run the macro to process all the text files in the folder.

Many thanks

Workbooks.OpenText Filename:="E:\folder\testfile.txt", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=True, Other:=True, OtherChar:="^", FieldInfo:=Array(Array(1, 1) _
        , Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1)), TrailingMinusNumbers:=True

ActiveWorkbook.SaveAs Filename:="E:\folder\testfile.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I have attached an example text file if you would like to test it
forum.ozgrid.com/index.php?attachment/62449/

Note: I have also posted this question on another website

http://www.mrexcel.com/forum/e…der-save-excel-files.html

Re: Seeking separate data into columns given the hour

Bump

Hi there,

I have repetitive task of copying and pasting. I was hoping somebody might be able to help me out with a macro to sort some data. It would be much appreciated and would save me a lot of time.

I have time values (sorted in order) in Column A and a value in Column B.

I would like to separate the data into columns. Here is an example https://www.dropbox.com/s/otmj77zgr8kkj1r/ozgrid.xlsx

There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.

I appreciate your time and help

Regards

Gary

Note: i have also posted this question here http://www.mrexcel.com/forum/e…ven-hour.html#post3498952[h=2][/h]

Hi,

I have this code in a macro and it works on my laptop.

Randomize
        c = Format(rnd(1), "#0.00")
        c = Round(rnd, 2)

However, when I try run it on my desktop I get an error 'Can't find project or library' with the 'Format' word highlighted.

Could anybody tell me how to find out which library is missing and how I activate it?

Thank you

also posted here http://www.mrexcel.com/forum/e…function.html#post3428506

Re: extracting data from every 3rd column and appending in single column

Thanks for your help Jasper, appreciate your time.

Hello there,

Would anybody be able to help me out with a macro for a repetitive task. I have data in every 3rd column (max of 11 columns) that I want to extract and append the data in column A in sheet 2. The data contains some blank cells and I'd like to remove them also.

I've attached an example in this workbook.

Appreciate any help

Kind Regards

https://dl.dropbox.com/u/54057365/All/ozgridxtractdata.xlsx

Re: Counting and suming number of visible rows

sorry

Re: Counting and suming number of visible rows

bump

Re: Counting and suming number of visible rows

Hello again,

I tried the code below to count the visible the rows, but it always returns Totalrows =1, which is incorrect.

Totalrows = ch1.Range("D:D").SpecialCells(xlCellTypeVisible).End(xlUp).count

Also,

I tried for the formula, Prob = "Subtotal sum of column D" / "The number of visible cells".

Prob = WorksheetFunction.Subtotal(101, ch1.Range("D:D")) / ch1.Range("D:D").SpecialCells(xlCellTypeVisible).End(xlUp).count

This that the correct syntax for the subtotal?

Thanks

Hello,

The code below counts the number of rows, my question is, how would you count the number of visible rows?

Totalrows = ch1.Range("D" & Rows.count).End(xlUp).Row

also

How would you calculate the sum of the visible rows in column D, from D2 downwards?

I'm trying to write a formula,

Prob = "Subtotal sum of column D" / "The number of visible cells"

Thanks for the help

also posted here : http://www.mrexcel.com/forum/e…ble-rows.html#post3412653

Re: seeking help with an if loop or lookup formula to check data against a list of da

Thank you

Re: seeking help with an if loop or lookup formula to check data against a list of da

Hi Guys,

I couldn't get this work correctly in the end. I've tried it again and this time concatenated the columns to make it easier.

I have attached the workbook with some sample data and the 3 rules explained properly.

https://dl.dropbox.com/u/54057365/All/ozgridlookup.xlsx

I would be grateful if you had time to look at it again.

Kind regards

Re: filling in blank cells with nearest number

Thank you for your help Pike, this works very well.

Kind regards

Re: filling in blank cells with nearest number

Hello Pike,

[FONT=Arial, Helvetica, sans-serif]My apologies for not defining what I want clearly. What I am looking to do is to fill in the blank cells according to certain rules.

1. When a list of blank cells is buffeted by different values (i.e. 1 and 0) , I want to make half of them 1s and the other half 0s.

2. [/FONT] When a list of blank cells is buffeted by same values (i.e. 1 or 0) , I want to make all the cells either 1 or 0.

I have attached an example workbook, with a before and after and also included the rules for illustration.

https://dl.dropbox.com/u/54057365/All/ozgrid.xlsx

Thanks for you help

Kind Regards

Re: filling in blank cells with nearest number

bump

Hi there,

Would anybody be able to help me with a macro to 'fill in some data'.

I have a column of data with '1's and '0's with blank cell in between. I'm trying to fill in the blank cells with the nearest number.

There is an example in this worksheet. I have 105,841 rows.

https://dl.dropbox.com/u/54057365/All/ozgrid.xlsx

Greatly appreciate any help.

Kind Regards

Question also posted here.

http://www.mrexcel.com/forum/e…t-number.html#post3407810

Hello,

Would anybody be able to help me to write a complicated if loop or suggested another way of doing it perhaps by some sort of lookup.

In Column D I have an If loop which returns a statement (possible 3) if the GPS coordinates in columns B and C match the single GPS coordinate in F3:G3.

My problem is that I have a list of coordinates (20 +) in columns F and G and not a single value. I need the If loop (or lookup) to check the list and not just the single coordinate and return the correct statement. I've attached the workbook.

https://dl.dropbox.com/u/54057365/All/mrexcel.xlsx

Appreciate any help

Kind regards

Also posted [B]her[B]e: http://www.mrexcel.com/forum/e…satement.html#post3405863[/B][/B]