reference a variable range via VBA

[luvbite38]

Hi guys,

I've tryin to find a solution for following situation for such a long time and had no luck so far. I am hoping to kindly get some help from anyone. Thanks in advance.

Problem:

I have a live table which will grow with the time (i.e. user will continue feed data into the table). In that table i wish to add three every time value changes in column A (i.e. excluding the headers in Row1). And want macro to type certain values in first blank row, in second row I want it do sum all the x value and in third row sum all the y values. To explain it better find the current and desired tables below.

Current Table

 

[luvbite38]

Hi guys,

I've tryin to find a solution for following situation for such a long time and had no luck so far. I am hoping to kindly get some help from anyone. Thanks in advance.

Problem:

I have a live table which will grow with the time (i.e. user will continue feed data into the table). In that table i wish to add three every time value changes in column A (i.e. excluding the headers in Row1). And want macro to type certain values in first blank row, in second row I want it do sum all the x value and in third row sum all the y values. To explain it better find the current and desired tables below.

Current Table

ColA ColB ColC ColD ColE ColT
rr aa 12 A 1 R250N 1200 1 1200 1.1
rr aa 16 A 1 R250N 1200 1 1200 1.1
KAL aa 12 A 1 R250N 1200 1 1200 1.1
KAL aa 12 A 1 D500N 1200 1 1200 1.1
KAL aa 24 A 1 D500N 1200 1 1200 1.1
KAL aa 36 A 1 D500N 1200 1 1200 1.1
KAL aa 40 A 1 D500N 1200 1 1200 1.1
KAL aa 12 A 1 R250N 1200 1 1200 1.1
KAL aa 12 A 1 R250N 1200 1 1200 1.1


Desired Table


ColA ColB ColC ColD ColE ColT
rr aa 12 A 1 R250N 1200 1 1200 1.1
rr aa 16 A 1 R250N 1200 1 1200 1.1
RR - Total @10 @12 @16 @20 @24 @28 @32 @36 @40
Summary - R250N Mass (kg) 0 1.1 1.1 0 0 0 0 0 0.0
Summary - D500N Mass (kg) 0 0 0 0 0 0 0 0 0.0
KAL aa 12 A 1 R250N 1200 1 1200 1.1
KAL aa 12 A 1 D500N 1200 1 1200 1.1
KAL aa 24 A 1 D500N 1200 1 1200 1.1
KAL aa 36 A 1 D500N 1200 1 1200 1.1
KAL aa 40 A 1 D500N 1200 1 1200 1.1
KAL aa 12 A 1 R250N 1200 1 1200 1.1
KAL aa 12 A 1 R250N 1200 1 1200 1.1
RR - Total @10 @12 @16 @20 @24 @28 @32 @36 @40
Summary - R250N Mass (kg) 0 3.3 0 0 0 0 0 0 0
Summary - D500N Mass (kg) 0 0 0 0 1.1 0 0 1.1 1.1


I hope it makes sense?

Regards

 

[luvbite38]

The value in Column T is Mass
and Value in Column F is Either R250N or D500N