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
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
65 Views
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
``````
0
Question by:Dritan Nikolla
• 6
• 5
• 2
• +1

LVL 45

Expert Comment

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

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

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
``````
0

Author Comment

ID: 41779998

I am getting an error when arrive at this point?
0

Author Comment

ID: 41780004
0

LVL 1

Expert Comment

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
``````
1

LVL 45

Expert Comment

ID: 41780026
@Dritan

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

LVL 30

Expert Comment

ID: 41780076
``````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
``````

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

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
``````
...this worked fine :). Somehow you are copying everything at once, which looks like magic :).
0

Author Comment

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

ID: 41780209
Okay. That means rawData is the code name of the sheet.
You correctly made the required change.

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

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

Author Closing Comment

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

ID: 41780237
You're welcome again Dritan! :)
0

Featured Post

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â€¦