Audit a column then copy data to another sheet 1

[markn82]

Well alrighty then...

I am trying to figure how how to automate a task on a spread-sheet with a macro function and don't quite know how to go about it. Currently we have to copy and paste.

In the lab that I work in, we use a spread sheet to report results of FAIs, the second to the last sheet of which is a listing of just out of tolerance features. I have automated the sheet to show which features are out of tolerance with conditional formatting and I use some true false statements to report "Accept" in one column if the feature is good and by default it keeps "Reject" in the next column if the feature is bad. The report has 4 columns in which to enter numerical values. If any of the four are good, it will show "Accept", if any are bad or blank it shows "reject."

Now that I've really made it confusing...

I want to build a macro that will automatically paste my rejected features to my "Out of Tolerance" page without skipping any rows. For instance, if I have ten rows with no rejects, I want it skip those rows, and also only copy the numeric value that is out of limit.

Im using Excel 2003 BTW.

If anybody has any ideas, please let me know.

 

[francesca]

It's not that difficult a task in VBA. You need to set up one loop to test your conditions that currently report "Accept" or "Reject" and if they're true, then to perform some actions or else go to the next one.

The easiest way to go about this is to record a macro while you do these pastes to your "out of tolerance" page.

You'll need several variables to remember your place on each sheet, but that's no trouble at all.

The hardest part will be getting the loop correct, and the hardest part of that is specifying when to stop looping. Is your data column a fixed length? This will make it easier, because then you can use a "loop until activecell.address = '$B$5'" type statement. If your data column varies in length, then you can loop until your cell is empty, but if you have empty cells in your column and you want to keep going then you'll need to find a creative way of specifying the end point of the loop. It's never insurmountable, sometimes just challenging.

My post at the end of this thread has a commented macro that includes a loop and an If Then Else statement that ought to point you in the right direction.

Assuming you keep your "Accept/Reject" field (which you could actually replace with the macro if you're feeling froggy) then you would set up a loop as follows:

(Paste into a new Module in the VBA window to reveal the handy color coding!)

Dim strTest as string 'set your variable

strTest = range("C3").Value 'where cell C3 is the first in your column of "Accept"/"Reject"

Do
   If strTest = "Accept" Then
      'insert statements for pasting to your other sheet
   ElseIf strTest = "Reject" Then
      'go to next -- i.e. do nothing
   Else
      'something's gone wrong, so abort!
      MsgBox "Oh poo" 'a message pops up to tell you you've had a problem
      Exit Do
   End If

ActiveCell.Offset(1,0).Select 'go to the next cell in the column
'note that Offset(1,0) means offset 1 Row and 0 Columns
strTest = ActiveCell.Value 'this is the easiest way to reset your test string

Loop Until strTest = "" 'loop until your encounter an empty cell in your column.

You'll need an addition variable to set the location on the other sheet, the easiest way to do that is to use the "Address" function. You can extend strings with a + as follows:

str1 = "Sheet1"
str2 = ActiveCell.Address
str3 = "'" + str1 + "'!" + str2

str3 would then contain the string "'Sheet1'!$B$4" assuming the active cell was B4. You can similarly use ActiveSheet.Name to set str1 equal to "Sheet1".

Good luck!

 

[markn82]

Hey, with a couple of mods to make it specific to my spreadsheet, I think this just may work! I'll have to do a little bit of modification to my spreadsheet, but I think this is a winner.

Thanks a lot!