Excel Macro code: Could someone proof for me?

gemcgraw

Board Regular
Joined
Mar 11, 2011
Messages
70
Hello - I found the following code on a thread here, but it doesn't do anything. I'd like to get someone to verify it for me. I can't find the original post any longer to contact the person who suggested it to another person. You're help and patience is most appreciated.

Code:
Sub MoveNonDrop()
Dim couNter As Long
Dim RowCount As Long
Application.ScreenUpdating = False
RowCount = Range("B905").End(xlUp).Row
couNter = 1
Do Until couNter > RowCount
If Range("B" & couNter).Value = "DROPPED" Then
Range("B" & couNter).EntireRow.Copy Destination:=Sheets("CURR-FILTER"). _
Range("B905").End(xlUp).Offset(1, -2)
RowCount = RowCount - 1
couNter = couNter - 1
End If
couNter = couNter + 1
Loop
Application.ScreenUpdating = True
End Sub

I have this code in a macro on the "PREV-DROP" worksheet. The destination sheet, as spelled out in the code is for "CURR-FILTER" worksheet. Any reason why this code doesn't error out but doesn't return any action? I assigned Ctrl+Shift+R to the macro... nothing. I even opened the code and ran it in the VB Editor... nothing. It should be copying any row from "PREV-DROP" sheet to "CURR-FILTER" sheet IF the word "DROPPED" does not exist in cells Bx.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
Without knowing what your data looks like, it is hard to say.

What does your data look like?
What colums/rows is it found in?
Does the word "DROPPED" appear in certain cells in column B?
 

gemcgraw

Board Regular
Joined
Mar 11, 2011
Messages
70
I think I see my problem: I have six(6) worksheets in this Workbook. I have a "destination" comment in the code but I'm not telling it what worksheet to analyze. How do I do that? The data on the origination worksheet ("PREV-DROP") is in a range of C2:AE628 (but I'm giving potential future range to AE905). The word "DROPPED" can appear in A2:A905. Here's another kicker... the word "DROPPED" is a result of a formula in that cell. What is the code actually "seeing"? Is it blind to the formula?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
You can select a particular sheet like:
Sheet("SheetName").Activate

Since you are returning the VALUE of the formula, I believe the code referencing the "DROPPED" value should work.
 

gemcgraw

Board Regular
Joined
Mar 11, 2011
Messages
70

ADVERTISEMENT

Arrgh! Okay, I need to start all over. My code is so dirty and cluttered. Nothing is making sense and it is just too muttled. All I need is simple and basic, in a sense: I just need to evaluate the values(produced by a formula in the cell) on sheet123 in the range A2:A905. If it does not = "DROPPED" then move the entire row to sheet456. Nothing more. I'm no coding pro but I am disappointed in myself that I can't figure out the simple block of coding. Maybe I'm trying to make too complex? Any ideas? I've attempted to use suggestion made to me and ideas on here but I'm really hitting the wall.


You can select a particular sheet like:
Sheet("SheetName").Activate

Since you are returning the VALUE of the formula, I believe the code referencing the "DROPPED" value should work.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
I just need to evaluate the values(produced by a formula in the cell) on sheet123 in the range A2:A905. If it does not = "DROPPED" then move the entire row to sheet456. Nothing more.
Then why not just use filters?
With Advanced Filters, you can copy your results to a new range.

Check out Excel's help files for more details on using Filters.
 

gemcgraw

Board Regular
Joined
Mar 11, 2011
Messages
70
Filters have crossed my mind; however, maybe I'm making this too complex. I just need to compare last month's data (sheet1) against this month's data (sheet2). Check if any cells (address, phone, badge no., etc.) on that row have changed. When I tried to compare a row back to last month's sheet and the value (Last,First,M) concatenated in A2:A905 value didn't exist, it crashed. Maybe a more complex formula in a cell?

Then why not just use filters?
With Advanced Filters, you can copy your results to a new range.

Check out Excel's help files for more details on using Filters.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,518
Office Version
  1. 365
Platform
  1. Windows
You could use a series of VLOOKUP functions to compare each field.
Personally, when I need to compare data, I usually use Microsoft Access.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,537
Messages
5,529,424
Members
409,875
Latest member
Khushal Bisht
Top