This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub AddRemoveHyperLinks() | |
Dim prv As String | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
For Each Cell In ActiveSheet.Range("B2:B" & ActiveSheet.UsedRange.Rows.Count) | |
If Cell.Hyperlinks.Count = 0 Then |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub EncloseinQuotes() | |
Dim Cell As Range | |
Dim IP As String | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, March 2017 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
With ActiveSheet | |
For Each Cell In Range("A2:A" & ActiveSheet.UsedRange.Rows.Count) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub InsertSpacesBetweenCharactersCase() | |
Dim mStr As String | |
Dim i As Integer | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, March 2017 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
For Each Cell In ActiveSheet.Range("G3:G43") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub FindFirstUpperCharacter() | |
Dim mStr As String | |
Dim FindUpper As String | |
Dim FindLower As String | |
Dim i As Integer | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'assumes the string has only two Upper characters that need separating by blank space; Angelina Teneva, March 2017 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ColourWordsInString() | |
Dim Cell As Range | |
Dim i As Integer | |
Dim prv As String | |
Dim word As String | |
Dim positive(1 To 5) As String | |
Dim negative(1 To 5) As String | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ColourCellsonAbsoluteValues() | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
For Each Cell In Selection | |
Cell.Activate | |
If Cell.Value < 0 Then |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub ColourCellsin_DifferentColumn() | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
For Each Cell In Range("F5:F" & ActiveSheet.UsedRange.Rows.Count) | |
Cell.Activate | |
Range(ActiveCell.Offset(0, -5), ActiveCell.Offset(0, 4)).Font.Color = RGB(0, 0, 0) 'black (reset any previous formatting) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub FillInEmptyCellWithConstant() | |
Dim i As Long | |
Dim Col As Integer | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
For Col = 7 To 68 | |
For i = 1 To Cells(Rows.Count, Col).End(xlUp).row | |
If IsEmpty(Cells(i, Col)) Then Cells(i, Col).Value = "empty" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub FillInEmptyCellWithPrevious() | |
Dim Cell As Range | |
Dim Region As Range | |
Set Region = Worksheets("Extract").Range(Cells(3, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 3, 3)) | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
For Each Cell In Region |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub FillInEmptyCellWithAdjacent() | |
Dim Cell As Range | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'written by Angelina Teneva, September 2016 | |
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | |
'copy to column | |
For Each Cell In ActiveSheet.Range("AB2:AB" & ActiveSheet.UsedRange.Rows.Count) | |
Cell.Activate |
OlderNewer