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
48 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 28

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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 28

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 28

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 28

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 28

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now