I am trying to write a formula in VBA do fill a column in a excel sheet. I can do the formula in a cell on a cell by cell basis but I want to copy this formula to the worksheet by VBA. This worksheet can be of varying lengths from 2000 rows to 10,000 rows. The data is imported into excel and I want to get another piece of statistical data from it. The data that is imported is 11 columns wide with the first column always with data the other columns may or may not have data on any given row.
I am looking to add a formula in column 12 that looks at data in column 2 depending on information in column 10 as to how big this range of data may be. I am calculating the min value for this range of data.
There is a value placed in column’s 9, 10 and 11 at the start of the data range in column 2. Each value in each row reflects a 4 sec time reading. The value in column 10 reflects the seconds of this data range (23sec), so the rows would be the value of column 10/4 rounded to nearest whole number.
Given this information I am trying to calculate the min value in this range. This range will vary over the data set, from 3-4 rows to as many as 400+ rows. This is based on the value in column 10 at the first row of this range in column 2. An example of this would be
Rows
A B C D E F G H I J L K
1 2 3 4 5 6 7 8 9 10 11 12 (Column)
1 1/3/2019 04:09:44 92 80
2 1/3/2019 04:09:48 92 79
3 1/3/2019 04:09:52 92 79
4 1/3/2019 04:09:56 92 78
5 1/3/2019 04:10:00 91 78 start 23 8 ???
6 1/3/2019 04:10:04 90 78
7 1/3/2019 04:10:08 90 77
8 1/3/2019 04:10:12 89 77
9 1/3/2019 04:10:16 89 78
10 1/3/2019 04:10:20 90 77
11 1/3/2019 04:10:24 92 77
12 1/3/2019 04:10:28 92 78
The data range starts on the row that is filled with data in column’s 9-11 and extends the # of rows reflected in cell J5 (23/4 = 6) rows down. I want to fill in the data in row 5 column 12(K).
The formula that works when placed in that cell is:
=IF(INDIRECT("I"&(ROW()))<>"Desat start",””,MIN(INDIRECT("B"&(ROW())):INDIRECT("B"&((ROW()+ROUND(J5/4,0)+1)))))
I want to put this in K1 and then copy down the whole sheet, because I don’t know where the column’s 9 – 11 will have data in them. When there is data, the formula will calculate the min for that range, if no data in Column’s 9 – 11 it will leave it blank.
When I try to use the R[?]C[?] notation in VBA it stars to break down and will not work in the editor. How do I write this formula in VBA so I can fill this column? Because Data in column 9-11 is sporadic, the only way I’ve found to do this is to put the formula in all of the rows.
Bookmarks