Thank you Mumps,
I understand your concern and i greatly appreciate your help this far. This has helped me understand more about VBA than you can imagine.
Posts by aelborn
-
-
Understood on referencing by WS1.Name - I will give that a try in the meantime. Can the .Name be a variable from the Userform?
There will not be a need to prompt to select each master file as the user fills that information in at the beginning in the Userform - they are asked to select how many Master Docs there are and where their locations are.
I would like to loop through however many the user selects - up to 5
Their extensions will all be .xlsm
Yes, i would like this to happen for each master file - on the let side of the screen there will be the CSV with/without highlighted cells, and on the right there may be 5, all the way down to 1, master docs open with/without highlighted cells. -
Good Morning Mumps,
I apologize for the confusion. There is only 1 CSV file that will check itself against (1 and up to 5) master docs while highlighting the cells in the Master doc/(s) if not in the CSV. Then, in the CSV, cells will highlight if not found in the Master doc (or master docs). The WS1 and WS2 is from your code:Dim WS1 as Worksheet
I am unable to reference a variable from the userform when WS1/2 is a Worksheet, not a string. When I Dim as WS1 as String, the variable WS1/2 comes back as an invalid qualifier.
As far as the Master doc/(s) containing multiple sheets, i was trying to express that i would have to call the Video Checklist sheet. Only the Video Checklist tab will be reconciled against.
PM501 is the name of the Master doc
-
Master Doc attached
-
Mumps,
As instructed, necessary files attached to run macro. Also, a description of my current problem.
1) initial code isDim WS1 as Worksheet
Dim WS2 as worksheetI would like to Dim WS1/2 as Strings so i can enter in textbox values from userform. However, when i Dim WS1 (& WS2) as String, WS1 (& WS2) receive an error message of INVALID QUALIFIER. I would like to use variables as i want the code to run all the way through after the user fills out the form completely. The end goal is to have the CSV on the left side (with highlighted cells, if any, and on the right side of the screen, the PM501/(s) with highlighted cells, if any. This would allow the user to review the CSV & PM501/(s) for any discrepancies.
The CSV Creator & Reconciliation code is unlocked and i think might bring a laugh or two due to my VERY limited coding skills, but i would really like this work somehow. Any help or direction would be appreciated.
one would open the CSV & Reconciliation doc, load the user form and then click "Create CSV". Merging the CSVs can be ignored for the problem that i am working on at the moment (NO)
I appreciate the help. The next post has the master doc attached as only 2 attachments can be sent per post
-
Amazing, thanks! So cool
-
Mumps,
This is exactly what i am looking for!! I do appreciate the help very much. I will be spending the next while learning exactly what you did. As i said, very new to it, but my interest level continues to grow. Thank you again!Thank you,
Andrew -
Good Afternoon Mumps,
Thank you for the response and direction. Please see below and attached.
I have attached two files - Master Doc and CSV. I would like the macro to start at the top of Cell A1 in Master Doc, recognize the value of that cell, then search the entire column A of the CSV doc for that value, and if it is present, move to A2 of Master Doc, recognize the value, and then search the entire column A of the CSV doc for that value. If the value is not in that column, i would like it to highlight that Cell, A2 of the master doc. Once it has gone through the whole column A of the Master doc, i would like it to do the exact opposite in the CSV, if it is present, no change, if not in the Master doc, highlight the cell in the CSV.In the CSV doc attached, if you run the code Compare_Col(), it works perfectly and as i would like it, but i want to treat the second sheet as another workbook.
I appreciate the help this far, and if anything else is needed that would allow you or anyone to help in anyway, please let me know. I keep coming up with Conditional Formatting and VLookup, but i THINK it will not work in this scenario, the code i have is great, just need to know if i can apply to two different workbooks, not 2 different sheets in the same workbook.
-
Hello All, i am very new to VBA but i am working my way through, slowly. The code below, NOT MINE, works great between 2 different sheets within the same workbook. Can the code be applied to work between 2 workbooks? I have tried but i am getting to the point where i believe it is not designed for the purpose i have in mind. Any help would be very much appreciated.
Code
Display MoreSub compare_cols() Dim report1 As Workbook 'CSV Dim report2 As Workbook 'master doc Set report1 = Workbooks.Open(Filename:="file path") 'csv Set report2 = Workbooks.Open(Filename:="file path") 'master doc Windows("report2").Activate Windows("report1").Activate Dim myRng As Range Dim lastCell As Long 'Get the last row Dim lastRow As Integer lastRow = ActiveSheet.UsedRange.Rows.Count 'Debug.Print "Last Row is " & lastRow Dim c As Range Dim d As Range Application.ScreenUpdating = False For Each c In report1.Range("A2:A" & lastRow).Cells For Each d In report2.Range("A2:A" & lastRow).Cells c.Interior.Color = RGB(255, 199, 206) 'Light red background color c.Font.Color = RGB(0, 0, 0) 'Black font color If (InStr(1, d, c, 1) > 0) Then c.Interior.ColorIndex = xlNone 'No fill color Exit For End If Next Next For Each c In report2.Range("A2:A" & lastRow).Cells For Each d In report1.Range("A2:A" & lastRow).Cells c.Interior.Color = RGB(255, 199, 206) 'Light red background color c.Font.Color = RGB(0, 0, 0) 'Black font color If (InStr(1, d, c, 1) > 0) Then c.Interior.ColorIndex = xlNone 'No fill color Exit For End If Next Next Application.ScreenUpdating = True End Sub