Macro help for excel

[IKHAN]

Need Help to creating macros


I have to COPY/paste items in column A of sheet 1 (primary sheet) recieved daily


Trying to acheive the following using macros


-To check and Delete column A(PASTED ITEMS) SHEET 1 (anything after "-" example - "Apple-bad" to show as "Apple")

-To check and delete duplicates from column A sheet 1 of pasted items(example:If item Apple exist in both Column A: Row1 and ColumnA : Row6 TO DELETE 1


item)

-To check in sheet 2 and if any match in column A of (sheet 2),Put it as "MATCHED" in column C of sheet 1

- When items copied/pasted in column A of sheet 1 ,To check/compare in sheet 3 predefined items in column F ,column J and COLUMN S and copy matched items in


Column E, column F and Column G respectively in SHEET 1

-Next delete complete row from column G from sheet 1 which says "EOL"

-Highlight SHEET 1 and Sort column F of sheet 1 in ascending(A-Z)

-Highlight sheet to ARIAL 11

Finally to compare data from sheet 4 column A when data is pasted in sheet 1 column and One pop up message appears " Apples and applesB matched and Orange


and orangeB matched" if any match found.

 

[Luke M]

1. Here's the find & replace code:

[pre]

Sub RemoveDash()
Columns("A:A").Replace What:="-*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
2. Remove duplicates

[pre][code]Sub RemoveDuplicates()
Dim LastRow As Integer
Application.ScreenUpdating = True
With Worksheets("Sheet1")
LastRow = .Range("A65536").End(xlUp).Row

For i = LastRow To 1 Step -1
If WorksheetFunction.CountIf(.Range(.Cells(LastRow, "A"), .Cells(i, "A")), .Cells(i, "A").Value) > 1 Then
.Cells(i, "A").EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
3. This is a simple formula of:

=IF(COUNTIF(Sheet1!A:A,A2),"MATCHED","NOT MATCHED")


4. I'm not sure what you're asking. =(


5. Macro

Sub DeleteEOL()
Dim LastRow As Integer
Application.ScreenUpdating = True
With Worksheets("Sheet1")
LastRow = .Range("G65536").End(xlUp).Row

For i = LastRow To 1 Step -1
If .Cells(i, "G").Value = "EOL" Then
.Cells(i, "G").EntireRow.Delete
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[/pre]
6. Sort col f

Sub SoftColF()

Columns("F:F&#34.Select
ActiveWorkbook.Worksheets("Sheet1&#34.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1&#34.Sort.SortFields.Add Key:=Range("F1&#34, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1&#34.Sort
.SetRange Selection
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub[/code][/pre]
7. Not sure what "highlight to arial 11" means.


There's a lot of confusion in your post...perhaps uploading a workbook would be helpful?

 

[IKHAN]

Luke - Have uploaded the file for your ref. and also inserted comments on whts not working. ..Thanks for your help


Issue 1 - To be Deleted Complete ROW if Column "G" has "End of Life" in sheet 1 - Not working when MACRO used

Issue 2 -looking for Macro to compare daily copied data in Column A in sheet 1 with Critical(sheet2) Column A, If match found write in Column C of Sheet 1 as "Matched"

Issue 3 - (Using Formula currently -Need MACRO)(When I copy/ paste daily data into Column A of sheet 1,It looks for corresponding data in pre Inputted sheet column F, J,S and it pastes in Sheet 1 Column E,F,G)

Issue4 - Select all Sheet 1 from Row 5 to column G and sort by Column F in ascending order(A-Z)

Issue5-Select ALL sheet 1 from Row 5 to column G and change FONTS to ARIAL and size 11


Download link:

https://hotfile.com/dl/153541673/6c15908/TEST_FILE.xlsx.html


Forum link:

https://hotfile.com/dl/153541673/6c15908/TEST_FILE.xlsx.html


HTML link:

https://hotfile.com/dl/153541673/6c15908/TEST_FILE.xlsx.html


Delete link:

https://hotfile.com/kill/153541673/0b412b4e07d6fa0bbf7c5580f6bb478c1eea016f.html


If there is a problem displaying the e-mail or you just can't copy the adresses click on or copy & paste this URL into your browser's address bar to see all the links:

http://hotfile.com/links/153541673/72a8379/TEST_FILE.xlsx

 

[IKHAN]

Have uploaded file for your ref. ,


Issue 1- To be Deleted Complete ROW if Column "G" has "End of Life" in sheet 1 - Not working when MACRO used

Issue2- looking for Macro to compare daily copied data in Column A in sheet 1 with Critical(sheet2) Column A, If match found write in Column C of Sheet 1 as "Matched"

Issue 3- (Using Formula currently -Need MACRO)(When I copy/ paste daily data into Column A of sheet 1,It looks for corresponding data in pre Inputted sheet column F, J,S and it pastes in Sheet 1 Column E,F,G)

Issue-4- Select all Sheet 1 from Row 5 to column G and sort by Column F in ascending order(A-Z)

Issue-5- Select ALL sheet 1 from Row 5 to column G and change FONTS to ARIAL and size 11


https://hotfile.com/dl/153541673/6c15908/TEST_FILE.xlsx.html