Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Become a Premium Member and unlock a new, free course in leading technologies each month.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Sub CheckLCase()
Dim cell As Range
Dim strSurname As String
Dim blnPassed As Boolean
For Each cell In Range([a2], [a65536].End(xlUp))
blnPassed = True 'Assuming we find no problems, name is okay
'Need to first get the surname
strSurname = Left(cell.Value, InStr(1, cell.Value, ",") - 1)
'I don't know the exact rules so it is hard to say what I am looking for. I will assume 'Mc' and 'Mac' are the only places this applies
'We are checking that the letter following 'Mac' or Mc' is upper case
If InStr(1, strSurname, "mc", vbTextCompare) Then
If StrComp(LCase(Mid(strSurname, 3, 1)), Mid(strSurname, 3, 1)) = 0 Then blnPassed = False
End If
If InStr(1, strSurname, "mac", vbTextCompare) Then
If StrComp(LCase(Mid(strSurname, 4, 1)), Mid(strSurname, 4, 1)) = 0 Then blnPassed = False
End If
If blnPassed = True Then
cell.Offset(0, 2).Value = True
Else
cell.Offset(0, 2).Value = False
End If
Next cell
End Sub
Sub corrector()
Dim rng As Range
Dim celle As Range
Dim tempstr() As String
Dim str1 As String
Dim str2 As String
Dim rowe As Long
Dim location As Long
Dim i As Long
rowe = 1
str1 = "A"
str2 = "A"
With Sheets("Sheet1")
Set rng = Range(.Cells(rowe, str1), .Cells(.Cells.Rows.Count, str2).End(xlUp))
End With
For Each celle In rng
tempstr() = Split(celle, ",")
If InStr(1, LCase(tempstr(0)), "le ", 1) <> 1 And _
InStr(1, LCase(tempstr(0)), "de ", 1) <> 1 And _
InStr(1, LCase(tempstr(0)), "van ", 1) <> 1 And _
InStr(1, LCase(tempstr(0)), "von ", 1) <> 1 Then
While InStr(1, tempstr(0), " ", 1) > 0
location = InStr(1, tempstr(0), " ", 1)
tempstr(0) = Left(tempstr(0), location - 1) & Mid(tempstr(0), location + 1, Len(tempstr(0)))
celle = tempstr(0) & "," & tempstr(1)
Wend
End If
If InStr(1, LCase(celle), "mc", 1) > 0 Then
celle = "Mc" & UCase(Mid(celle, 3, 1)) & Mid(celle, 4, Len(celle))
End If
If InStr(1, LCase(celle), "mac", 1) > 0 Then
celle = "Mac" & UCase(Mid(celle, 4, 1)) & Mid(celle, 5, Len(celle))
End If
tempstr() = Split(celle, ",")
If InStr(1, tempstr(0), ".", 1) > 0 Then
location = InStr(1, tempstr(0), ".", 1)
tempstr(0) = Left(tempstr(0), location) & UCase(Mid(tempstr(0), location + 1, 1)) _
& Mid(tempstr(0), location + 2, Len(tempstr(0)))
celle = tempstr(0) & "," & tempstr(1)
End If
tempstr() = Split(celle, ",")
If InStr(1, tempstr(0), "-", 1) > 0 Then
location = InStr(1, tempstr(0), "-", 1)
tempstr(0) = Left(tempstr(0), location) & UCase(Mid(tempstr(0), location + 1, 1)) _
& Mid(tempstr(0), location + 2, Len(tempstr(0)))
celle = tempstr(0) & "," & tempstr(1)
End If
For i = 1 To Len(celle)
Select Case Asc(Mid(celle, i, 1))
Case 192 To 198
celle = "A" & Mid(celle, i + 1, Len(celle))
Case 199
celle = "C" & Mid(celle, i + 1, Len(celle))
Case 200 To 203
celle = "E" & Mid(celle, i + 1, Len(celle))
Case 232 To 235
celle = Left(celle, i - 1) & "e" & Mid(celle, i + 1, Len(celle))
End Select
Next i
Next celle
End Sub
budorat-01.xls
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
if statement with a sum of range cells | 10 | 36 | |
Error message for incorrect or missing hyperlink | 3 | 27 | |
Modifying VBA to exclude a single row of data when extracted to another workbook | 7 | 18 | |
Query Task | 8 | 23 |
Join the community of 500,000 technology professionals and ask your questions.