Databases
StackOverflow2013
Postsdbo
POVBA Excel Loop Multiple Observations Per Subject/Record
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA Excel Loop Multiple Observations Per Subject/Record
    primarykey
    data
    text
    <p>I have a question regarding copying cells from one sheet to another using VBA. </p> <p><strong>Question Background:</strong></p> <p>Sheet1 of an excel spreadsheet contains headers and a number of test observations from multiple test subjects. Each row is an observation of one trial per subject. Each test subject has a static number of observations, i.e. 15 rows. I need to select an individual cell(s) from a specified row(s) and copy them to sheet2 of the same workbook into 1 row per subject. Sheet 2 will contain headers as well. Being new to VBA, and to programming/scripting in general, I’m having some issues wrapping my head around on how to do this. Below is a similar example of the data that I am trying to manipulate. The data set below contains 5 columns. The subject numbers are 1111 and 2222. Each heading of Variable() contains 1 letter, e.g. VariableOne = "a", VariableTwo = "b", etc. </p> <p><strong>Subject - VariableOne - VariableTwo - VariableThree - VariableFour</strong> </p> <p>1111 - a b c d</p> <p>1111 - e f g h</p> <p>1111 - i j k l</p> <p>1111 - m n o p</p> <p>2222 - a b c d</p> <p>2222 - e f g h</p> <p>2222 - i j k l</p> <p>2222 - m n o p</p> <p>For example, row 2 (due to headers) of sheet 2 will include the subject number 1111 and values “m” from VariableOne, “f” from VariableTwo, “c” from VariableThree, and “l” from VariableFour. Row 3 of sheet 2 will contain subject #2222 and the values listed in the same position as subject 1111 (the values will be different but their position within the data set will be the same). </p> <p>I think the code should include 2 loops; one to iterate through subjects and the other to iterate through the actual data. I’m not really sure as to how to go about doing this so if anybody has done this before, I would appreciate the help. </p> <p>Additionally, I’ve just gone through the book “Microsoft Excel VBA programming for the absolute beginner”, which was a nice introduction to VBA for excel but it didn’t help me with formulating any solutions for my personal VBA/excel needs. Does anyone know of a better source, such as a book, to become more acquainted with the VBA scripting language for excel? </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmatt
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COIt seems you wrote a story, and i felt very bore to read it. tell me in brief what you want us to here?
      singulars
      1. POVBA Excel Loop Multiple Observations Per Subject/Record
      1. USArup Rakshit
    2. COI skimmed the book you mentioned. Personally, I find you can learn VBA much easier (initially) by recording macros and attempting to modify them to suit your needs. Record a macro. Modify it to attempt to do what you want. If it doesn't work, share the code, and explain what it is _supposed_ to do. Usually, someone will tell you how to fix it. If you are unable to do that, make your problem sound more interesting and someone might write it for you.
      singulars
      1. POVBA Excel Loop Multiple Observations Per Subject/Record
      1. USDaniel
    3. COInput data and expected output can say a lot than writing a novel... Sorry but the truth... :)
      singulars
      1. POVBA Excel Loop Multiple Observations Per Subject/Record
      1. USbonCodigo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload