VBA, pulling values together to do a comparison

wonka1234 · Jul 19th 2016

Hi,

my current code does

1) Opens my desired workbooks via a picker button on my master workbook (Just need to compare 2 at once)
2) Delimits by pipe
3) concatenates columns B and C (into col D)

I am stuck here, my goal is to compare column A numbers from both opened workbooks on my master sheet.

Should I vlookup my values from one workbook to another and bring in the numbers and subtract?

How can I get my values in my master sheet and do a nice little subtraction? its tough, like I looked at some code to put my sheets into master but you have to state the master sheet and the current sheets to copy (sheet names will always be dynamic names so this kind is useless)

Any help would be appreciated, Sorry i am a beginner.

Current code:

Code

Sub Rec()
    Dim fileNames As Object, errCheck As Boolean
    Dim ws As Worksheet, wks As Worksheet, wksSummary As Worksheet
    Dim y As Range, intRow As Long, i As Integer
    
    
    




    ' Turn off screen updating and automatic calculation
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With




    'get user input for files to search
    Set fileNames = CreateObject("Scripting.Dictionary")
    errCheck = UserInput.FileDialogDictionary(fileNames)
    If errCheck Then
        Exit Sub
    End If




    For Each Key In fileNames 'loop through the dictionary




        On Error Resume Next
        Workbooks.OpenText Filename:=fileNames(Key), _
                           DataType:=xlDelimited, _
                           Other:=True, _
                           OtherChar:="|"
        On Error GoTo 0    ' or your custom error handler
        
     'delete first row
       For Each ws In ActiveWorkbook.Worksheets
ws.Range("1:1").Delete
Next ws
       
       'concat B and C and place into D
        For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(i, "D").Value = Cells(i, "C").Value & "_" & Cells(i, "B").Value
    Next i
    
   'paste into rec sheet?








    Next 'End of the fileNames loop
    Set fileNames = Nothing
















    
    ' Reset system settings
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = True
        .Visible = True
    End With
End Sub

Display More

wonka1234 · Jul 20th 2016

Re: VBA, pulling values together to do a comparison

bumpy

Infomage · Jul 20th 2016

Re: VBA, pulling values together to do a comparison

When you say you want to compare the numbers in column A, what do you mean exactly?

You might mean that you want to indicate elsewhere whether the same or different values appear on each row, or perhaps determine whether the number on one sheet appears anywhere in the other sheet(s)...

Please clarify. The more detail the better.

wonka1234 · Jul 21st 2016

Re: VBA, pulling values together to do a comparison

Quote from Infomage;774260

When you say you want to compare the numbers in column A, what do you mean exactly?

You might mean that you want to indicate elsewhere whether the same or different values appear on each row, or perhaps determine whether the number on one sheet appears anywhere in the other sheet(s)...

Please clarify. The more detail the better.

Sorry.

So both files are identical set, Column A has #'s. When I say i want to compare, I mean the difference between them.
Now each number has a country and city assigned to it, B and C respectively. Also in both sheets.
Now they are not in order, so new york usa may be in row 1 in sheet A, and row 30 in sheet B, So there must be some sort of lookup? Correct?

The numbers are not identical on both workbooks, but the citys and countries are. if they arent the result should just be N/A or something.

Is that more helpful?

wonka1234 · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

bumpy

**StephenR** · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

Given that you've not had much response, I suggest you post a small sample workbook which illustrates how your data are laid out and your desired results.

wonka1234 · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

Perhaps this sample shows more detail.

Sheet 1 would be a workbook in reality, same with sheet 2.
and desired result would be my master sheet with the button.

Of course, my way may be abit longer, so if anyone has ideas on better compiling this, it would be appreciated. ie. the concat may not be necessary?

**StephenR** · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

So the two source tables are in different files and you want the results in a third file?

wonka1234 · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

Quote from StephenR;774365

So the two source tables are in different files and you want the results in a third file?

Yes. Precisely. Unless you think of a better way of displaying?

**StephenR** · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

Well you'll need to add workbook references, but this should work for your example file. If you sorted both tables before you wouldn't need the concatenate if they contain the same items

Code

Sub x()


Dim rWB1 As Range, rWB2 As Range, r As Range, n As Long


Set rWB1 = Sheets("Workbook 1").Range("A2").CurrentRegion
Set rWB2 = Sheets("Workbook 2").Range("A2").CurrentRegion


With Sheets("Desired Result")
    .Range("A1:D1").Value = Array("Concatenated (Country and City)", _
                                "Number from workbook 1", _
                                "Number from workbook 2", _
                                "Difference")
    For Each r In rWB1.Columns(4).Offset(1).Resize(rWB1.Rows.Count - 1).Cells
        n = Application.Match(r, rWB2.Columns(4), 0)
        .Range("A" & Rows.Count).End(xlUp)(2).Value = r.Value
        .Range("B" & Rows.Count).End(xlUp)(2).Value = r.Offset(, -3).Value
        .Range("C" & Rows.Count).End(xlUp)(2).Value = rWB2.Cells(n, 1).Value
        .Range("D" & Rows.Count).End(xlUp)(2).Value = r.Offset(, -3).Value - rWB2.Cells(n, 1).Value
    Next r
End With


End Sub

Display More

wonka1234 · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

thanks for your reply, where in my original code could I input this?

**StephenR** · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

I can't see where you define your file names? Do the two files have specific names or can they be anything?

wonka1234 · Jul 22nd 2016

Re: VBA, pulling values together to do a comparison

Quote from StephenR;774371

I can't see where you define your file names? Do the two files have specific names or can they be anything?

They will change slightly, so no they wont always be the same name,

wonka1234 · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

bumpy

**StephenR** · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

Can you answer my previous question please. I don't understand this line, it errors for me

Code

errCheck = UserInput.FileDialogDictionary(fileNames)

wonka1234 · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

Quote from StephenR;774447
Can you answer my previous question please. I don't understand this line, it errors for me
Code
errCheck = UserInput.FileDialogDictionary(fileNames)

Sorry Stephen , User Input is just a file picker functionI have in another module.
I can post that too, Name the module 'UserInput' :

Code

Function FileDialogDictionary(ByRef file As Object) As Boolean ' returns true if the user cancels
'Declare a variable as a FileDialog object.
Dim fd As FileDialog
Dim item As Variant
Dim i As Long
'Create a FileDialog object as a File Picker dialog box.
file.RemoveAll 'clear the dictionary
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
'Use a With...End With block to reference the FileDialog object.
With fd
    'Use the Show method to display the File Picker dialog box and return the user's action.
    'The user pressed the action button.
    .Title = "Select Excel Workbooks" 'Change this to suit your purpose
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Microsoft Excel files", "*.xlsx,*.xls,*.csv,*.dat,*txt"
    If .Show = -1 Then
        'Step through each string in the FileDialogSelectedItems collection.
        For Each item In .SelectedItems 'loop through all selected and add to dictionary
            i = i + 1
            file.Add i, item
        Next item
        FileDialogDictionary = False
    'The user pressed Cancel.
    Else
        FileDialogDictionary = True
        Set fd = Nothing
        Exit Function
    End If
End With
Set fd = Nothing 'Set the object variable to Nothing.
End Function

Display More

**StephenR** · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

Try this, you will probably have to change some references. It has a slightly different approach to selecting the files - just select the two at the beginning.

Code

Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet, rWB1 As Range, rWB2 As Range, r As Range, n As Long


' Turn off screen updating and automatic calculation
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
     
With Application.FileDialog(msoFileDialogOpen)
 .AllowMultiSelect = True
 .Show
 Set wb1 = Workbooks.Open(.SelectedItems(1))
 Set wb2 = Workbooks.Open(.SelectedItems(2))
End With
     
For Each ws In wb1.Worksheets
    ws.Range("1:1").Delete
    For i = 2 To ws.Cells(Rows.Count, "A").End(xlUp).Row
         ws.Cells(i, "D").Value = ws.Cells(i, "C").Value & "_" & ws.Cells(i, "B").Value
     Next i
Next ws


For Each ws In wb2.Worksheets
    ws.Range("1:1").Delete
    For i = 2 To ws.Cells(Rows.Count, "A").End(xlUp).Row
         ws.Cells(i, "D").Value = ws.Cells(i, "C").Value & "_" & ws.Cells(i, "B").Value
     Next i
Next ws
      
Set rWB1 = wb1.Sheets(1).Range("A2").CurrentRegion ' may have to change sheet
Set rWB2 = wb2.Sheets(1).Range("A2").CurrentRegion ' may have to change sheet
 
With ThisWorkbook.Sheets(1)  ' may have to change workbook/sheet
    .Range("A1:D1").Value = Array("Concatenated (Country and City)", _
    "Number from workbook 1", _
    "Number from workbook 2", _
    "Difference")
    For Each r In rWB1.Columns(4).Offset(1).Resize(rWB1.Rows.Count - 1).Cells
        n = Application.Match(r, rWB2.Columns(4), 0)
        .Range("A" & Rows.Count).End(xlUp)(2).Value = r.Value
        .Range("B" & Rows.Count).End(xlUp)(2).Value = r.Offset(, -3).Value
        .Range("C" & Rows.Count).End(xlUp)(2).Value = rWB2.Cells(n, 1).Value
        .Range("D" & Rows.Count).End(xlUp)(2).Value = r.Offset(, -3).Value - rWB2.Cells(n, 1).Value
    Next r
End With
     
' Reset system settings
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = True
    .Visible = True
End With


End Sub

Display More

wonka1234 · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

Do I combine above code to my original?

Or is this fine on its own? (keep in mind im opening .txt files that are piped, (not delimited))

Running on its own I get error on :

Code

[COLOR=blue]Set[/COLOR][COLOR=#333333] rWB1 = wb1.Sheets(1).Range("A2").CurrentRegion [/COLOR][COLOR=#006400]' may have to change sheet[/COLOR]

What is currentRegion?

**StephenR** · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

OK, try this, replacing all your code

Code

Sub x()


Dim wb1 As Workbook, wb2 As Workbook, ws As Worksheet, rWB1 As Range, rWB2 As Range, r As Range, n As Long


With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
     
With Application.FileDialog(msoFileDialogOpen)
 .AllowMultiSelect = True
 .Show
 Workbooks.OpenText Filename:=.SelectedItems(1), DataType:=xlDelimited, Other:=True, OtherChar:="|"
 Set wb1 = ActiveWorkbook
 Workbooks.OpenText Filename:=.SelectedItems(2), DataType:=xlDelimited, Other:=True, OtherChar:="|"
 Set wb2 = ActiveWorkbook
End With
     
With wb1.Worksheets(1)
.Range("1:1").Delete
    For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
        .Cells(i, "D").Value = .Cells(i, "C").Value & "_" & .Cells(i, "B").Value
     Next i
End with


With wb2.Worksheets(1)
    .Range("1:1").Delete
    For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
         .Cells(i, "D").Value = .Cells(i, "C").Value & "_" & .Cells(i, "B").Value
     Next i
End with
      
Set rWB1 = wb1.Sheets(1).Range("A2").CurrentRegion ' may have to change sheet
Set rWB2 = wb2.Sheets(1).Range("A2").CurrentRegion ' may have to change sheet
 
With ThisWorkbook.Sheets(1)  ' may have to change workbook/sheet
    .Range("A1:D1").Value = Array("Concatenated (Country and City)", _
    "Number from workbook 1", _
    "Number from workbook 2", _
    "Difference")
    For Each r In rWB1.Columns(4).Offset(1).Resize(rWB1.Rows.Count - 1).Cells
        n = Application.Match(r, rWB2.Columns(4), 0)
        .Range("A" & Rows.Count).End(xlUp)(2).Value = r.Value
        .Range("B" & Rows.Count).End(xlUp)(2).Value = r.Offset(, -3).Value
        .Range("C" & Rows.Count).End(xlUp)(2).Value = rWB2.Cells(n, 1).Value
        .Range("D" & Rows.Count).End(xlUp)(2).Value = r.Offset(, -3).Value - rWB2.Cells(n, 1).Value
    Next r
End With
     
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = True
    .Visible = True
End With


End Sub

Display More

wonka1234 · Jul 25th 2016

Re: VBA, pulling values together to do a comparison

Wow thanks, seems to work, however I get an error on :

Code

n = Application.Match(r, rWB2.Columns(4), 0)

VBA, pulling values together to do a comparison

Participate now!

Share