Help with this VBA code please?

[tekkenking]

Hi


Narayan has been extremely kind in helping me with an excel sheet (see http://chandoo.org/forums/topic/saving-whilst-using-advanced-filters?replies=1#post-89762 if interested).


There is some VBA code he had written and I was wondering if anybody could help me in deciphering it.


Any sort of commentary to put it in context would be extremely useful for my learning especially since I would now like to manipulate by adding rows/columns.


I am particularly interested in this portion:

[pre]

Public Sub Transfer()
Dim from_array As Variant, to_array As Variant

Range("Output").Cells(2, 1).Select
number_of_rows = ActiveCell.CurrentRegion.Rows.Count - 1
number_of_columns = Range("Output").Columns.Count

from_array = ActiveCell.Resize(number_of_rows, number_of_columns).Value

Worksheets("SalesRecord").Activate
Range("Data_Range").Cells(2, 1).Select
number_of_rows = ActiveCell.CurrentRegion.Rows.Count - 1
number_of_columns = ActiveCell.CurrentRegion.Columns.Count

to_array = ActiveCell.Resize(number_of_rows, number_of_columns).Value

For i = LBound(to_array, 1) To UBound(to_array, 1)
cell = to_array(i, 1)
If cell = Worksheets("Snapshot").Range("Criteria").Cells(2, 1).Value Then
st = to_array(i, 2)
pt = to_array(i, 3)
qt = to_array(i, 4)

For j = LBound(from_array, 1) To UBound(from_array, 1)
sf = from_array(j, 2)
pf = from_array(j, 3)
qf = from_array(j, 4)
cf = from_array(j, 5)

If ((st = sf) And (pt = pf) And (qt = qf)) Then
ActiveCell.Offset(i - 1, 4).Value = cf
End If
Next
End If
Next
End Sub

[/pre]

Narayan if you could answer that would be fantastic, however I fear I may have taken up a lot of your time with this - so if there is anybody out there who could help, I would be extremely grateful.


Many thanks.

 

[tekkenking]

specifically, I would like to know if my columns have now increased on my sales record, how do i go about making the changed?


narayans:

https://www.dropbox.com/s/enhdekpb0altluw/Advanced%20Filter_R1.xlsm


new requirement:

https://hotfile.com/dl/202450983/5bbe5da/Advance_Filter_Updated.xlsm.html


Only the last to columns need to be editable and saveable (editable and comments columns)


Thanks

 

[NARAYANK991]

Hi Jeevan ,


The code is actually straightforward ; the only point is that instead of checking cells , which may be time-consuming , we are transferring the data in the cells / ranges to arrays , and then comparing two arrays.


We have a tab called SalesRecord , which is a sort of master database ; there is another tab called Snapshot which contains filtered data ; the filter criterion is a drop-down which allows you to select from a list ; once the filtered data is displayed on the Snapshot tab , you enter comments in the comments column , and click on the TRANSFER button , which transfers the comments to the master database in the SalesRecord tab.


To do this transfer , it compares the cells in the 4 columns on the SalesRecord tab with the corresponding cells in the Snapshot tab ; if they match , the transfer is done.


The first change is that the columns in the named range Extract need to match the columns in the named range Data_Range ; I changed the definition of the named range Extract to :


=Snapshot!$B$6:$N$6


A similar change has to be made in the definition of the named range Output.


Secondly , the columns all have to have field names , since otherwise the CurrentRegion property will not work correctly.


The revised file is here :


https://www.dropbox.com/s/m4khawdi5j7sb0p/Advance%20Filter%20Updated.xlsm


Narayan

 

[Slimline]

Hi Narayan,


Your spreadhseet was exactly what I was looking for. However I have alot more collumns.


All the buttons work without any error messages after having the relevant ranges but the problem I am having is getting my data to paste in my last collumn.


Please correct me if I am wrong but I know I need to make changes to this part of the code:


st = to_array(i, 2)

pt = to_array(i, 3)

qt = to_array(i, 4)


For j = LBound(from_array, 1) To UBound(from_array, 1)

sf = from_array(j, 2)

pf = from_array(j, 3)

qf = from_array(j, 4)

cf = from_array(j, 5)


If ((st = sf) And (pt = pf) And (qt = qf)) Then

ActiveCell.Offset(i - 1, 4).Value = cf


Can you please explain/clarify what st,pt,qt and sf,pf,qf,cf are? What changes will I need to make in order to get the text to paste in last collumn as my spreadsheet grows.


Many Thanks,


Serena

 

[NARAYANK991]

Hi Serena ,


The 't' suffix is for to , and the 'f' suffix is for from.


The 4 columns which need to be matched in the original poster's problem are :


Team , Salesman , Product and Quantity


In the code , cell represents the team , which is selected using the drop-down ; the other 3 variables st / sf , pt / pf and qt / qf represent the salesman , product and quantity. When all 4 variables match in the from_array and to_array , it means we are at the right record , in which case , we copy the entered comment ( the 5th column ).


I can suggest more specific changes if I know how your data is laid out ; however , you can get a good idea by downloading the revised file , and comparing the two versions ; in the revised file , the number of columns to be displayed has increased. I have mentioned a couple of other changes to be made to the named ranges in my earlier post.


Narayan

 

[Slimline]

Hi,

Thats great! My sheet is working.

One more question for my learning –


So I know s,p,t represent Salesman , Product and Quantity. Can I use any letters e.g. a,b,c...to represent such variables?


Thanks !

Serena

 

[NARAYANK991]

Hi Serena ,


Of course ; though I have not followed the principle , programming books recommend that you use understandable names for variables , so that if you do need to modify the code at a later date , you will be able to it more easily , especially when your code is longer than say 100 lines.


Narayan

 

[Slimline]

Hi Narayan,

Cheers for the above!


If you really don’t mind there is something a little more challenging as I need because I am facing changes & duplication of data.


S0 I want to track & keep history of the changes of ‘salesman’, which may change every week.


FYI - In my weekly reports there is no ‘current salesman’ column. So I manually paste the new data in column B -C, skip D and then paste rest of the data in E-N.


New data = red

Old data = black.

Full Duplicate = Italic red


https://hotfile.com/dl/203207506/7a8f8c8/Advance_Filter_Updated_2_(2).xlsm.html


So when a new set of data is added to the list in ‘Salesrecord’ I want the macro button to do the following on the sheet:

1) Identify the new data added to the list (as it will be lower down in the list)

2) Look/match up the duplicate via REF# with the older data set (which will always be above it in the list)


- If the ‘original salesman’ in red = to the ‘original salesman’ in black, then ‘red’ is a duplicate & the whole red row is deleted.


- If the ‘original salesman’ in red is NOT = black, it is then copied into black ‘Current salesman’ cell and rest of the red row is also copied/ replaces the black row . (skipping ‘original salesman’). This last thing should happen every time incase other changes are made. The ‘originalsalesman’ stays unchanged as I always want to know which salesman it initially started from.


This would be extremely helpful if you can sort this out. Also thanks a lot for your previous work – its smashing!!!! 


Serena

 

[tekkenking]

An interesting twist on the spreadsheet. Curious to see how this works out