I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data. I
would like to search a column for a particular value and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source sheet,
delete the row and repeat process until the search is no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the features
I need.

Thanks for any and all help,

dave

see if this helps

activecell.entirerow.cut _
sheets("sheet1").range("a1")

"dave" wrote in message
...
I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data. I
would like to search a column for a particular value and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source sheet,
delete the row and repeat process until the search is no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the features
I need.

Thanks for any and all help,

dave

Set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup))

would set a reference to all the data in Column A (the second value in
Cells) of the active sheet.

To work on another sheet that is not the active sheet

With Worksheets("Sheet9")
Set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup))
End with

Now to loop through each cell

Dim rng as Range, rng1 as Range
Dim cell as Range
With Worksheets("Sheet9")
Set rng = .Range(.cells(1,1),.Cells(rows.count,1).End(xlup))
End with

for each cell in rng
if cell.Value = 3 then
if rng1 is nothing then
set rng1 = cell
else
set rng1 = Union(rng1,cell)
end if
Next
if not rng1 is nothing then
rng1.EntireRow.copy Destination:= _
Worksheets("Sheet3").Range("A1")
rng1.EntireRow.ClearContents
' or
' rng1.Entirerow.Delete
End if

If you read some of the posting in this newgroup, you will get good sample
code on how to do a variety of actions in Excel.

Dave McRitchie has some links to Tutorials on Excel and VBA:

http://www.mvps.org/dmcritchie/excel....htm#tutorials

the vba tutorials are after the excel tutorials

Other sources

Chip Pearson's site:
http://www.cpearson.com/excel.htm
look at the pages/topics indexes.

See John Walkenbach's site
http://www.j-walk.com/ss/excel

go to the developer's tips.

--
Regards,
Tom Ogilvy


dave wrote in message
...
I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data. I
would like to search a column for a particular value and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source sheet,
delete the row and repeat process until the search is no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the features
I need.

Thanks for any and all help,

dave

Thanks for the quick response, but...

I know that I have to change "sheet1" to the name of the
sheet I'm cutting from but what does the range("a1")
select? I obviously don't always want row 1 so how do I
make that a variable reference?
dave
-----Original Message-----
see if this helps

activecell.entirerow.cut _
sheets("sheet1").range("a1")

"dave" wrote in message
...
I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data.
I
would like to search a column for a particular value
and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I
can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put
into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source
sheet,
delete the row and repeat process until the search is
no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the
features
I need.

Thanks for any and all help,

dave


.

Actually, Sheets("Sheet1") is the sheet you are cutting to. A1 is the
destination.

The activesheeet, row with the active cell is the row you are cutting from
in the code Don provided.
rw = 10
activecell.entirerow.cut _
sheets("sheet1").range("a1")(rw)

or

rw = 10
activecell.entirerow.cut _
sheets("sheet1").range("a1").offset(rw-1,1)

would cut to row 10 on sheet1.

--
Regards,
Tom Ogilvy



dave wrote in message
...
Thanks for the quick response, but...

I know that I have to change "sheet1" to the name of the
sheet I'm cutting from but what does the range("a1")
select? I obviously don't always want row 1 so how do I
make that a variable reference?
dave
-----Original Message-----
see if this helps

activecell.entirerow.cut _
sheets("sheet1").range("a1")

"dave" wrote in message
...
I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data.
I
would like to search a column for a particular value
and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I
can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put
into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source
sheet,
delete the row and repeat process until the search is
no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the
features
I need.

Thanks for any and all help,

dave


.

There's a lot of different ways to approach this. You
should find a large variety of responses. For me, my
approach has been to use the Cells Method. A lot of
programmers work with the ranges.

For example, this is my style:

Sub Sample()
Dim S1 As Worksheet
Dim i As Integer

Set S1 = Sheets(1)

For i = 1 To 100
If S1.Cells(i, 5).Value = "Sample" Then
MsgBox "The word Sample was found at row " & _
S1.Cells(i, 5).Row & ".", , "Sample Message"
End If
Next i

End Sub

The integer "i" represents the rows, looping from 1 to
100. The integer "5" is the fifth column. You could loop
through the columns too, if you wanted. You set objects,
not variables. The sheets are objects; that's why I could
set it, the way it's written above. "Sheet(1)" is the
first sheet in the workbook.

Now, as you learn to loop through the cells or sheets
looking for conditions, you can loop within the loops,
going up and down, left and right - all sorts of things
that you can dream up of doing.

When you delete rows, you'll want to start at the bottom
and then loop up. Someone told me about that technique
many years ago. I can help you with that, if you are
interested. Let's see, first, what others might provide
for you.

I hope that gets you started.

-----Original Message-----
I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data. I
would like to search a column for a particular value and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source sheet,
delete the row and repeat process until the search is no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the features
I need.

Thanks for any and all help,

dave
.

Dave,

Here's some code that does most of what you want. The trick is to know when
you have not found the item, which is shown here.

This code assumes a few things
- that the range being searched is A1:A100
- that the value being searched for is 1
- that the worksheets are called Sheet1 and Sheet2

All of these can be modified up-front in the code.

Dim oWS1 As Worksheet
Dim oWs2 As Worksheet
Dim cLastRow As Long
Dim oCell As Range
Dim myValue
Dim myRange As Range

myValue = 1
Set oWS1 = Worksheets("Sheet1")
Set oWs2 = Worksheets("Sheet2")
Set myRange = oWS1.Range("A1:A100")
cLastRow = oWs2.Cells(Rows.Count, "A").End(xlUp).Row
If cLastRow = 1 And oWs2.Cells(cLastRow, "A") = "" Then
cLastRow = 0
End If

Set oCell = myRange.Find(myValue)
If Not oCell Is Nothing Then
Do Until oCell Is Nothing
cLastRow = cLastRow + 1
oCell.EntireRow.Cut Destination:=oWs2.Cells(cLastRow, "A")
Set oCell = myRange.FindNext
Loop
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dave" wrote in message
...
I've been a programmer for many years. I can do the
programming if I can figure out how to get at the data.
That's my problem with using VBA with Excel (or Word,
etc.) How do I find out how to access a cell, group of
cells, range, worksheet, etc. using VBA.

For example, I have a worksheet that has rows of data. I
would like to search a column for a particular value and
if it is present, delete the data and row from the
current worksheet and paste it into another sheet. I can
figure out how to search for the data by recording a
macro when I do it manually. But when I then select the
row to so I can do a Ctrl-X to "move" the data, the
recorded macro does a .Select on that specific row. I
need to be able to have the selected row number put into
a variable that I can use to "cut" the data then when I
go to the target sheet I need to have the target row
incremented. Then I want to go back to the source sheet,
delete the row and repeat process until the search is no
longer successful.
How do I do those kind of things? I'm not real familiar
with the data setup that allows me access to the features
I need.

Thanks for any and all help,

dave