toothless200
10-10-2018, 11:08 AM
I have a working Excel Macro that I've cobbled together from sources I've found on the internet.
It does what I want: it pastes usernames from the clipboard into a worksheet ("Bump") compares the resulting list to a list of users one the master list worksheet ("Reddit"), and then copies any new user names into the master list worksheet ("Reddit").
However, when it finds a user with a number for their name (e.g., '843564485), it will never find that name on the master worksheet, and copies it over every time.
Also, if it finds no matches, it errors out.
I'd like it to display a 'no matches' message for a few second on a pop-up, and then auto-close the pop-up message after a few seconds.
Any help would be appreciated.
Thanks!
Sub SelectBumpFinal()
'
' SelectBumpFinal Macro
' Macro recorded 9/26/2018 by Laptop2
'
' Keyboard Shortcut: Ctrl+k
'
Sheets("Bump").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1:C1000").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Dim TheSheet As Worksheet
If TypeOf ActiveSheet Is Worksheet Then
Set TheSheet = ActiveSheet
Else
Exit Sub
End If
Dim Row As Integer
Dim CellsToSelect As String
For Row = 1 To TheSheet.Range("C" & CStr(TheSheet.Rows.Count)).End(xlUp).Row
If TheSheet.Range("C" & CStr(Row)).Value = "No Match" Then
If CellsToSelect <> "" Then CellsToSelect = CellsToSelect & ","
CellsToSelect = CellsToSelect & "B" & CStr(Row)
End If
Next Row
TheSheet.Range(CellsToSelect).Select
Selection.Copy
Sheets("Reddit").Activate
ActiveCell.SpecialCells(xlCellTypeLastCell).Select
Application.Goto Cells(ActiveCell.Row, 1), 0
ActiveCell.Offset(1).PasteSpecial xlPasteValues
End Sub
It does what I want: it pastes usernames from the clipboard into a worksheet ("Bump") compares the resulting list to a list of users one the master list worksheet ("Reddit"), and then copies any new user names into the master list worksheet ("Reddit").
However, when it finds a user with a number for their name (e.g., '843564485), it will never find that name on the master worksheet, and copies it over every time.
Also, if it finds no matches, it errors out.
I'd like it to display a 'no matches' message for a few second on a pop-up, and then auto-close the pop-up message after a few seconds.
Any help would be appreciated.
Thanks!
Sub SelectBumpFinal()
'
' SelectBumpFinal Macro
' Macro recorded 9/26/2018 by Laptop2
'
' Keyboard Shortcut: Ctrl+k
'
Sheets("Bump").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1:C1000").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Key2:=Range("B1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Dim TheSheet As Worksheet
If TypeOf ActiveSheet Is Worksheet Then
Set TheSheet = ActiveSheet
Else
Exit Sub
End If
Dim Row As Integer
Dim CellsToSelect As String
For Row = 1 To TheSheet.Range("C" & CStr(TheSheet.Rows.Count)).End(xlUp).Row
If TheSheet.Range("C" & CStr(Row)).Value = "No Match" Then
If CellsToSelect <> "" Then CellsToSelect = CellsToSelect & ","
CellsToSelect = CellsToSelect & "B" & CStr(Row)
End If
Next Row
TheSheet.Range(CellsToSelect).Select
Selection.Copy
Sheets("Reddit").Activate
ActiveCell.SpecialCells(xlCellTypeLastCell).Select
Application.Goto Cells(ActiveCell.Row, 1), 0
ActiveCell.Offset(1).PasteSpecial xlPasteValues
End Sub