dansmith88
08-16-2012, 03:24 AM
Hi all,
I was wondering if anyone could suggest why my array was not working. The purpose of it is to search through the town colums for the states in the array, if it then finds one, it assigns it to a temporary variable and swaps with the corresponding cell in the state column. It probably is a basic syntax error but any help would be hugely appreciated.
Thanks
PS an example of the table can be seen below.
Dim x As Long
Dim tName As String
Dim sName As String
Dim sCol As Integer
Dim tCol As Integer
x = 2
tCol = 4
sCol = 5
Dim states(11) As String
states(1) = "Texas"
states(2) = "Louisianna"
states(3) = "Mississippi"
states(4) = "Alabama"
states(5) = "Florida"
states(6) = "Georgia"
states(7) = "South Carolina"
states(8) = "North Carolina"
states(9) = "Arkansas"
states(10) = "Virginia"
states(11) = "Tennessee"
For i = 1 To 5000
If Cells(x, tCol) = states(11) Then
tName = Cells(x, tCol).Value
sName = Cells(x, sCol).Value
Cells(x, tCol).Value = sName
Cells(x, sCol).Value = tName
End If
x = x + 1
Next i
End Sub
First NameLast NameAgeHome TownHome StateFavourite FishSustainably SourcedFish Consumed p/mOpinion on SushiErickAltenwerth77amariburghMISSISSIPPIhalibutno16Never tried itmajorBotsford75hayeshavenNORTH CAROLINAplaiceyes14Never tried itdemondSteuber35east isabellvilleGeorgiaSalmonyes18What is sushi?DARIANAstreich34LESCHPORTTennesseePlaiceyes20What is sushi?
I was wondering if anyone could suggest why my array was not working. The purpose of it is to search through the town colums for the states in the array, if it then finds one, it assigns it to a temporary variable and swaps with the corresponding cell in the state column. It probably is a basic syntax error but any help would be hugely appreciated.
Thanks
PS an example of the table can be seen below.
Dim x As Long
Dim tName As String
Dim sName As String
Dim sCol As Integer
Dim tCol As Integer
x = 2
tCol = 4
sCol = 5
Dim states(11) As String
states(1) = "Texas"
states(2) = "Louisianna"
states(3) = "Mississippi"
states(4) = "Alabama"
states(5) = "Florida"
states(6) = "Georgia"
states(7) = "South Carolina"
states(8) = "North Carolina"
states(9) = "Arkansas"
states(10) = "Virginia"
states(11) = "Tennessee"
For i = 1 To 5000
If Cells(x, tCol) = states(11) Then
tName = Cells(x, tCol).Value
sName = Cells(x, sCol).Value
Cells(x, tCol).Value = sName
Cells(x, sCol).Value = tName
End If
x = x + 1
Next i
End Sub
First NameLast NameAgeHome TownHome StateFavourite FishSustainably SourcedFish Consumed p/mOpinion on SushiErickAltenwerth77amariburghMISSISSIPPIhalibutno16Never tried itmajorBotsford75hayeshavenNORTH CAROLINAplaiceyes14Never tried itdemondSteuber35east isabellvilleGeorgiaSalmonyes18What is sushi?DARIANAstreich34LESCHPORTTennesseePlaiceyes20What is sushi?