Godders199
Active Member
- Joined
- Mar 2, 2017
- Messages
- 313
- Office Version
- 2013
Hello, i have a report of 50 plus columns which i use, I have set up to transpose the information i require into a new worksheet, sample of code is below. my question is the data on the main report comes in date order ( submission date) no filtering or sorting has been done, however when i transpose the data i require , the date order is lost. I have spot checked random rows and all the data has transposed correctly, just not in date order.
Is it purely how excel runs the code or is there something i need to do to keep the date order?
QAgentcode = Sheets("info").Range("z11")
Qadvtype = Sheets("info").Range("z10")
QKRN = Sheets("info").Range("z12")
Qsubmissiondate = Sheets("info").Range("z13")
Sheets("submissions").Select
r = 2
t = 2
Do Until Range("A" & r).Value = ""
If Sheets("submissions").Range(Qadvtype & r).Value = "OTHER" Or Sheets("submissions").Range(QRiskRule1 & r).Value = "PIMs Review" Then
Else
If Sheets("submissions").Range(Qallocatedto & r).Value = "" Or Sheets("submissions").Range(Qallocatedto & r).Value = "" <> "" Then
Sheets("Adviser data").Range("A" & t).Value = Sheets("submissions").Range(QAgentcode & r).Value 'Adviser code
Sheets("Adviser data").Range("b" & t).Value = Sheets("submissions").Range(Qadvtype & r).Value 'Adviser type'
Sheets("Adviser data").Range("c" & t).Value = Sheets("submissions").Range(QKRN & r).Value 'KRN'
Sheets("Adviser data").Range("d" & t).Value = Sheets("submissions").Range(Qsubmissiondate & r).Value 'submission date'
t = t + 1
Else
End If
End If
r = r + 1
Loop
Is it purely how excel runs the code or is there something i need to do to keep the date order?
QAgentcode = Sheets("info").Range("z11")
Qadvtype = Sheets("info").Range("z10")
QKRN = Sheets("info").Range("z12")
Qsubmissiondate = Sheets("info").Range("z13")
Sheets("submissions").Select
r = 2
t = 2
Do Until Range("A" & r).Value = ""
If Sheets("submissions").Range(Qadvtype & r).Value = "OTHER" Or Sheets("submissions").Range(QRiskRule1 & r).Value = "PIMs Review" Then
Else
If Sheets("submissions").Range(Qallocatedto & r).Value = "" Or Sheets("submissions").Range(Qallocatedto & r).Value = "" <> "" Then
Sheets("Adviser data").Range("A" & t).Value = Sheets("submissions").Range(QAgentcode & r).Value 'Adviser code
Sheets("Adviser data").Range("b" & t).Value = Sheets("submissions").Range(Qadvtype & r).Value 'Adviser type'
Sheets("Adviser data").Range("c" & t).Value = Sheets("submissions").Range(QKRN & r).Value 'KRN'
Sheets("Adviser data").Range("d" & t).Value = Sheets("submissions").Range(Qsubmissiondate & r).Value 'submission date'
t = t + 1
Else
End If
End If
r = r + 1
Loop