Vba help!!

[lwaithe]

i have this code and it's suppose to take the cities i listed in column 2 and move it to column 3 in reverse order.

so instead of it saying:

Houston

Galveston

Beaumont

Dallas

El Paso

San Antonio

Midland

Odessa

Austin

Ft. Worth its suppose to say: Ft. Worth Austin Odessa Midland San Antonio El Paso Dallas Beaumont Galveston Houston but for some reason the code does not work. I'm using Excel Visual Basics for Mac

<tbody>
</tbody>

Option Explicit


Sub final()
Dim cities(10) As String
Dim i, N As Integer

N = Cells(1, 1)
For i = 1 To N
cities(i) = Cells(i, 2)
Next i


For i = N To 1 Step -1
Cells(i, 3) = cities(i)
Next i


End Sub

 

[Rick Rothstein]

You could do what you want with just formula. Assuming no blank cells within your data, put this formula in cell C1 and copy it down to the end of your data...

=INDEX(B$1:B$10,1+COUNTA(B:B)-ROW(A1))

 

[perimidt]

It is difficult to say without the spreadsheet, but it looks like the N never gets a values different from zero, of course if you have a value i A1 then it does.

Best regards
Per Erik

 

[Rick Rothstein]

You could do what you want with just formula. Assuming no blank cells within your data, put this formula in cell C1 and copy it down to the end of your data...

=INDEX(B$1:B$10,1+COUNTA(B:B)-ROW(A1))

However, if you really need that as VBA code...

Sub ReverseList()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  With Range("C1:C" & LastRow)
    .Formula = "=INDEX(B$1:B$" & LastRow & ",1+COUNTA(B:B)-ROW(A1))"
    .Value = .Value
  End With
End Sub

 

[mirabeau]

to get your own code working, and to help you see why it wasn't working, note the modification in red

Sub final()
Dim cities(10) As String
Dim i, N As Integer

N = Cells(1, 1)
For i = 1 To N
cities(i) = Cells(i, 2)
Next i

For i = N To 1 Step -1
Cells(i, 3) = cities(N + 1 - i)
Next i

End Sub

for a somewhat similar approach, which may be useful particularly if you have lots of cities (say a few thousand or even a million) then you could look at

Sub reverse()
Dim n, a, i
N = Cells(Rows.Count, "b").End(3).Row 'or N = Cells(1,1) if you want it that way
a = Cells(1, "b").Resize(N, 2)

For i = 1 To N
    a(i, 2) = a(N + 1 - i, 1)
Next i

Cells(1, "b").Resize(N, 2) = a

End Sub

If you also want to retain your cities' formatting (say their cell color, font size or color etc.) after the reveral there's a couple of other approaches ..