Smashing Two Things Together

[alexfwalker81]

I have two great bits of code, which work well in Excel and Access.

This works well in Excel to identify gaps and close them up for me. Thank you @Gasman

Sub Tidy_Address()
Dim iLoop1 As Integer, iLoop2 As Integer, iLastRow As Integer
iLastRow = ActiveSheet.UsedRange.Rows.Count
Range("B1").Select
While ActiveCell.Row <= iLastRow
    iLoop = 1
    While iLoop < 4
' MsgBox (ActiveCell.Offset(0, iLoop).Value)
    
        If IsEmpty(ActiveCell.Offset(0, iLoop)) Then
            For iLoop2 = iLoop To 3
            If Not IsEmpty(ActiveCell.Offset(0, iLoop2 + 1).Value) Then
                ActiveCell.Offset(0, iLoop).Value = ActiveCell.Offset(0, iLoop2 + 1).Value
                ActiveCell.Offset(0, iLoop2 + 1).Value = ""
                Exit For
            End If
            Next iLoop2
        End If
    
    iLoop = iLoop + 1
    Wend
ActiveCell.Offset(1, 0).Activate
Wend

Range("A1").Select

End Sub

This works well to identify non Ascii in Access. Thank you to a colleague of mine.

Public Function RemoveNonASCII(str As String) As String
    Dim i As Integer
    For i = 1 To Len(str)
        If AscW(Mid(str, i, 1)) < 127 Then
            RemoveNonASCII = RemoveNonASCII & Mid(str, i, 1)
        End If
    Next i
End Function

Is there a way that I can take the existing code which identifies gaps in Excel, and insert the actions of the Access code, so that I have a subroutine in Excel which loops through all of my data and removes all the non Ascii characters? I've tried what I thought was obvious and failed!

 

[Gasman]

I would probably run another loop right before moving down a row.
The loop would go through all the columns .offset(0,iLoopCol) and pass the cell value to your function and set that cell value ?

 

[alexfwalker81]

I would probably run another loop right before moving down a row.
The loop would go through all the columns .offset(0,iLoopCol) and pass the cell value to your function and set that cell value ?Makes

Makes logical sense, I just don't know how to do it!

 

[Gasman]

You know how many columns you need to check?, I'll guess for 6
Dim iLoopCol as Integer at the top of the code where the rest are.

    Wend
For iLoopCol = 0 to 5
    If Not IsEmpty(ActiveCell.Offset(0,iLoopCol).Value) Then
        ActiveCell.Offset(0,iLoopCol).Value = RemoveNonASCII(ActiveCell.Offset(0,iLoopCol).Value)
    End If
Next
ActiveCell.Offset(1, 0).Activate
Wend

 

[alexfwalker81]

You know how many columns you need to check?, I'll guess for 6
Dim iLoopCol as Integer at the top of the code where the rest are.

    Wend
For iLoopCol = 0 to 5
    If Not IsEmpty(ActiveCell.Offset(0,iLoopCol).Value) Then
        ActiveCell.Offset(0,iLoopCol).Value = RemoveNonASCII(ActiveCell.Offset(0,iLoopCol).Value)
    End If
Next
ActiveCell.Offset(1, 0).Activate
Wend

Like a charm - thank you!

 

[harpygaggle]

I have two great bits of code, which work well in Excel and Access.

This works well in Excel to identify gaps and close them up for me. Thank you @Gasman

Sub Tidy_Address()
Dim iLoop1 As Integer, iLoop2 As Integer, iLastRow As Integer
iLastRow = ActiveSheet.UsedRange.Rows.Count
Range("B1").Select
While ActiveCell.Row <= iLastRow
    iLoop = 1
    While iLoop < 4
' MsgBox (ActiveCell.Offset(0, iLoop).Value)
   
        If IsEmpty(ActiveCell.Offset(0, iLoop)) Then
            For iLoop2 = iLoop To 3
            If Not IsEmpty(ActiveCell.Offset(0, iLoop2 + 1).Value) Then
                ActiveCell.Offset(0, iLoop).Value = ActiveCell.Offset(0, iLoop2 + 1).Value
                ActiveCell.Offset(0, iLoop2 + 1).Value = ""
                Exit For
            End If
            Next iLoop2
        End If
   
    iLoop = iLoop + 1
    Wend
ActiveCell.Offset(1, 0).Activate
Wend

Range("A1").Select

End Sub

This works well to identify non Ascii in Access. Thank you to a colleague of mine.

Public Function RemoveNonASCII(str As String) As String
    Dim i As Integer
    For i = 1 To Len(str)
        If AscW(Mid(str, i, 1)) < 127 Then
            RemoveNonASCII = RemoveNonASCII & Mid(str, i, 1)
        End If
    Next i
End Function

Is there a way that I can take the existing code which identifies gaps in Excel, and insert the actions of the Access code, so that I have a subroutine in Excel which loops through all of my data and removes all the non Ascii characters? I've tried what I thought was obvious and failed!

Thank you for sharing! This have been helpful