Hi,
I have a macro that is meant to format all the cells in order to make the data fit into the the database however when I'm trying to insert chr(32) Nothing happens.
If anyone could show me what's wrong with this code I'd much appreciate it.
Code
Sub format()
'
' Macro1 Macro
'-
Dim x As String
'slect sheets
Sheets("Sheet1").Select
'selects sheets
Range("A1").Select
b = -1
Do Until activecell = "" Or Null
activecell.Offset(1, 0).Select
b = b + 1
Loop
activecell.Offset(-b - 1, 0).Select
'starts and defines end of loop
Do Until activecell = "" Or Null
'determines calue of x
x = LCase(activecell.Value)
'selects case
Select Case x
'selects the specific case
Case "name"
'ActiveCell.Offset(1, 0).Select
'defines value of b
Sheets("Sheet1").Select
Case "firstname", "first name"
Dim myRange As Range
Range(Selection, Selection.End(xlDown)).Select
For Each cell In Selection
cell.Value = WorksheetFunction.Proper(cell.Value)
Next cell
activecell.Offset(1, 0).Select
activecell.Offset(-1, 0).Select
Sheets("Sheet1").Select
'defines cases name
Case "lastname", "last name"
Dim myRange1d As Range
Range(Selection, Selection.End(xlDown)).Select
For Each cell In Selection
cell.Value = WorksheetFunction.Proper(cell.Value)
Next cell
activecell.Offset(1, 0).Select
activecell.Offset(-1, 0).Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("Sheet1").Select
Case "address line 1", "address line 1"
c = 0
Do Until c = b
'defines value of x
c = 1
'repeats until the next cell to the ight doesn't start with "address line"
Do Until Left(activecell.Value, 12) <> "address line"
If activecell.Value = "address line " & c And activecell.Offset(0, 1).Value = "address line " & d + 1 Then
c = c + 1
activecell.Offset(0, 1).Select
Else
activecell.Offset(0, 1).Select
End If
On Error Resume Next
Loop
Do Until c = b
y = 1
activecell.Offset(1, 0).Select
Do Until c = y
'adds value of next cell into previous cell
activecell.Value = activecell.Value & " " & activecell.Offset(0, y).Value
y = y + 1
Loop
c = c + 1
Loop
Loop
'activecell.Offset(-b, 0).Select
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''Start of problem code''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets("Sheet1").Select
Case "Postcode", "postcode"
c = 0
Do Until c = b
activecell.Offset(1, 0).Select
If Len(Postcode1) = 7 Then
activecell.Value = Left(Postcode1, 4) & " " & Right(Postcode1, 3)
Else
If Len(Postcode1) = 6 Then
activecell.Value = Left(posctode1, 3) & " " & Right(Postcode1, 3)
Else
'remove record to sperate sheet becasue bad postcode length
End If
End If
c = c + 1
Loop
activecell.Offset(-b, 0).Select
''''''''''''''''''''''''''''''''''''''''''''''''''''''''End of problem code'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Case "telephone", "Telephone"
c = 0
Do Until c = b
Dim Telephone As String
Do Until c = b
activecell.Offset(1, 0).Select
If Left(Telephone, 1) = "0" Then
If Len(Telephone) = 11 Then
'skip to next
Else
'remove to other tables
End If
Else
'this fommats the number to have a 0 at the front
activecell.NumberFormat = "0##########"
activecell.Value = 0 & activecell.Value
End If
c = c + 1
Loop
Loop
End Select
activecell.Offset(0, 1).Select
Loop
End Sub
Display More
Please help Ozgrid.com you're my only hope.