VBA Code
 

Hello,

Is it possible to write a code that will read column A, Then B, then C and
place the data from column D into a different sheet that has a different
set-up?

The list is A and B are columns of a table and Column C is the rows of the
table. Column D is the Data that is needed to populate in the table.

VBA Code
 

Hi,
Can you give an example as it not clear (to me!) what exactly is
required?

"GBH99" wrote:

Hello,

Is it possible to write a code that will read column A, Then B, then C and
place the data from column D into a different sheet that has a different
set-up?

The list is A and B are columns of a table and Column C is the rows of the
table. Column D is the Data that is needed to populate in the table.

VBA Code
 

I have had this written into a workbook but it does work,

Sub test()
Dim a, dic As Object, w(), x, z, i As Long, ii As Integer
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Range("a1").CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
a = .Value
.ClearContents
End With
End With
For i = 1 To UBound(a, 1)
z = a(i, 1) & ":" & a(i, 2)
If Not dic.exists(z) Then
ReDim w(1 To 4)
For ii = 1 To 4: w(ii) = a(i, ii): Next
dic.Add z, w
Else
w = dic(z)
ReDim Preserve w(1 To UBound(w) + 2)
w(UBound(w) - 1) = a(i, 3)
w(UBound(w)) = a(i, 4)
dic(z) = w
End If
Next
x = dic.items: Set dic = Nothing: Erase a
With Range("a2")
For i = 0 To UBound(x)
.Offset(i).Resize(, UBound(x(i))) = x(i)
Next
End With
Erase x
End Sub

I have a list that looks like this:-

Mtrl Product Area Slot Status
Slot1 ACCESS EQPT Barnsley, Donc & Roth Sold
Slot1 ACCESS EQPT Bournemouth Sold
Slot1 ACCESS EQPT Cambridge Reserved
Slot1 ACCESS EQPT Norwich Reserved

This data does not exactly match the main table, the main table has 2205
products (All listed Slot1 then slot2) and 104 areas in a table and needs the
slot status to come across or Say "Available" if nothing found.

Thanks for looking at this.


"Toppers" wrote:

Hi,
Can you give an example as it not clear (to me!) what exactly is
required?

"GBH99" wrote:

Hello,

Is it possible to write a code that will read column A, Then B, then C and
place the data from column D into a different sheet that has a different
set-up?

The list is A and B are columns of a table and Column C is the rows of the
table. Column D is the Data that is needed to populate in the table.

VBA Code
 

Hi,
What about the current code doesn't work? There is no test for
column D being blank and therefore inserting "Available" in this case.

What else is wrong? And I have to admit I don't fully follow how the code
works!


"GBH99" wrote:

I have had this written into a workbook but it does work,

Sub test()
Dim a, dic As Object, w(), x, z, i As Long, ii As Integer
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Range("a1").CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
a = .Value
.ClearContents
End With
End With
For i = 1 To UBound(a, 1)
z = a(i, 1) & ":" & a(i, 2)
If Not dic.exists(z) Then
ReDim w(1 To 4)
For ii = 1 To 4: w(ii) = a(i, ii): Next
dic.Add z, w
Else
w = dic(z)
ReDim Preserve w(1 To UBound(w) + 2)
w(UBound(w) - 1) = a(i, 3)
w(UBound(w)) = a(i, 4)
dic(z) = w
End If
Next
x = dic.items: Set dic = Nothing: Erase a
With Range("a2")
For i = 0 To UBound(x)
.Offset(i).Resize(, UBound(x(i))) = x(i)
Next
End With
Erase x
End Sub

I have a list that looks like this:-

Mtrl Product Area Slot Status
Slot1 ACCESS EQPT Barnsley, Donc & Roth Sold
Slot1 ACCESS EQPT Bournemouth Sold
Slot1 ACCESS EQPT Cambridge Reserved
Slot1 ACCESS EQPT Norwich Reserved

This data does not exactly match the main table, the main table has 2205
products (All listed Slot1 then slot2) and 104 areas in a table and needs the
slot status to come across or Say "Available" if nothing found.

Thanks for looking at this.


"Toppers" wrote:

Hi,
Can you give an example as it not clear (to me!) what exactly is
required?

"GBH99" wrote:

Hello,

Is it possible to write a code that will read column A, Then B, then C and
place the data from column D into a different sheet that has a different
set-up?

The list is A and B are columns of a table and Column C is the rows of the
table. Column D is the Data that is needed to populate in the table.

VBA Code
 

the code isn't working at all, it's full of errors by the looks of it. I have
a report that I cut and paste into a sheet on the workbook every fortnight
and then click a button and the list is supposed to populate in the table.
There is lots of spaces on the table after the list populates but I am not
worried by this anymore, I need it to just empty the table and populate the
data in the correct sections, if you want I could e-mail you the spreadsheet
so you could see it.

"Toppers" wrote:

Hi,
What about the current code doesn't work? There is no test for
column D being blank and therefore inserting "Available" in this case.

What else is wrong? And I have to admit I don't fully follow how the code
works!


"GBH99" wrote:

I have had this written into a workbook but it does work,

Sub test()
Dim a, dic As Object, w(), x, z, i As Long, ii As Integer
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Range("a1").CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
a = .Value
.ClearContents
End With
End With
For i = 1 To UBound(a, 1)
z = a(i, 1) & ":" & a(i, 2)
If Not dic.exists(z) Then
ReDim w(1 To 4)
For ii = 1 To 4: w(ii) = a(i, ii): Next
dic.Add z, w
Else
w = dic(z)
ReDim Preserve w(1 To UBound(w) + 2)
w(UBound(w) - 1) = a(i, 3)
w(UBound(w)) = a(i, 4)
dic(z) = w
End If
Next
x = dic.items: Set dic = Nothing: Erase a
With Range("a2")
For i = 0 To UBound(x)
.Offset(i).Resize(, UBound(x(i))) = x(i)
Next
End With
Erase x
End Sub

I have a list that looks like this:-

Mtrl Product Area Slot Status
Slot1 ACCESS EQPT Barnsley, Donc & Roth Sold
Slot1 ACCESS EQPT Bournemouth Sold
Slot1 ACCESS EQPT Cambridge Reserved
Slot1 ACCESS EQPT Norwich Reserved

This data does not exactly match the main table, the main table has 2205
products (All listed Slot1 then slot2) and 104 areas in a table and needs the
slot status to come across or Say "Available" if nothing found.

Thanks for looking at this.


"Toppers" wrote:

Hi,
Can you give an example as it not clear (to me!) what exactly is
required?

"GBH99" wrote:

Hello,

Is it possible to write a code that will read column A, Then B, then C and
place the data from column D into a different sheet that has a different
set-up?

The list is A and B are columns of a table and Column C is the rows of the
table. Column D is the Data that is needed to populate in the table.

VBA Code
 

Hi,
e-mail to

"GBH99" wrote:

the code isn't working at all, it's full of errors by the looks of it. I have
a report that I cut and paste into a sheet on the workbook every fortnight
and then click a button and the list is supposed to populate in the table.
There is lots of spaces on the table after the list populates but I am not
worried by this anymore, I need it to just empty the table and populate the
data in the correct sections, if you want I could e-mail you the spreadsheet
so you could see it.

"Toppers" wrote:

Hi,
What about the current code doesn't work? There is no test for
column D being blank and therefore inserting "Available" in this case.

What else is wrong? And I have to admit I don't fully follow how the code
works!


"GBH99" wrote:

I have had this written into a workbook but it does work,

Sub test()
Dim a, dic As Object, w(), x, z, i As Long, ii As Integer
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Range("a1").CurrentRegion
With .Offset(1).Resize(.Rows.Count - 1)
a = .Value
.ClearContents
End With
End With
For i = 1 To UBound(a, 1)
z = a(i, 1) & ":" & a(i, 2)
If Not dic.exists(z) Then
ReDim w(1 To 4)
For ii = 1 To 4: w(ii) = a(i, ii): Next
dic.Add z, w
Else
w = dic(z)
ReDim Preserve w(1 To UBound(w) + 2)
w(UBound(w) - 1) = a(i, 3)
w(UBound(w)) = a(i, 4)
dic(z) = w
End If
Next
x = dic.items: Set dic = Nothing: Erase a
With Range("a2")
For i = 0 To UBound(x)
.Offset(i).Resize(, UBound(x(i))) = x(i)
Next
End With
Erase x
End Sub

I have a list that looks like this:-

Mtrl Product Area Slot Status
Slot1 ACCESS EQPT Barnsley, Donc & Roth Sold
Slot1 ACCESS EQPT Bournemouth Sold
Slot1 ACCESS EQPT Cambridge Reserved
Slot1 ACCESS EQPT Norwich Reserved

This data does not exactly match the main table, the main table has 2205
products (All listed Slot1 then slot2) and 104 areas in a table and needs the
slot status to come across or Say "Available" if nothing found.

Thanks for looking at this.


"Toppers" wrote:

Hi,
Can you give an example as it not clear (to me!) what exactly is
required?

"GBH99" wrote:

Hello,

Is it possible to write a code that will read column A, Then B, then C and
place the data from column D into a different sheet that has a different
set-up?

The list is A and B are columns of a table and Column C is the rows of the
table. Column D is the Data that is needed to populate in the table.