I am trying to "reinvent the wheel" on an existing app with buggy
macros.

My main challenge (as I am fairly new to Excel VBA) is to find a way to
evaluate a specific cell within a row and, if it is a match, move that
whole row to a different worksheet within the same workbook. I have to
ensure that if there are multiple instances with this condition that
they are moved to the correct worksheet, no rows are overwritten on the
destination sheet, and that blank rows on the original sheet are
deleted.

There will be a number of keywords in different cells that will be
evaluated and could end up on one of several destination sheets.

For example:

In column 13 for any given row, I am looking for the string "DELETED"
which may or may not have other words in the cell. If it is found, the
entire row should be copied to the "DELETED" worksheet. There are a
variable number of rows each day, but the columns stay constant. Once
all the deleted events are removed, the remaining rows would be
evaluated for occurrences of other strings. Once completed, the ones
that don't match any of the search criteria will remain on the original
sheet for further processing.

The next thing I need to do is divide the remaining rows up as either
value 1 or 2 (left or right). There may be multiple rows with the same
channel value that would all need to be grouped as either 1 or 2;
others may have only one channel assigned.

I appreciate any insight you may have regarding these issues; I hope
this information is specific enough. If not, let me know and I'll be
glad to provide it.


Thanks!

Judy

hi,
what is the specific cell?
what is it matching?
what are the keywords?
how many different cells?
what are the cells?
how many cells are in the row?
how many destinations sheets? names?
what is the name of the source sheet?

-----Original Message-----
I am trying to "reinvent the wheel" on an existing app
with buggy
macros.

My main challenge (as I am fairly new to Excel VBA) is to
find a way to
evaluate a specific cell within a row and, if it is a
match, move that
whole row to a different worksheet within the same
workbook. I have to
ensure that if there are multiple instances with this
condition that
they are moved to the correct worksheet, no rows are
overwritten on the
destination sheet, and that blank rows on the original
sheet are
deleted.

There will be a number of keywords in different cells
that will be
evaluated and could end up on one of several destination
sheets.

For example:

In column 13 for any given row, I am looking for the
string "DELETED"
which may or may not have other words in the cell. If it
is found, the
entire row should be copied to the "DELETED" worksheet.
There are a
variable number of rows each day, but the columns stay
constant. Once
all the deleted events are removed, the remaining rows
would be
evaluated for occurrences of other strings. Once
completed, the ones
that don't match any of the search criteria will remain
on the original
sheet for further processing.

The next thing I need to do is divide the remaining rows
up as either
value 1 or 2 (left or right). There may be multiple rows
with the same
channel value that would all need to be grouped as either
1 or 2;
others may have only one channel assigned.

I appreciate any insight you may have regarding these
issues; I hope
this information is specific enough. If not, let me know
and I'll be
glad to provide it.


Thanks!

Judy

.

Name one of your sheet tabs Source and another Target for this code to work
properly.

I have assumed that the data in the source worksheet column is sequential
and has no blank rows. A loop checks the value of each cell in column A in
the source worksheet and if the value contains the word "Deleted" it cuts the
row and moves it to the next available row in the work sheet named Target the
loop stops when a blank cell is encountered

Code Starts on the with the word sub (all lines preceded with an apostrophe
indicate a comment line in the code)

Sub DeleteMe()

Dim strVal As String 'Holds the value of the current cell
Dim varArray As Variant 'Holds the parsed value of the current cell
Dim lTargetRow As Long 'The the next blank row in target sheet
Dim lSourceRow As Long 'The row containing the matching word
Dim strSourceRow As String 'The row address as a string
Dim wkTarget As Worksheet 'The target worksheet
Dim wkSource As Worksheet 'The source worksheet
Dim strRowNum As String 'Row number string for target range
Dim strRange As String 'Concatenated target range

strVal = ActiveCell.Value 'Assigns current cell value to
variable
Set wkTarget = Worksheets("Target") 'Sets the target worksheet variable
Set wkSource = Worksheets("Source") 'sets the source worksheet variable

'Loops until it encounters a blank cell

Do Until strVal = ""
'The current cells value is parsed and stored in an array
varArray = Split(strVal)

'for each individual item in the array, the word deleted
'is looked for. If found the row is selected and moved to the
'next available target row

For Each Item In varArray
If Trim$(Item) = "deleted" Then
'increments the next target row to lTargetRow
'stores teh current row in the source to lSourcRow
lTargetRow = lTargetRow + 1
lSourceRow = ActiveCell.Row
'Changes the status bar to indicate which row is moving to
'the target worksheet
Application.StatusBar = "Now moving row " & lSourceRow
'Converts the long interger row values to the source
'and target string rows
'Cuts the current row in the source worksheet
'Activates the target worksheet
'Selects the next available blank row
'Pastes cut data
'moves back to source worksheet and resumes the loop
strSourceRow = Trim$(Str(lSourceRow))
strSourceRow = strSourceRow & ":" & strSourceRow
strRowNum = Trim$(Str(lTargetRow))
strRange = "A" & strRowNum
Rows(strSourceRow).Select
Selection.Cut
wkTarget.Activate
Range(strRange).Select
ActiveSheet.Paste
wkSource.Activate
End If
Next
ActiveCell.Offset(1).Select
strVal = ActiveCell.Value
Loop
'Resets the status bar to nothing
Application.StatusBar = False

'Removes the object variable values from memory
Set wkTarget = Nothing
Set wkSource = Nothing

End Sub


"Judy" wrote:

I am trying to "reinvent the wheel" on an existing app with buggy
macros.

My main challenge (as I am fairly new to Excel VBA) is to find a way to
evaluate a specific cell within a row and, if it is a match, move that
whole row to a different worksheet within the same workbook. I have to
ensure that if there are multiple instances with this condition that
they are moved to the correct worksheet, no rows are overwritten on the
destination sheet, and that blank rows on the original sheet are
deleted.

There will be a number of keywords in different cells that will be
evaluated and could end up on one of several destination sheets.

For example:

In column 13 for any given row, I am looking for the string "DELETED"
which may or may not have other words in the cell. If it is found, the
entire row should be copied to the "DELETED" worksheet. There are a
variable number of rows each day, but the columns stay constant. Once
all the deleted events are removed, the remaining rows would be
evaluated for occurrences of other strings. Once completed, the ones
that don't match any of the search criteria will remain on the original
sheet for further processing.

The next thing I need to do is divide the remaining rows up as either
value 1 or 2 (left or right). There may be multiple rows with the same
channel value that would all need to be grouped as either 1 or 2;
others may have only one channel assigned.

I appreciate any insight you may have regarding these issues; I hope
this information is specific enough. If not, let me know and I'll be
glad to provide it.


Thanks!

Judy

Kevin:

Your assumption is correct. There are 16 columns and a variable number
of rows each day. Question: if their are multiple target worksheets (a
different one for each condition), how do I specify which one? Also, I
think all values will be text and not numeric...this should work just
fine?

Judy