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

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

Dritan NikollaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
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
 
aikimarkCommented:
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
 
Dritan NikollaAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Dritan NikollaAuthor Commented:
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
 
Dritan NikollaAuthor Commented:
Screen-Shot-2016-09-01-at-16.04.19.png
0
 
Andrew WhiteCommented:
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
 
aikimarkCommented:
@Dritan

It would help if you posted a representative sample workbook with before and after worksheets.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Dritan NikollaAuthor Commented:
@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
 
Dritan NikollaAuthor Commented:
You guys are amazing :). Its also amasing how you provide this much help for free, thank you soo much, god bless you :).
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Also you are requested to close both of your questions by accepting the solution. :)
0
 
Dritan NikollaAuthor Commented:
With many many many thanks to all of you who have helped me - you are absolute angels :). Thank you
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome again Dritan! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.