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
72 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
[X]
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
  • 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 31

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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 31

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 31

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 31

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 31

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

734 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