Data Normalization & Windows 8

Well yesterday morning I believe I was the first downloadee of the publicly released Windows 8 Dev Preview to get it set up on the Asus EP121. Details and video here.

Last night's 'grit' stemmed from a task to import 600+ entries from a hardcopy member directory by hand into MailChimp.

Fortunately, I found the data online- however, it was presented in a very inaccessible fashion... (one big list, impossible to upload directly).

I set out to normalize the data autonomously with a VBA script... and copied it below. It's been years since I've used VBA, so apologies for the inefficiencies.

If anyone has another way to do this, I'd be very interested.

Sub NormalizeData()

Sheets("Sheet1").Select
Dim ws As Worksheet, iRow As Long, iCol As Long, i As Long, curAdd As Integer, curRow As Integer
Dim isStart As Boolean
isStart = True
curRow = 2
curAdd = 6
iRow = 1

For iRow = 1 To 7886

'first data is company name after view more info
'run if statement- if email then put in email go to next, if memb. type, phone, then paste
'if view then go to next and add one to row number
'if none of the above add it to address, do 3 times
'skip empty cells
'dont paste address data after the 5th data row for each entry/company

If Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value = "" Then

GoTo endWith2
Else

End If


If isStart = True Then
curAdd = 6
activeCustomerCellNum = 1
If Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value = "" Then GoTo endWith2
Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Copy
Sheets("Sheet1").Range("A1").Offset(curRow - 1, 0).PasteSpecial
Application.CutCopyMode = False
isStart = False
GoTo endWith
Else
End If

If InStr(1, Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value, "More Info") > 0 Then
isStart = True
curRow = curRow + 1
curAdd = 6
GoTo endWith
Else
End If

If InStr(1, Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value, "Key Staff") > 0 Then
curAdd = 6
GoTo endWith
Else
End If

If InStr(1, Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value, "E-mail") > 0 Then
Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Copy
ThisWorkbook.Worksheets(1).Activate
ThisWorkbook.Worksheets(1).Cells(curRow, 2).PasteSpecial
Application.CutCopyMode = False
curAdd = 6
GoTo endWith
Else
End If
If InStr(1, Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value, "Phone") > 0 Then
Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Copy
ThisWorkbook.Worksheets(1).Activate
ThisWorkbook.Worksheets(1).Cells(curRow, 3).PasteSpecial
Application.CutCopyMode = False
curAdd = 6
GoTo endWith
Else
End If
If InStr(1, Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Value, "Member Type") > 0 Then
Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Copy
ThisWorkbook.Worksheets(1).Activate
ThisWorkbook.Worksheets(1).Cells(curRow, 5).PasteSpecial
Application.CutCopyMode = False
curAdd = 6
GoTo endWith
Else
End If

' .Value.Copy
If activeCustomerCellNum < 5 Then
Sheets("Sheet2").Range("A1").Offset(0 + iRow, 0).Copy
ThisWorkbook.Worksheets(1).Activate
ThisWorkbook.Worksheets(1).Cells(curRow, curAdd).PasteSpecial
Application.CutCopyMode = False
curAdd = curAdd + 1


GoTo endWith
Else
End If
curAdd = 6
'.Value = .Value & ", " & curState
endWith:
activeCustomerCellNum = activeCustomerCellNum + 1
endWith2:

Next

End Sub