• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get values from a column & display it in a pre-defined range of columns

inddon

Member
Hello There,

I have values in Sheet1 in a particular column. I would like to display it in Sheet2 in a fixed set of columns.

I am aware this can be done using arrays/collections. I tried my best but couldn't get it done (wiped out everything).

Could you please help me out how this can be done using arrays and collections? Your code will be of help in the future as well.

I have attached a sample workbook for your reference.

Look forward to hearing from you.

Thanks & regards,
Donald
 

Attachments

  • Sample Workbook List BLN.xlsm
    13.9 KB · Views: 12

Hi !

Very bad idea to merge cells ! Very not needed …
Easy just with Excel basics without …
Rearrange your destination worksheet and attach a new workbook
or just activate the Macro Recorder and operate manually
in order to get your own code base …
 
Hi Marc,

Thank you for your reply.

Just for my understanding why is it a bad idea to merge cells? I have a few workbooks where cells are merged. I can take necessary steps to keep it single cell instead.

Attach a new workbook w/o cell merge for your reference.

Regards,
Don
 

Attachments

  • Sample Workbook List BLN.xlsm
    12.4 KB · Views: 7
Last edited by a moderator:
Even for visual it can be avoided with the Center across option …
Just for my understanding why is it a bad idea to merge cells?
'Cause the easy Excel basics fails with merged cells … (Copy method)

According to your last attachment
as a beginner starter respecting the TEBV main rule :​
Code:
Sub Demo1()
    With Sheet2
       .[D8:D27] = Sheet1.[C5:C24].Value2
       .[C8:C27] = .[IF(D8:D27>0,ROW(A1:A20),"")]
       .[F8:F27] = Sheet1.[C25:C44].Value2
       .[E8:E27] = .[IF(F8:F27>0,ROW(A21:A40),"")]
       .[H8:H27] = Sheet1.[C45:C64].Value2
       .[G8:G27] = .[IF(H8:H27>0,ROW(A41:A60),"")]
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
 
Have a read of...
https://chandoo.org/forum/threads/macro-column-sort-with-merged-cells.38597/#post-231072
http://datascopic.net/xlcaliber-7deadlysins/
http://www.excel-user.com/2012/01/avoid-merged-cells-in-excel.html

In my opinion, merged cell is the worst feature in Excel, and should be avoided. Only exception, is at last stage of analysis/reporting, where no further calculation/analysis is needed, as visual fluff only.

Thanks Chihiro for the link. That is very useful

Regards,
Don
 
Even for visual it can be avoided with the Center across option …

'Cause the easy Excel basics fails with merged cells … (Copy method)

According to your last attachment
as a beginner starter respecting TEBV rule :​
Code:
Sub Demo1()
    With Sheet2
       .[D8:D27] = Sheet1.[C5:C24].Value2
       .[C8:C27] = .[IF(D8:D27>0,ROW(A1:A20),"")]
       .[F8:F27] = Sheet1.[C25:C44].Value2
       .[E8:E27] = .[IF(F8:F27>0,ROW(A21:A40),"")]
       .[H8:H27] = Sheet1.[C45:C64].Value2
       .[G8:G27] = .[IF(H8:H27>0,ROW(A41:A60),"")]
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !


Thank you Marc for the beginner code :). I have removed the merge cells

Could you please be so kind to let me know how this can be done using Array or Collection. I tried doing it, but gave up, thats why this thread. Your sample code for this purpose will help me understand to apply the working of it in the future too.

Thanks again & regards,
Don
 
Hi Marc,

Thank you for your reply.

In the sample wotkbook, in Sheet1, the values are in column C5:C46. The code will work good. However, when the values can vary, can be less, at times will have more values

How can the values in Sheet1 be listed in an array in a generic way and then use that generic array to list the values Sheet2. The cells in Sheet2 where these values will be displayed are fixed.


Eg.
Array = Sheet1 first value in column C to last value in column C
Using a Array Loop, assign values in Sheet2 to the fixed cells starting from:
Goto cell C8 and display values from C8 (array sequence) & D8 (array Sheet1 value)
If row = 28 then go to cell E8 ( array sequence) & F8 ( array Sheet1 value)
If row = 28 then go to cell G8 ( array sequence) & H8 ( array Sheet1 value)

Thank you for taking the time for helping me.

Regards,
Don
 
Last edited by a moderator:
Unclear for your both « If row = 28 » and seems a worse way very not
necessary as like you wrote the Sheet2 cells are fixed so my demo
yet does the job whatever the number of source values …
I won't transform replace an obvious smarter Excel solution avoiding
a slower loop to a pure VBA gas factory code
with at least twice as many codelines using a loop !
Why indirectly looping on an array as my demo directly uses an array ?‼
 
Hi Marc,

You are right. The ranges are fixed in Sheet2. Why loop through when it can be done straightforward. I will always keep this in mind :)

Your solution works perfect and is faster :awesome: and I have already implemented the same.

I was curious to learn more about arrays. Could you recommend any resources where I can study and grow my knowledge in this area?

Many thanks and wish you a pleasant weekend.

Regards,
Don
 
Last edited by a moderator:

To start see the VBA inner help of Dim, Redim, Array, Range.Value, …

Thanks and good weekend as well.
 
In the attached (a copy of your attachment in msg #1; the one with merged cells) is a macro which can be run by clicking the button on the Sheet2-Display Reference sheet.
This macro can be made to work on your second workbook too (the one without merged cells) with one adjustment (hStep = 2).
The start of the macro has 8 self-explanatory lines which you can adjust:
Code:
SourceSheetName = "Sheet1-List of Reference"
DestnSheetName = "Sheet2-Display Reference"
FR = 5    'first row of source data.
LR = Sheets(SourceSheetName).Cells(Rows.Count, "C").End(xlUp).Row 'last row of source data.
vStep = 20    ' no. of rows per column on result sheet
hStep = 8    'no. of columns separating the columns on the result sheet (shouldn't be 1).
RowNo = 1    'first Sr. number.
Set Destn = Sheets(DestnSheetName).Range("C8")    'where first Sr. no. will be placed (top left of results data)

The whole code:
Code:
Sub blah()
SourceSheetName = "Sheet1-List of Reference"
DestnSheetName = "Sheet2-Display Reference" '"Sheet1"
FR = 5  'first row of source data.
LR = Sheets(SourceSheetName).Cells(Rows.Count, "C").End(xlUp).Row 'last row of source data.
vStep = 20  ' no. of rows per column on result sheet
hStep = 8  'no. of columns separating the columns on the result sheet (shouldn't be 1).
RowNo = 1  'first Sr. number.
Set Destn = Sheets(DestnSheetName).Range("C8")  'where first Sr. no. will be placed (top left of results data)

'All set, let's go:
Do
  With Destn.Resize(Application.Min(vStep, LR - FR + 1))
  .Formula = "=ROW(A" & RowNo & ")"
  .Value = .Value
  .Offset(, 1).Formula = "='" & SourceSheetName & "'!$C" & FR
  .Offset(, 1).Value = .Offset(, 1).Value
  End With
  FR = FR + vStep
  RowNo = RowNo + vStep
  Set Destn = Destn.Offset(, hStep)
Loop Until FR > LR
End Sub
 

Attachments

  • Chandoo39499Sample Workbook List BLN.xlsm
    25 KB · Views: 1
Last edited:

As my post #5 code works on original attachment with merged cells
just updating the range addresses !
As my first idea was to use Range.Copy I warned upon merged cells …
 
Back
Top