Pseudocode - level advice required

[Hairs]

Hi there everyone

I need some help defining the best methods of approaching how to achieve a project, as the code examples I've seen so far are either throwing up errors when I modify them (because I'm thick) or because I'm not approaching the code properly.

Basically what I need to do is to

1. Pull in only the used rows (which will be variable, between say 500 and 1500) from columns A, B, C, E & F from a source workbook and append to a master workbook, and ignore the rest (all columns in the source will end on the same row and the following rows will all be blank). Determining the Used Rows is important.
2. I need to re-order those columns slightly in the new workbook.
3. I need to assign today's date as a fixed value in Column G.
4. I need to pick a given sample (say, 50%) for today's date for a given value in Column C, and enter a fixed value in Column F and H.

I think that the simplest method is probably to have a "Daily Working" sheet in the master workbook, where I can pull the data, do the manipulations, and then append the data onto the master spreadsheet. My questions are as follows:

1. Why are the code examples for this so varied? Ron de Bruin's code in http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx goes on for pages, yet http://www.ozgrid.com/forum/showthread.php?t=86791& p=423613#post423613 seems to achieve the same thing in about 5 lines! If "UsedRange.Rows.Count" gives you the number of used rows in a column, why would you use anything else? I can't help feeling that I'm missing something here, or that UsedRange is unreliable somehow. Is it simply that "End(xlUp).Count" is applicable where there might be a blank entry in the column you're counting? Given that the sheet name will change daily that I'm copying from, am I better to use a FileDialogPicker box to get the user to open the source file, and select the last entry in a row on the sheet, and capture that data?
2. This should be a simple copy command, in fact I can probably just modify a recorded macro, right? Given how badly some of my code has disappeared I want to be sure. I should be able to do "copy source.(a4:bEnd) > Destination.(a4:bEnd) / copy source.(e4:eEnd) > Destination.(c4:cEnd) / copy source.(c4:cEnd) > destination.(d4:dEnd) / copy source.(f4:fEnd) > destination.(f4:fEnd)", then end the subroutine, right?
3. A dead simple code example from http://www.ozgrid.com/VBA/auto-add-date.htm to add the current date to another column when text is entered which looks perfect. However, because excel doesn't think paste or fill handle is a change event, it doesn't work manually. Should I resort to filling these columns during the copy operation?
4. I can randomise the data manually, but I'd like to be able to then pick sample sizes and output those samples in batches. So, a 15% sample of Value 3 in Column C will be split into batches, and then appended onto a checksheet for a staff member to look at, with the staff member's name in Column H of the master workbook. This is where it gets complicated, and I think I might be better off using a macro to do the sample in the workbook, enter the staff member's name in H, and then do the copying manually.

Thanks for any and all help you can offer, people.

 

[Smitty]

Welcome to the Board!

For starters I'd record a macro doing what you want. That will give you a better idea of what's going on in your particular case. And recorded code can always be optimized if you post it back here where someone can give it a once over for you.

For the random sample, I'd go back to the recorder and invoke the Sampling add-in (Data-->Data Analysis-->Sampling). If it's not there then you'll need to activate the Analysis Toolpak.

As to the other questions I'll see what I can do:

1. Code examples are varied because there are 100 different ways to skin a cat in Excel and everyone does things a bit differently. One routine vs. another doesn't necessarily matter provided they get you where you want and do it efficiently.

As for "UsedRange", it can be unreliable, because the UsedRange isn't always what you think. Let's say you have a 10,000 row data set and delete it down to 100 rows; Excel still sees the used range as 10,000 rows unless you actually delete the rows.

End(xlUp) is preferable over End(xlDown), which will fail if you have gaps in your data, where Up will find the last row with data.

2. Not quite sure given your post, but yes you can do this:

Range("A1:A1000").Copy Range("B1")

3. Probably, although depending on the circumstance you can get by with a Calculate event.

4. Sampling

HTH,

 

[Hairs]

Thanks for the pointers. I'd mostly given up on manipulating recorded macros because sifting out what was "me shuffling the data about manually" started to overwhelm the code I wanted to get at, and the code I want to end up with will rely on more variables than there would be hard links for the recorded macros.

The sampling tool add-in won't work for me, as I understand it only samples on numeric data. The data I'm going to sample is ranked according to text in column F. In fact, it'll be sampled twice. More to the point, I'm not sure I've got permission to install the analysis pack from the sysadmins (strange though that sounds, given that I'm running a macro which will pull data from other files).

Essentially, what I have is two workbooks, one with source data (Different file each month, different sheet for each day within that month), and a master workbook with a "daily working" sheet, a "records" sheet, and a "statistics" sheet. I'll pull in that day's data, clean it up, sample it, assign a staff member to look it over, and store the data on the records.

The "queue" (Type A, Type D, Type E) and "Task" columns need to be swapped in the "daily working" sheet. I then sample a percentage of the "audited" column, complete the "date received/date allocated" columns, fill in staff member's name(s) under "assigned" for the given samples, and fill the rest as "do not check".

The sample sizes are calculated at the top of the working sheet. I think the best way of doing this is to:

1) get the user to fill the Date received/allocated in the first data row manually (as this may not necessarily be today, or "yesterday").
2) start the macro, and fill to End(xlUp) with those dates, and =rand() in the last column.
3) Sort ascending according to the "random" column
4) Use a popup box with a dropdown menu to ask the user which staff member is working that day
5) Filter for "audited", and take the sample size from the top of the sheet, and use a loop for (sample size) to fill the staff member's name.
(Should I use Target(1, 11) to fill the "assigned to" cell in the row?)
6) Re-filter for "not checked", and repeat for the other sample size.
7) Re-filter for "blank" in "assigned to" and fill with "Don't check"


I can use this as an index for a loop in VBA, do a filter for "audited/not checked", then

Excel Workbook
	Date	Staff	Task ID	E	Queue	Type	Audited	Notes	J	K
4	1-Sep-11	123456	12345678923	Garbage	Type E	Copied Garbage	Audited		Garbage	Garbage
5	1-Sep-11	123456	12345678924	Garbage	Type E	Copied Garbage	Audited		Garbage	Garbage
6	1-Sep-11	123456	12345678925	Garbage	Type E	Copied Garbage	Not Checked		Garbage	Garbage
7	1-Sep-11	123456	12345678926	Garbage	Type E	Copied Garbage	Not Checked		Garbage	Garbage
8	1-Sep-11	123456	12345678927	Garbage	Type E	Copied Garbage	Not Checked		Garbage	Garbage
9	1-Sep-11	123456	12345678928	Garbage	Type E	Copied Garbage	Not Checked		Garbage	Garbage
10	1-Sep-11	123456	12345678929	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
11	1-Sep-11	123456	12345678930	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
12	1-Sep-11	123456	12345678931	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
13	1-Sep-11	123456	12345678932	Garbage	Type A	Copied Garbage	Audited		Garbage	Garbage
14	1-Sep-11	123456	12345678933	Garbage	Type A	Copied Garbage	Audited		Garbage	Garbage
15	1-Sep-11	123456	12345678934	Garbage	Type A	Copied Garbage	Audited		Garbage	Garbage
16	1-Sep-11	123456	12345678935	Garbage	Type A	Copied Garbage	Audited		Garbage	Garbage
17	1-Sep-11	123456	12345678936	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
18	1-Sep-11	123456	12345678937	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
19	1-Sep-11	123456	12345678938	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
20	1-Sep-11	123456	12345678939	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
21	1-Sep-11	123456	12345678940	Garbage	Type A	Copied Garbage	Not Checked		Garbage	Garbage
22	1-Sep-11	123456	12345678941	Garbage	Type D	Copied Garbage	Not Checked		Garbage	Garbage
23	1-Sep-11	234567	12345678942	Garbage	Type D	Copied Garbage	Not Checked		Garbage	Garbage
24	1-Sep-11	234567	12345678943	Garbage	Type D	Copied Garbage	Audited		Garbage	Garbage
25	1-Sep-11	234567	12345678944	Garbage	Type D	Copied Garbage	Audited		Garbage	Garbage
Source Data Worksheet

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1			Number of Tasks	Tasks Audited	Tasks Not Checked
2			0	0	0
3			Total Sample Size	Sample Size Audited	Sample Size Not Checked
4			0	0	0
5
6	Date	Staff ID	Queue	Task ID	Task Type	Audited	Notes	Date Received	Date Allocated	Date Completed	Allocated To	Random
7												0.864282
8												0.206937
9												0.110418
10												0.539639
11												0.53861
12												0.717145
13												0.468522
14												0.532195
15												0.019737
16												0.632203
17												0.494143
18												0.606042
19												0.157095
20												0.331818
21												0.36939
22												0.8644
23												0.655324
24												0.448284
25												0.046817
26												0.62636
Excel 2003 Today's working Data

Excel 2003

Cell Formulas
Range	Formula
C2	=COUNTA(A:A)-1
C4	=INT(C2*0.05)
D2	=COUNTIF(F7:F65533,"Audited")
D4	=INT(D2*0.05)
E2	=COUNTIF(F:F,"Not Checked")
E4	=INT(E2*0.05)
L7	=RAND()
L8	=RAND()
L9	=RAND()
L10	=RAND()
L11	=RAND()
L12	=RAND()
L13	=RAND()
L14	=RAND()
L15	=RAND()
L16	=RAND()
L17	=RAND()
L18	=RAND()
L19	=RAND()
L20	=RAND()
L21	=RAND()
L22	=RAND()
L23	=RAND()
L24	=RAND()
L25	=RAND()
L26	=RAND()

 

[Hairs]

Ok I have got half of this sorted out now.

The data is all coming across perfectly onto the working sheet, and has columns H, I, L completed.
I now need to filter the data by Col. F, and assign the first "x" rows of the data with a certain value in Col K. The problem is that the filtering creates multi-area ranges, and a simple .Range("K7:K" & xrows) = Value doesn't work properly.
If I use Offset to get to "K7 + xrows", can I then use Union("K7:"&xrows)? The MSDN examples all use "Selection.Offset" which is no good to me as nothing will be selected.... Recording a macro doesn't help as I end up with "Selection.AutoFilter Field:=6, Criteria1:="Audited"
Selection.FillDown"

 

[AlphaFrog]

Maybe try something like this to assign a value to a filtered range.

.Range("K7:K" & xrows).SpecialCells(xlCellTypeVisible).Value = MyValue

This will assign the value MyValue to only the visible filtered cells in "K7:K" & xrows

SpecialCells Method

 

[Hairs]

Maybe try something like this to assign a value to a filtered range.

.Range("K7:K" & xrows).SpecialCells(xlCellTypeVisible).Value = MyValue

This will assign the value MyValue to only the visible filtered cells in "K7:K" & xrows

SpecialCells Method

That's still unfortunately only giving me partial results. The Filtering is being done correctly, but I can't get it to fill Column K reliably (best result so far is to get 18 entries, from rows 7-33, when according to my fake data, it should be all filled in rows 7-47.

Even if I sort column F so that once the filter is in place, there are no "multi-area" ranges (ie gaps in row numbers), I still only get 17 entries. I think this one could drive me over the brink!

      .Range("A6:K" & lNumRows).AutoFilter Field:=6, _
        Criteria1:="Audited", Operator:=xlOr, Criteria2:="Checked"
        MsgBox "Filtered Audited"
        .Range("K7:K" & xrows).SpecialCells(xlCellTypeVisible).Value = MyValue
        MsgBox "Staffname Assigned"

 

[AlphaFrog]

What value is xrows? Could that be limiting the range you are setting values to?

Shouldn't it be like this?

.Range("K7:K" & [COLOR="Red"]lNumRows[/COLOR]).SpecialCells(xlCellTypeVisible).Value = MyValue

 

[Hairs]

What value is xrows? Could that be limiting the range you are setting values to?

Shouldn't it be like this?

.Range("K7:K" & [COLOR=Red]lNumRows[/COLOR]).SpecialCells(xlCellTypeVisible).Value = MyValue

No, the xrows was intentional, the idea was that having filtered, I would then fill only a subset of the filtered range. (which is stored in xrows).

I actually have all this part of the code sorted out now. For any newbies in the same predicament, here's what I did, and where I got help:

I used one "Get Data" function which calls Application.FileDialog(msoFileDialogOpen) to bring up the standard file open dialogue box. This is useful, because the file/sheet is going to change on a daily basis, so it can't be hard coded. Then I use an InputBox to get the user to select the last row of today's source sheet. I could use code to get to the last sheet, and use xlEndUp to get to the last row, but in my case I want the user to check the data anyway.

Application.DisplayAlerts = False
    'Get the last Row which is in the sheet
            Set rRange = Application.InputBox(Prompt:= _
                "Please select the Last Row which contains data", _
                    Title:="Get Used Range", Type:=8)

Note that once you have rRange stored, you can use Range.parent properties to get the worksheet name etc if you need them for something else. In my case, I threw up a confirmation dialogue. I then use lLastRow = rRange.Row to get a Long variable with the number of rows I'm going to copy (in my case I had to take account of some headers and padding so lLastRow = lLastRow -4 was the next line.) Now, my copying ranges are Range("a4:a" & lLastRow). I know this is correct because the user verified it, and I don't have to worry about row counting methodologies.

http://www.ozgrid.com/VBA/inputbox.htm

Then, instead of doing a copy, and sorting everything on the master "daily working" sheet, I do the re-ordering as I copy. So, I want the source data to be changed from A B C D E F G to end up on the new sheet as A B E D F G. I copy each column in turn. This is easier than copying A>G and then trying to re-sort it, which was some of the initial advice I looked at.

Next up, I remove any data rows I don't want with help from Ron de Bruin's "autofilter - two delete" method.

(things I learned: It's important to use this method and introduce it as "With ActiveSheet", as my code broke down altogether when I tried to merge it with something else in the same Sub. I also didn't need to store the calculation mode, I just reset it to automatic.)

Now came the bit I thought would be hardest, the sampling. Actually, doing this manually for a while when I was working out the code helped. I knew my criteria for what the sample sizes would be, so what I did was store those in cells at the top of the "working data" sheet using standard Excel formulas. Then I referenced those cells in the VBA code to fill my "how big is the sample" variables.

I tried various methods of working this out, between copying ranges to a second "working" sheet, to using Autofilter again and filling "xrows" number of rows, to using the sampling add in. Eventually, I realised that I can do whatever I want with the working data, the original worksheet's not getting touched. So, I just found the end of the data (that's stored in a cell at the top of the sheet, so I didn't have to do Count(xlEndUp).), and used Copy to fill the first two columns, and .Formula to fill another column with random data for sorting.

lNumRows = Range("c2").Value + 6
lAuditTasks = Range("D4").Value + 6
lUncheckedTasks = Range("E4").Value + 6
MsgBox "Last row is " & lNumRows & ". Audited: " & lAuditTasks & ", Unchecked: " & lUncheckedTasks
Application.ScreenUpdating = False
Range("H7:I7").Copy Range("H7:I" & lNumRows)
Range("L7:L" & lNumRows).Formula = "=rand()"

Now I have all my columns filled, with the exception of the staffname. Next, I have to separate out the two criteria I'm going to sample by.

Range("a6:l" & lNumRows).Sort _
Key1:=ActiveSheet.Range("L6"), _
Order1:=xlAscending, _
Header:=xlYes
Range("a6:l" & lNumRows).Sort _
Key1:=ActiveSheet.Range("F6"), _
Order1:=xlAscending, _
Header:=xlYes
Range("K7:K" & lAuditTasks).Value = sStaffName

Things I learned: It's important to specify there is a header row, and that "sort" treats Row 1 as the header row. (row 6 in my case). Not doing this causes the code to copy and re-order the header row!

Next up on the block: Working out a method to use a VBA function to check the master data against each individual staff member's worksheet, to see what work they've completed, and update the sheet. Currently, I do this using a VLOOKUP but I'm aware that's going to impact performance in the long term. Ideally, I want to have a Command button linked to a macro, which will check the rows for each staff member, check for blanks, and then lookup those rows on the staff member's worksheet, and copy and updated data.