Yes, the code works perfect!!
Posts by Anna Mary
-
-
Sorry, Justin. My miss.
Could you please suggest me a way, if the data is jumbled
for ex
Workbook 1
1 row
2 row
3 row
Workbook 2
3 row
2 row
1 row
1 row should compare with 1 row if the data is not in order.
Please share your thoughts.
-
Thank you Justin, but I'm getting error when I run this code. Attaching screenshots for reference.
Please let me know what should I update.
It would be great if the code allows me to pick the file for comparison.
For ex
CodesBook = Application.InputBox(Prompt:= _"Compare this workbook (" & wb1.Name & _") to...?", -
Hi
Looking for a code to compare two workbooks and highlight the differences.
I have 3 workbooks,
Host.xlsm
File1.xlsx( only sheet1 available)
Flie2.xlsx( only sheet1 available)
Below is the code but it doesn't work.
Any help is appreciated. Thanks!
Code
Display MoreSub All_Diffs_Highlighted() Dim wb1 As Workbook Dim wb2 As Workbook Dim ws1 As Worksheet Dim ws2 As Worksheet Dim Cell As Range Dim sBook As String ' If Workbooks.Count < 2 Then MsgBox "Error: Only one Workbook is open" & vbCr & _ "Open a 2nd Workbook and run this macro again." Exit Sub End If ' Application.DisplayAlerts = False fbook = Application.InputBox(Prompt:= _ "Select workbook(" & wb1.Name & _ ") to...?", _ Title:="workbook1?", _ Default:=wb1.Name, _ Type:=2) Set wb1 = Workbooks(fbook) For Each wb2 In Workbooks If wb2.Name <> wb1.Name Then Exit For Next ' On Error Resume Next ReDo1: Application.DisplayAlerts = False sBook = Application.InputBox(Prompt:= _ "Compare this workbook (" & wb1.Name & _ ") to...?", _ Title:="Compare to what workbook?", _ Default:=wb2.Name, _ Type:=2) If sBook = "False" Then Exit Sub If Workbooks(sBook) Is Nothing Then MsgBox "Workbook: " & sBook & " is not open." GoTo ReDo1 Else Set wb2 = Workbooks(sBook) End If ' Application.ScreenUpdating = False For Each ws1 In wb1.Sheets If Not wb2.Sheets(ws1.Name) Is Nothing Then Set ws2 = wb2.Sheets(ws1.Name) For Each Cell In ws1.UsedRange If Cell.Formula <> ws2.Range(Cell.Address).Formula Then Cell.Interior.ColorIndex = 35 ws2.Range(Cell.Address). _ Interior.ColorIndex = 35 End If Next Cell If ws1.UsedRange.Rows.Count <> _ ws2.UsedRange.Rows.Count Or _ ws1.UsedRange.Columns.Count <> _ ws2.UsedRange.Columns.Count Then For Each Cell In ws2.UsedRange If Cell.Formula <> ws1.Range(Cell.Address).Formula Then Cell.Interior.ColorIndex = 35 ws1.Range(Cell.Address). _ Interior.ColorIndex = 35 End If Next Cell End If End If Next ws1 ' Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub -
Thank you so much, Mumps!!!
-
Thank you, Mumps. Really appreciate your help!! What should I update in the above code, if I require to change only 3rd and 5th instance of "Fruit"?
-
This macro should work only if header contains "Fruit" else skip this step. What code should I update? Pleas help
-
I tried trimming and getting errors. I have attached my sample workbook in the above post. Attaching it again..
-
Wow. thank you so much rory. Is it possible to remove all the special characters from a worksheet?
-
Good Day,
Please assist to trim the recorded macro.
Code
Display MoreSub Replace() Cells.Find(What:="Fruit", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate Cells.FindNext(After:=ActiveCell).Activate ActiveCell.Replace What:="Fruit", Replacement:="Apple", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Cells.Find(What:="Fruit", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate ActiveCell.Replace What:="Fruit", Replacement:="Mango", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Cells.Find(What:="Fruit", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Replace What:="Furit", Replacement:="Orange", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 Cells.Find(What:="Fruit", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate End SubA table consist of same header, I'm replacing the header names with find and replace vba code.
Ignoring the two matches and Replacing 3rd match, 4th match, 5th match as Apple,Mango and Orange respectively.
Kindly refer to the attached file.
-Anna
-
highlighting cells that contain any characters in a range
-
-
-
Good Day,
Can anyone please share suggestions to highlight cells those contain "*" or "**" or "***".
Thanks
Anna
-
I receive 5-6 columns of data from a client.
Scenario 1
column_e, column_b, column_a, column_c,column_d
<Reordering the columns and delivering the outputs>
Reqd. Output
column_a,column_b,column_c,column_d,column_e
Scenario 2
column_a, column_c, column_e, column_d,column_b
Reqd. Output
column_a,column_b,column_c,column_d,column_e
Scenario 3
column_a, column_c, column_e, column_d,column_b,column_f
Reqd. Output
column_a,column_b,column_c,column_d,column_e
As you can see the reqd. output, column order is fixed (column_a,column_b,column_c,column_d,column_e),
But I get inputs in different column order. I'm looking for a code which can work dynamically. Irrespective of the input, the columns should get rearranged to a,b,c,d,e..
-
Oh thats nice. If I interchange the column header, will the output get fetched accurately?
-
Yes, But its completely based on copy-paste codes which is not dynamic.
-
royUK Sure, I will do that.. Any inputs on rearranging the columns in customized order?
-
Thank you royUK. Yes, the input data contain formulas. Looks much better than before.
-
Here you go.. royUK