Avatar of vijay vj
vijay vj
Flag 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
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
vijay vj

8/22/2022 - Mon
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
vijay vj

ASKER
no change sir its copy as it is , not flipping
Rgonzo1971

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
vijay vj

ASKER
No change sir, same as before
Rgonzo1971

Could you send a dummy?
vijay vj

ASKER
I have attached the excel file sir
Rakesh.xls
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
vijay vj

ASKER
thanks sir , its working perfectly
vijay vj

ASKER
but its copying only half values sir....
Rgonzo1971

Be more precise
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rgonzo1971

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

vijay vj

ASKER
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
Rgonzo1971

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
vijay vj

ASKER
thanks sir, finally working well