Link to home
Start Free TrialLog in
Avatar of vijay vj
vijay vjFlag for India

asked on

vba to flip column

Dear sir , i have vba where its downloads data from web and copy data to another sheet , but i need data to be flipped in reverse order. (i.e) column A has values of 1 ,2 ,3 ,4 and need column A values to be flipped as 4,3,2,1 and so on.
 here is my vba code

Sub Go()
'
' Go Macro
' Macro recorded 29/03/2007 by pjPonzo

Dim i As Integer, N As Integer, j As Integer, sym As String

N = Range("A2")
j = 3
Clear
' get data for each symbol
For i = 1 To N
    sym = Cells(1, 2 + i)
'    sym = sym + "&selected=" + sym
    Cells(3, j) = sym
    Sheets("Webpage").Select
    Range("A2") = sym
    GetData
    Range("B8:J60").Select
    Selection.Copy
    Sheets("Data").Select
    Cells(5, j).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    j = j + 10
Next i

Range("B1").Select

End Sub

Open in new window


i have attached my excel sheet image

here webpage is the data download from web and data sheet where it copy that data from webpage to data sheet , i have attached code for copy data sheet.
2.jpg
1.jpg
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try
Sub Go()
'
' Go Macro
' Macro recorded 29/03/2007 by pjPonzo

Dim i As Integer, N As Integer, j As Integer, sym As String

N = Range("A2")
j = 3
Clear
' get data for each symbol
For i = 1 To N
    sym = Cells(1, 2 + i)
'    sym = sym + "&selected=" + sym
    Cells(3, j) = sym
    Sheets("Webpage").Select
    Range("A2") = sym
    GetData
    Range("B8:J60").Select
    Selection.Copy
    Sheets("Data").Select
    Cells(5, j).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Set Rng = Range(Cells(4, j), Cells(Cells(4, j).End(xlDown).Row, Cells(4, j).End(xlToRight).Column))
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Cells(4, j), SortOn:=xlSortOnValues, _
                Order:=xlDescending, DataOption:=xlSortTextAsNumbers
        .SetRange Rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       
    j = j + 10
Next i

Range("B1").Select

Open in new window

Regards
Avatar of vijay vj

ASKER

no change sir its copy as it is , not flipping
then try
Sub Go()
'
' Go Macro
' Macro recorded 29/03/2007 by pjPonzo

Dim i As Integer, N As Integer, j As Integer, sym As String

N = Range("A2")
j = 3
Clear
' get data for each symbol
For i = 1 To N
    sym = Cells(1, 2 + i)
'    sym = sym + "&selected=" + sym
    Cells(3, j) = sym
    Sheets("Webpage").Select
    Range("A2") = sym
    GetData
    Range("B8:J60").Select
    Selection.Copy
    Sheets("Data").Select
    Cells(5, j).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Set Rng = Range(Cells(4, j), Cells(Cells(4, j).End(xlDown).Row, Cells(4, j).End(xlToRight).Column))
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Cells(4, j), SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortTextAsNumbers
        .SetRange Rng
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
       
    j = j + 10
Next i

Range("B1").Select

Open in new window

No change sir, same as before
Could you send a dummy?
I have attached the excel file sir
Rakesh.xls
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks sir , its working perfectly
but its copying only half values sir....
Be more precise
then try
Sub Go()
'
' Go Macro
' Macro recorded 29/03/2007 by pjPonzo
Application.ScreenUpdating = False
Dim i As Integer, N As Integer, j As Integer, sym As String

N = Range("A2")
j = 3
Clear
' get data for each symbol
For i = 1 To N
    sym = Cells(1, 2 + i)
    Cells(3, j) = sym
    Sheets("Webpage").Select
    Range("A2") = sym
    GetData
    Idx1 = 0
    For Idx = WorksheetFunction.CountA(Range("B12:B" & Rows.Count)) To Range("B12").Row Step -1
        Sheets("Data").Cells(4, j).Offset(Idx1).Resize(1, 13).Value = Range("B12:N12").Offset(Idx - 1).Value
        Idx1 = Idx1 + 1
    Next

    Sheets("Data").Select
    j = j + 14
Next i

Range("B1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

Dear sir, i have attached similar excel file , i need code for this too, kindly check the error, its copy half values (i.e) exactly 30 counts values.
Rakesh.xls
then try
Sub Go()
'
' Go Macro
' Macro recorded 29/03/2007 by pjPonzo

Dim i As Integer, N As Integer, j As Integer, sym As String

N = Range("A2")
j = 3
Clear
' get data for each symbol

For i = 1 To N
    sym = Cells(1, 2 + i)
    Cells(3, j) = sym
    Sheets("Webpage").Select
    Range("A2") = sym
    GetData
    Idx1 = 0
    For Idx = WorksheetFunction.CountA(Range("B8:B" & Rows.Count)) To 1 Step -1
        Sheets("Data").Cells(4, j).Offset(Idx1).Resize(1, 10).Value = Range("B8:J8").Offset(Idx - 1).Value
        Idx1 = Idx1 + 1
    Next

    Sheets("Data").Select
    j = j + 14
Next i

Range("B1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

thanks sir, finally working well