Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

copying a column in one sheet to a row in another sheet with nested do loops in if statements.

Posted on 2016-09-01
16
65 Views
Last Modified: 2016-09-01
Can I ask a question please? I am now copying that column into a row in a different sheet. I have the following code, see below. But it runs only the first If statement. Once the loop exits from the first if statement, I want it to go to the next if statement, but it exits. I dont' know why!

Sub testmethod()
    
    Dim count1 As Long
    Dim count2 As Long
    Dim count3 As Long
    Dim count4 As Long
    Dim count5 As Long
    Dim count6 As Long
    Dim myValue As Long
    
    count1 = 2
    count2 = 14
    count3 = 2
    count4 = 1
    count5 = 2
    count6 = 2
    
    rawData.Activate ' activating the relevant worksheet
    Range("A2").Select ' selecting the reference point
                               
    If ActiveCell.Value = "106" Then ' these are codes, different number refers to different person
               
        Do
            myValue = ActiveCell.Offset(0, 13).Value ' assign the value 13 cells to the right of the active cell to a long variable
            myData.Activate                                     ' the next sheet where I want to paste the data
            Cells(count5, count6).Value = myValue   ' pasting the data in the relevant cell
            count6 = count6 + 1                             'moving one column to the right on the same row
            rawData.Activate                                    'back to the worksheet to copy the data
            ActiveCell.Offset(1, 0).Select                    ' move one row down
            
        Loop While ActiveCell.Value = "106"                 'loop while that condition holds
                        
    ElseIf ActiveCell.Value = "108" Then
    
        count5 = count5 + 1 ' moves the counter so that in the copying to sheet i start copying one row down for the next participant.
        Do
            myValue = ActiveCell.Offset(0, 13).Value
            myData.Activate
            Cells(count5, count6).Value = myValue
            count6 = count6 + 1
            rawData.Activate
            ActiveCell.Offset(1, 0).Select
        Loop While ActiveCell.Value = "108"
                        
      End If
                                            
End Sub

Open in new window

0
Comment
Question by:Dritan Nikolla
  • 6
  • 5
  • 2
  • +1
16 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41779970
Your looping is inside your If/elseif structure.  If you need to iterate across cells, you will need to place the if/elseif structure inside a looping structure.
0
 

Author Comment

by:Dritan Nikolla
ID: 41779982
Hi aikimark. Thank you for your answer. But that would not fix my problem. I am trying to copy the cells 13 rows adjacent to the A2 cell, to another row as long as the value in A2 cells remains the same/ when the value changes, I need to start a new row from the start. So, basically, converting a column (vector), into a table (matrix). The way I have it works logically. Is there a different way I could use?
0
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41779987
What you are trying to achieve, can be done by the following code. See if you get the desired output.
Sub CopyData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long
Application.ScreenUpdating = False
Set sws = Sheets("rawData")
Set dws = Sheets("myData")
slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
With sws.Range("A1:A" & slr)
   .AutoFilter field:=1, Criteria1:=106
   If sws.Range("A1:A" & slr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      sws.Range("N2:N" & slr).Copy
      dws.Range("B2").PasteSpecial xlPasteAll, Transpose:=True
   End If
   .AutoFilter field:=1, Criteria1:=108
   If sws.Range("A1:A" & slr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
      sws.Range("N2:N" & slr).Copy
      dws.Range("B3").PasteSpecial xlPasteAll, Transpose:=True
   End If
   .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Open in new window

0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Dritan Nikolla
ID: 41779998
Screen-Shot-2016-09-01-at-16.00.30.pngScreen-Shot-2016-09-01-at-16.00.40.png
I am getting an error when arrive at this point?
0
 

Author Comment

by:Dritan Nikolla
ID: 41780004
Screen-Shot-2016-09-01-at-16.04.19.png
0
 
LVL 1

Expert Comment

by:Andrew White
ID: 41780006
Try this:
Sub testmethod()
    
     Dim count1 As Long
    Dim count2 As Long
    Dim count3 As Long
    Dim count4 As Long
    Dim count5 As Long
    Dim count6 As Long
    Dim myValue As Long
    Dim rnge As Range
    Dim rcell As Range
    
    count1 = 2
    count2 = 14
    count3 = 2
    count4 = 1
    count5 = 2
    count6 = 2
    Set rnge = Range("myRange")
    Sheets("rawData").Activate ' activating the relevant worksheet
    Range("A2").Select ' selecting the reference point
                    For Each rcell In rnge
                     Select Case ActiveCell.Value
                    Case "106"
                    'these are codes, different number refers to different person
                               
                                        Do While ActiveCell.Value = "106"
                                                myValue = ActiveCell.Offset(0, 13).Value ' assign the value 13 cells to the right of the active cell to a long variable
                                                Sheets("myData").Activate                                     ' the next sheet where I want to paste the data
                                                Cells(count5, count6).Value = myValue   ' pasting the data in the relevant cell
                                                count6 = count6 + 1                             'moving one column to the right on the same row
                                                Sheets("rawData").Activate                                    'back to the worksheet to copy the data
                                                ActiveCell.Offset(1, 0).Select                    ' move one row down
                                            
                                        Loop                  'loop while that condition holds
                            
                                        
                                        
                   Case "108"
                    
                                                count5 = count5 + 1 ' moves the counter so that in the copying to sheet i start copying one row down for the next participant.
                                        Do While ActiveCell.Value = "108"
                                                myValue = ActiveCell.Offset(0, 13).Value
                                                Sheets("myData").Activate
                                                Cells(count5, count6).Value = myValue
                                                count6 = count6 + 1
                                                Sheets("rawData").Activate
                                                ActiveCell.Offset(1, 0).Select
                                        Loop
                                        
                      End Select
                      Next rcell
    
    
End Sub

Open in new window

1
 
LVL 45

Expert Comment

by:aikimark
ID: 41780026
@Dritan

It would help if you posted a representative sample workbook with before and after worksheets.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41780076
Please look at the following lines with comments.....
Set sws = Sheets("rawData")    'Source Data Sheet where data is being copied from
Set dws = Sheets("myData")     'Destination Data Sheet where data is being pasted

Open in new window


The error you get because the sheet rawData might not be present in the workbook.
So make sure that the sheets rawData and myData are present in the workbooks.
And if the sheet names are different from above, please change them as required.
0
 

Author Comment

by:Dritan Nikolla
ID: 41780199
@Tiwari

Hi sir Tiwari,

I found out why it didn't work. The rawData is not the name of the sheet in the user tab which would be refered to as you did Sheets("rawData"), correctly. The rawData is the name of the sheet in the properties window, so I fixed how that worked. simply,
Set sws = rawData

Open in new window

...this worked fine :). Somehow you are copying everything at once, which looks like magic :).
0
 

Author Comment

by:Dritan Nikolla
ID: 41780201
You guys are amazing :). Its also amasing how you provide this much help for free, thank you soo much, god bless you :).
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41780209
Okay. That means rawData is the code name of the sheet.
You correctly made the required change.

You're welcome. Glad to help.
Most of us do the paid jobs, but love to help here whenever we get some free time.
And thanks for the feedback and blessings. :)
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41780215
Also you are requested to close both of your questions by accepting the solution. :)
0
 

Author Closing Comment

by:Dritan Nikolla
ID: 41780235
With many many many thanks to all of you who have helped me - you are absolute angels :). Thank you
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41780237
You're welcome again Dritan! :)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question