Very basic code assistance please....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to align two rows of data containing date and time of equipment
readings. One is taken every second and one every 3 seconds ....
A B C
D
02/01/05 13:22:01 <data> 02/01/05 13:22:01 <data>
02/01/05 13:22:02 <data> 02/01/05 13:22:04 <data>
02/01/05 13:22:03 <data> 02/01/05 13:22:07 <data>
02/01/05 13:22:04 <data> 02/01/05 13:22:10 <data>

I need to align D with A so that the times match. I fugure that if I use
something like the following it should work but I am not sure of the syntax
for looping or going to the next row.
What I want to code is:
If(C1<>A1)insert a row and move the data down until it does match, then go
to the next row and loop the code until EOF .....I tried to create the code
but failed...
For N=1 to 1000
IF(C1<>A1)
Range("C1").Select (how do I change C1 ...is it C1+1??)
Selection.Insert Shift:xlDown
Next
 
Nigel,

Try this

Sub ReOrder()
Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "C").End(xlUp).Row
For i = cRows To 2 Step -1
Cells(i, "C").Resize(1, 2).Insert shift:=xlDown
Cells(i, "C").Resize(1, 2).Insert shift:=xlDown
Next i
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks ffor the effort Bob but the screen just jumps and flickers to infinity
and nothing changes. Am I supposed to add anything to your code or is it
complete?

Nigel

Bob Phillips said:
Nigel,

Try this

Sub ReOrder()
Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "C").End(xlUp).Row
For i = cRows To 2 Step -1
Cells(i, "C").Resize(1, 2).Insert shift:=xlDown
Cells(i, "C").Resize(1, 2).Insert shift:=xlDown
Next i
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Not possible. It might not do what you actually want, but there is insert.

I re-created your data, and it ended up like

02/01/05 13:22:01 <data> 02/01/05 13:22:01 <data>
02/01/05 13:22:02 <data> <blank>
02/01/05 13:22:03 <data> <blank>
02/01/05 13:22:04 <data> 02/01/05 13:22:04 <data>
<blank>
<blank>
02/01/05 13:22:07
<data>
<blank>
<blank>
02/01/05 13:22:10
<data>


--

HTH

RP
(remove nothere from the email address if mailing direct)


Nigel Forge said:
Thanks ffor the effort Bob but the screen just jumps and flickers to infinity
and nothing changes. Am I supposed to add anything to your code or is it
complete?

Nigel
Click to expand...
 
Many thanks Bob

I realized that the data does not always follow the 2 second delay hence the
apparent error. I now need to configure the code to compare the date and time
values from col A with col C to see if they match and if they do then move on
if not then insert however many rows are needed to align the data.
If you can help further I would very much appreciate it, if not, then
thankls for what you haev done so far.

Nigel
 
Hi Bob

I want to say thanks as we managed to add the necessary code to make this
work for the erratic dates and times too as follows:

Private Sub CommandButton1_Click()

Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cRows Step 1
If (Worksheets("Sheet1").Range("A" & i) <>
Worksheets("Sheet1").Range("C" & i)) Then
Cells(i, "C").Resize(1, 2).Insert shift:=xlDown
End If
Next i


End Sub

Many thanks for giving us the basis of the code for this one.

Nigel
 
Back
Top