Adjusting cells to cells

roka001

Board Regular
Joined
Aug 6, 2007
Messages
72
Hello!

I get reports that are stamped with the time when a number of instances have occurred.
The problem is that these reports are unsorted.
Example:
02:05 23,
05:34 43,
06:54 43,
07:13 54,
and so on.

I manage this by adjusting the cells so that they match a range from 00:00 to 23:00 with 1 minute range.
The timestamp is in column B and the value is in column C. The scale is in column A.

I use this code:

range("b1").select
do until activecell.offset(0,-1) = "" '

if activecell = activecell.offset(0,-1) then
activecell.offset(1,0).select
else
ActiveCell.Resize(1, 2).Insert xlShiftDown
end if

loop

Now the question ... Suppose that these reports have a few thousand rows. When I run my macro, it takes a while for it to compleat.
I wonder if there is no better way to synchronize the cells with each other.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
First off, you could speed things up quite a bit by not selecting, it is generally not necessary to select ranges in VBA, you can work with ranges directly in VBA.

I don't quite understand what it is you are trying to do.
You do seem to be inserting rows for some reason, why?

Your explanation includes 3 columns but your sample, includes I'm guessing 2. Is the sample you show columns B and C, does the value in Column C actually contain a comma.

Why can't you sort from Column B?

What is in Column A?
 

roka001

Board Regular
Joined
Aug 6, 2007
Messages
72
Hi!

Column A contains the scale that I want the data in column C to sync with. The macro I typed in adjusts column B and C so that the value in column B syncs with the same value in column A

Example 1: Raw data
Column A Column B Column C
00:00 00:05 20
00:01 00:11 35
00:02 00:13 41
00:03 00:14 29
00:04 00:17 12
00:05
00:06
00:07
00:08
00:09
00:10
00:11
00:12
00:13
00:14
00:15
00:16
00:17
00:18
00:19

Example 2: Processed Data
Column A Column B Column C
00:00
00:01
00:02
00:03
00:04
00:05 00:05 20
00:06
00:07
00:08
00:09
00:10
00:11 00:11 35
00:12
00:13 00:13 41
00:14 00:14 29
00:15
00:16
00:17 00:17 12
00:18
00:19

The only way I can think of is to insert rows until the values match. The reports I get contains loads of data with time stamps. What I'm trying to do is to get then to sync with a time scale so that its easy to use a diagram.

The method I'm using works but it takes to long to process. The question is if there is an other faster way to do it?
The time stamps when the data occures are random. Its not possible to predict when they will occure.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top