Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming |