Excel VBA help

[steviewonderpants]

Hello all

I have one speadsheet with 3 tabs. Tab 1 contains around 800 lines of our master account and tab 2 contains a small number of these accounts. Tab 3 is blank. What I am looking to do is cross reference tabs 1 & 2 for recurring data. If any detail comes up more than once I'd like to copy the entire line from tab one and paste it into tab 3.

Is this possible... If so how??

Many thanks

 

[jindon]

I have edited the code, so copy and try again.

I will go off-line now and next reply will be after Tue

rgds,
jindon

 

[steviewonderpants]

Hi Jindon

I am having a couple more issues with this macro, I have edited my last post to state the problem.

Many thanks for helping with this.

Steve

 

[steviewonderpants]

Hi

Thanks again for your help on this, I have now tweaked your VB code to suit my sheet and have ended up with this...

Sub CrossRef()

Dim Master As Worksheet 'Sheet1
Dim RefTab As Worksheet 'Sheet2
Dim NewTab As Worksheet 'Sheet3
Dim Cell As Range
Dim iRow As Long
Dim jRow As Long
Dim dic As Object

Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompre

Set Master = Sheet2
Set RefTab = Sheet1
Set NewTab = Sheet3

jRow = NewTab.Range("G65536").End(xlUp).Row
With Master
For iRow = 2 To .Range("g" & Rows.Count).End(xlUp).Row
If Not dic.exists(.Cells(iRow, "g").Value) Then dic.Add .Cells(iRow, "g").Value, Nothing
Next
End With
With RefTab
For iRow = 2 To .Range("a" & Rows.Count).End(xlUp).Row
If dic.exists(.Cells(iRow, "a").Value) Then
.Rows(iRow).Copy NewTab.Rows(jRow)
jRow = jRow + 1
End If
Next
End With
Set dic = Nothing
End Sub

It seems that the setmaster and set Reftab numbers needed to be switched but this is now working a treat.

One final thing, a collegue and myself have been reading through this to try and follow the string but we were a little confused over what the following means "dic.CompareMode = vbTextCompre" So I was hoping that you could give me a quick one line explanation as to what that means.

Many thanks

Steve

 

[jindon]

"dic.CompareMode = vbTextCompare"

Dictionary object has 2ways in comparing the value

TextCompare : Non case sensitive
BinaryCompare : Case sensitive (Default setting)

 

[steviewonderpants]

Hi Jindon

I have today found a problem with this macro in when I run this the sheet seems to be moving entries onto the third tab that are found in tab 2 but are not found in tab 1.

Is there a tweak to the above code that can be changed to eradicate this problem. For example I have account number X123 in Column A of tab 2 and if this is in column G of tab 1 then move the entire line to tab 3. However in this instance X123 is not on tab one but is moving tab 3 when it shouldn't be.

Is there also a way that when the macro is run I can watch a step by step 'run through' of the process to see how it works.

Many thanks

Steve

 

[jindon]

Does this work?

Sub CrossRef()

   Dim Master     As Worksheet   'Sheet1
   Dim RefTab     As Worksheet   'Sheet2
   Dim NewTab     As Worksheet   'Sheet3
   Dim Cell       As Range
   Dim iRow       As Long
   Dim jRow       As Long
   Dim dic As Object
    
   Set dic = CreateObject("Scripting.Dictionary")
   dic.CompareMode = vbTextCompre
    
   Set Master = Sheet2
   Set RefTab = Sheet1
   Set NewTab = Sheet3
    
   jRow = NewTab.Range("G65536").End(xlUp).Row
   With Master
      For iRow = 1 To .Range("g" & Rows.Count).End(xlUp).Row
         If Not dic.exists(.Cells(iRow, "g").Value) Then dic.Add .Cells(iRow, "g").Value, iRow
      Next
   End With
   With RefTab
      For iRow = 1 To .Range("a" & Rows.Count).End(xlUp).Row
         If dic.exists(.Cells(iRow, "a").Value) Then
            Master.Rows(dic(.Cells(iRow,"a").Value).Copy NewTab.Rows(jRow)
            jRow = jRow + 1
         End If
      Next
   End With
Set dic = Nothing
End Sub

 

[steviewonderpants]

Hi Jindon

The macro does not like this line

Master.Rows(dic(.Cells(iRow,"a").Value).Copy NewTab.Rows(jRow)

Thanks

Steve

 

[jindon]

Yeah

should

dic.add .Cells(iRow,"g").Value , iRow

instead of i


code edited already

 

[steviewonderpants]

Hi, sorry but I am a little confused at how I should be changing this as the line is still red within the VB..

Sorry

 

[jindon]

No

You don't need to change it, just copy the code again and try