PDA

View Full Version : Solved: Transform the position of data on a table



Ismael
05-29-2007, 09:46 AM
Hello to all,

My doubt is regarding the transformation of data that is on a table.

I have attached the file for good understanding.

Any help is welcome.

Thanks in advance.

Best regards,

Ismael

mdmackillop
05-29-2007, 02:14 PM
I'm not sure if this is what you're after, but table 2 will update when you change values in range(F8:I65536)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long
Dim cel As Range
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
k = 5
If Not Intersect(Target, Range("F:I")) Is Nothing Then
For i = 6 To 9
For Each cel In Range(Cells(8, i), Cells(Rows.Count, i).End(xlUp))
If cel <> "" Then
k = k + 1
Cells(k, 11) = Cells(cel.Row, 3)
Cells(k, 12) = Cells(cel.Row, 4)
Cells(k, 13) = Cells(cel.Row, cel.Column)
Cells(k, 15) = Cells(7, cel.Column)
Range(Cells(k, 11), Cells(k, 15)).Interior.ColorIndex = _
Cells(7, cel.Column).Interior.ColorIndex
End If
Next
Next
End If
Application.EnableEvents = True
End Sub

Ismael
05-30-2007, 08:52 AM
Hi Mdmackillop,

In first place thanks for the reply.

Sorry but I don?t understand how I can update the table, can you explain me?

At the first look it?s exactly what I want. I pretend whit more column, but I think that I will be able to do that.

Kind regards,

Ismael

Ismael
05-30-2007, 08:59 AM
Sorry I'm using excel 2007, and where this doesn't work.... I try on Excel 2003 and everything works ok.

The problem is that I don?t have success on expand this for more columns. Could you please explain me how to do it.

Best regards,

Ismael

mdmackillop
05-30-2007, 09:40 AM
I've made the number of columns dynamic, based on data entered in row 7

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long
Dim col As Long
Dim cel As Range

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
col = Cells(7, 6).End(xlToRight).Column
k = 5
If Not Intersect(Target, Range(Columns(6), Columns(col))) Is Nothing Then
For i = 6 To col
For Each cel In Range(Cells(8, i), Cells(Rows.Count, i).End(xlUp))
If cel <> "" Then
k = k + 1
Cells(k, col + 2) = Cells(cel.Row, 3)
Cells(k, col + 2).NumberFormat = Cells(cel.Row, 3).NumberFormat
Cells(k, col + 3) = Cells(cel.Row, 4)
Cells(k, col + 4) = Cells(cel.Row, cel.Column)
Cells(k, col + 6) = Cells(7, cel.Column)
Range(Cells(k, col + 2), Cells(k, col + 6)).Interior.ColorIndex = _
Cells(7, cel.Column).Interior.ColorIndex
End If
Next
Next
End If
Application.EnableEvents = True
End Sub

Ismael
05-30-2007, 02:13 PM
Hi Mdmackillop,

Once more thanks for the help. I see the changes that you have done. But maybe I don't explain very well my idea.

What I pretend is more columns on table 1.

The perfect thing will be table 1 on sheet 1 and table 2 on sheet 2. In this way I will have the possibility to expand table 1 for example from column F to column BA, and all the values will be updated on table 2 on sheet 2.

Is this possible?

Best regards,

Ismael

mdmackillop
05-30-2007, 03:44 PM
Eminently possible
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long
Dim col As Long
Dim cel As Range

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
col = Cells(7, 6).End(xlToRight).Column
k = 5
If Not Intersect(Target, Range(Columns(6), Columns(col))) Is Nothing Then
For i = 6 To col
For Each cel In Range(Cells(8, i), Cells(Rows.Count, i).End(xlUp))
If cel <> "" Then
k = k + 1
With Sheets("Sheet2")
.Cells(k, 2) = Cells(cel.Row, 3)
.Cells(k, 2).NumberFormat = Cells(cel.Row, 3).NumberFormat
.Cells(k, 3) = Cells(cel.Row, 4)
.Cells(k, 4) = Cells(cel.Row, cel.Column)
.Cells(k, 6) = Cells(7, cel.Column)
.Range(.Cells(k, 2), .Cells(k, 6)).Interior.ColorIndex = _
Cells(7, cel.Column).Interior.ColorIndex
End With
End If
Next
Next
End If
Application.EnableEvents = True
End Sub

Ismael
05-31-2007, 01:51 AM
Hi Mdmackillop,

It's perfect. It's exactlly what I pretend.

Thanks a lot.

Best regards,

Ismael

Ismael
04-20-2009, 02:55 AM
Hello Mdmackillop,

Sorry to bother, but I can't put this working....

In attach you can find an excel macro file with your code on it, but nothing is happen... What I'm doing wrong?

Thanks in advance.
Best regards.
Ismael

mdmackillop
04-20-2009, 12:00 PM
You've deleted the original attached file which makes my reply meaningless.

Ismael
04-20-2009, 01:57 PM
Hi,

Yes I Know... But I don't now where I put the original file. It's possible for you to help me with this new file?

Thanks in advance.
Ismael

mdmackillop
04-20-2009, 04:30 PM
Here is the table with working code. As I said before, I don't really know what you are after.

Ismael
04-21-2009, 03:32 AM
Thanks Mdmackillop!

In attach you can find a picture with more explation about my idea.

Best regards.
Ismael

mdmackillop
04-21-2009, 05:41 AM
With Sheets("Sheet2")
.Cells(k, 2) = Cells(cel.Row, 3)
.Cells(k, 2).NumberFormat = Cells(cel.Row, 3).NumberFormat
.Cells(k, 3) = Cells(cel.Row, 4)
.Cells(k, 4) = Cells(cel.Row, cel.Column)
.Cells(k, 5) = Cells(6, cel.Column) '<======= Line added
.Cells(k, 6) = Cells(7, cel.Column)
.Range(.Cells(k, 2), .Cells(k, 6)).Interior.ColorIndex = _
Cells(7, cel.Column).Interior.ColorIndex

End With

Ismael
04-21-2009, 10:27 AM
Hi,
It's almost perfect... The only thing that is not "correct" is:
If a departure flight doesn't have bags coming in the arrivals flights the code doesn't have to write anything...if you check on the example you will see that for example for the flight TP 920 (cell L6) the code will return:
STA
FLT
07:45/10
TP 920
07:45/10

The objective is return “anything” if doesn’t exist any arrival bags.
Thanks once more.
Best regards.
Ismael

mdmackillop
04-21-2009, 11:32 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, j As Long, k As Long
Dim col As Long
Dim cel As Range

If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
col = Cells(7, 6).End(xlToRight).Column
k = 5
If Not Intersect(Target, Range(Columns(6), Columns(col))) Is Nothing Then
For i = 6 To col
For Each cel In Range(Cells(8, i), Cells(Rows.Count, i).End(xlUp))
If cel <> "" And cel.Row > 7 Then
k = k + 1
With Sheets("Sheet2")
.Cells(k, 2) = Cells(cel.Row, 3)
.Cells(k, 2).NumberFormat = Cells(cel.Row, 3).NumberFormat
.Cells(k, 3) = Cells(cel.Row, 4)
.Cells(k, 4) = Cells(cel.Row, cel.Column)
.Cells(k, 5) = Cells(6, cel.Column)
.Cells(k, 6) = Cells(7, cel.Column)
.Range(.Cells(k, 2), .Cells(k, 6)).Interior.ColorIndex = _
Cells(7, cel.Column).Interior.ColorIndex
End With
End If
Next
Next
End If
Application.EnableEvents = True
End Sub

Ismael
04-22-2009, 01:03 AM
Thanks a lot! Now is perfect.

Best regards.
Ismael