Macro If Then...?
 

I have a list of data that I want to filter. However, the last column on the
sheet, if populated, indicates a certain type of investment security that I
want to separate from the list. How can I write a macro that wil cut & copy
each row that has this field populated?

I was thinking If...then, but I am still very new at writing Macros...I'll
be working on it, and any help would be greatly appreciated!!!

Charles

Macro If Then...?
 

Ok...I have an update for those of you who might be interested in helping
out. I have written the following macro using If...Then:

Sub IfThen()

If Range("M4") 0 Then Range("A4:M4").Select
Selection.Cut
Sheets("JP Morgan - VRDNs").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

This will cut the entire row out of the sheet if the Range "M4" is populated
and paste it into the tab "JP Morgan - VRDNs." However, the list is of
securities is about 500 hundred rows long. Instead of writing the same macro
above 500 times, is there a more efficient way to go about writing it?


"CJLuke" wrote:

I have a list of data that I want to filter. However, the last column on the
sheet, if populated, indicates a certain type of investment security that I
want to separate from the list. How can I write a macro that wil cut & copy
each row that has this field populated?

I was thinking If...then, but I am still very new at writing Macros...I'll
be working on it, and any help would be greatly appreciated!!!

Charles

Macro If Then...?
 

charles -
yes, you can do this with looping.
i don't have time right now to write & test it, but if you search the
newsgroup for "looping cut & paste" i bet you'll find lots of
examples.

the UNTESTED PSEUDOCODE to get you started might be:

Sub IfThen()

dim rRange as range
dim myColumn as range
dim lastrow as long
dim myrow as long
dim ws as worksheet
dim ws1 as worksheet
dim cell as range

set ws as workbook.activesheet
set ws1 as workbook.worksheet("JP Morgan - VRDNs")

'this finds the last cell with info in it in column a
set lastrow = cells(20000,1).end(xlup).row

set rrange = ws.range("a1:m" & lastrow)
set mycolumn = ws.range("m:m")

for each cell in mycolumn
If cell < 0 Then
'do nothing
else
set myrow = cell.row
ws.Range("A:M" & myrow).cut
set myrow = ws1.cells(20000,1).end(xlup).offset(1,0)
ws.range("a" & myrow).paste
end if
next cell

End Sub

hope this gets you started! & search the newsgroup - it really helps!
susan


On Mar 21, 10:46 am, CJLuke wrote:
Ok...I have an update for those of you who might be interested in helping
out. I have written the following macro using If...Then:

Sub IfThen()

If Range("M4") 0 Then Range("A4:M4").Select
Selection.Cut
Sheets("JP Morgan - VRDNs").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

This will cut the entire row out of the sheet if the Range "M4" is populated
and paste it into the tab "JP Morgan - VRDNs." However, the list is of
securities is about 500 hundred rows long. Instead of writing the same macro
above 500 times, is there a more efficient way to go about writing it?



"CJLuke" wrote:
I have a list of data that I want to filter. However, the last column on the
sheet, if populated, indicates a certain type of investment security that I
want to separate from the list. How can I write a macro that wil cut & copy
each row that has this field populated?

I was thinking If...then, but I am still very new at writing Macros...I'll
be working on it, and any help would be greatly appreciated!!!

Charles- Hide quoted text -

- Show quoted text -

Macro If Then...?
 

Thanks for your response Susan; however, I have some questions:

First, how does the Cells(20000, 1).End(xlUp).Row code work?

Second, can you please take a look at the Macro I wrote based on your
recommendation:

Sub Test()

Dim MyColumn As Range
Dim MyRow As Range
Dim Cell As Range

Set MyColumn = Sheets("JP Morgan - Municipal").Range(Cells(4, 13),
Cells(500,13))

For Each Cell In MyColumn
If cell 0 Then _

Set MyRow = cell.Row
Sheets("JP Morgan - Municipal").Range("A:M" & MyRow).Cut

Set MyRow = Sheets("JP Morgan - VRDNs").Cells(20000,
1).End(xlUp).Offset(1, 0)
Sheets("JP Morgan - VRDNs").Range("a" & MyRow).Paste

End If
Next cell

End Sub

I keep getting a compile error at the following row:

Set MyRow = Cell.Row

Any help would be appreciated...Thanks!

"Susan" wrote:

charles -
yes, you can do this with looping.
i don't have time right now to write & test it, but if you search the
newsgroup for "looping cut & paste" i bet you'll find lots of
examples.

the UNTESTED PSEUDOCODE to get you started might be:

Sub IfThen()

dim rRange as range
dim myColumn as range
dim lastrow as long
dim myrow as long
dim ws as worksheet
dim ws1 as worksheet
dim cell as range

set ws as workbook.activesheet
set ws1 as workbook.worksheet("JP Morgan - VRDNs")

'this finds the last cell with info in it in column a
set lastrow = cells(20000,1).end(xlup).row

set rrange = ws.range("a1:m" & lastrow)
set mycolumn = ws.range("m:m")

for each cell in mycolumn
If cell < 0 Then
'do nothing
else
set myrow = cell.row
ws.Range("A:M" & myrow).cut
set myrow = ws1.cells(20000,1).end(xlup).offset(1,0)
ws.range("a" & myrow).paste
end if
next cell

End Sub

hope this gets you started! & search the newsgroup - it really helps!
susan


On Mar 21, 10:46 am, CJLuke wrote:
Ok...I have an update for those of you who might be interested in helping
out. I have written the following macro using If...Then:

Sub IfThen()

If Range("M4") 0 Then Range("A4:M4").Select
Selection.Cut
Sheets("JP Morgan - VRDNs").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

This will cut the entire row out of the sheet if the Range "M4" is populated
and paste it into the tab "JP Morgan - VRDNs." However, the list is of
securities is about 500 hundred rows long. Instead of writing the same macro
above 500 times, is there a more efficient way to go about writing it?



"CJLuke" wrote:
I have a list of data that I want to filter. However, the last column on the
sheet, if populated, indicates a certain type of investment security that I
want to separate from the list. How can I write a macro that wil cut & copy
each row that has this field populated?

I was thinking If...then, but I am still very new at writing Macros...I'll
be working on it, and any help would be greatly appreciated!!!

Charles- Hide quoted text -

- Show quoted text -

Macro If Then...?
 

the end(xlup) code works by......... i didn't know how many rows your
range is going to be at any given time - it may vary - may grow bigger
or smaller. this works by going down to row 20000, column a and then
does the same thing as control+up, which finds the last cell in your
range. many times you can't start @ the top & go end(xldown) because
if there are blanks, it will stop @ the first blank. so it's best to
go from the bottom up.

the code:

myrow has to be Dim myRow as LONG because it will return a NUMBER.
see, if my range was a1:g500, the end(xlup) will stop on a500, and by
adding .row to it, as i did, you find out that the last row in your
range (the #, not the address) is 500.

as how you're setting mycolumn, i guess that works, but if you ever
add or subtract data, that will be outdated & be wrong. it's better
to say

Set MyColumn = Sheets("JP Morgan - Municipal").Range("m4:m" &
mylastrow)
not only that, it's easier to understand when you look at it, instead
of referencing column 13 (which made me have to go & look @ a
spreadsheet to see what the 13th column is). plus, by using
mylastrow, you're covered if you ever add an additional 100 rows to
your data. your choice.

if cell 0 then TAKE OUT THE UNDERLINE HERE

Set MyRow = cell.Row
Sheets("JP Morgan - Municipal").Range("A:M" & MyRow).Cut

Set MyRow = Sheets("JP Morgan - VRDNs").Cells(20000,
1).End(xlUp).Offset(1, 0)
Sheets("JP Morgan - VRDNs").Range("a" & MyRow).Paste

you can't use myrow in 2 different cells, changing it in the middle of
what you're doing.
use the first one (cutting) as myrow & the second one (pasting) as
mynewrow.
then vba will understand they're two distinct, different places.

at the moment you are getting a compile error because you have myrow
dimmed as a range. change it to a long & see if that works.... you
may have to take out the "set".

also, this: Set MyRow = Sheets("JP Morgan - VRDNs").Cells(20000,
1).End(xlUp).Offset(1, 0) should have .row tacked onto the end of it

you're getting there!
susan


On Mar 21, 3:14 pm, CJLuke wrote:
Thanks for your response Susan; however, I have some questions:

First, how does the Cells(20000, 1).End(xlUp).Row code work?

Second, can you please take a look at the Macro I wrote based on your
recommendation:

Sub Test()

Dim MyColumn As Range
Dim MyRow As Range
Dim Cell As Range

Set MyColumn = Sheets("JP Morgan - Municipal").Range(Cells(4, 13),
Cells(500,13))

For Each Cell In MyColumn
If cell 0 Then _

Set MyRow = cell.Row
Sheets("JP Morgan - Municipal").Range("A:M" & MyRow).Cut

Set MyRow = Sheets("JP Morgan - VRDNs").Cells(20000,
1).End(xlUp).Offset(1, 0)
Sheets("JP Morgan - VRDNs").Range("a" & MyRow).Paste

End If
Next cell

End Sub

I keep getting a compile error at the following row:

Set MyRow = Cell.Row

Any help would be appreciated...Thanks!



"Susan" wrote:
charles -
yes, you can do this with looping.
i don't have time right now to write & test it, but if you search the
newsgroup for "looping cut & paste" i bet you'll find lots of
examples.

the UNTESTED PSEUDOCODE to get you started might be:

Sub IfThen()

dim rRange as range
dim myColumn as range
dim lastrow as long
dim myrow as long
dim ws as worksheet
dim ws1 as worksheet
dim cell as range

set ws as workbook.activesheet
set ws1 as workbook.worksheet("JP Morgan - VRDNs")

'this finds the last cell with info in it in column a
set lastrow = cells(20000,1).end(xlup).row

set rrange = ws.range("a1:m" & lastrow)
set mycolumn = ws.range("m:m")

for each cell in mycolumn
If cell < 0 Then
'do nothing
else
set myrow = cell.row
ws.Range("A:M" & myrow).cut
set myrow = ws1.cells(20000,1).end(xlup).offset(1,0)
ws.range("a" & myrow).paste
end if
next cell

End Sub

hope this gets you started! & search the newsgroup - it really helps!
susan

On Mar 21, 10:46 am, CJLuke wrote:
Ok...I have an update for those of you who might be interested in helping
out. I have written the following macro using If...Then:

Sub IfThen()

If Range("M4") 0 Then Range("A4:M4").Select
Selection.Cut
Sheets("JP Morgan - VRDNs").Select
Range("A4").Select
ActiveSheet.Paste

End Sub

This will cut the entire row out of the sheet if the Range "M4" is populated
and paste it into the tab "JP Morgan - VRDNs." However, the list is of
securities is about 500 hundred rows long. Instead of writing the same macro
above 500 times, is there a more efficient way to go about writing it?

"CJLuke" wrote:
I have a list of data that I want to filter. However, the last column on the
sheet, if populated, indicates a certain type of investment security that I
want to separate from the list. How can I write a macro that wil cut & copy
each row that has this field populated?

I was thinking If...then, but I am still very new at writing Macros...I'll
be working on it, and any help would be greatly appreciated!!!

Charles- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Macro If Then...?
 

going home now..... be back tomorrow! :)
susan

Macro If Then...?
 

Thanks so much for your help, Susan...I still haven't got it, but I will keep
trying and the information you gave me is great. I appreciate it...

Charles

"Susan" wrote:

going home now..... be back tomorrow! :)
susan

Macro If Then...?
 

well, my explanations weren't the best, but maybe they were good
enough.
& also, i can't stress searching the newsgroup enough - you can type
in a simple thing like "end(xlup)" & find a ton of examples &
explanations..... you can copy & paste other people's code into yours
& see where they differ or why their's works when yours didn't.
susan


On Mar 22, 9:19 am, CJLuke wrote:
Thanks so much for your help, Susan...I still haven't got it, but I will keep
trying and the information you gave me is great. I appreciate it...

Charles



"Susan" wrote:
going home now..... be back tomorrow! :)
susan- Hide quoted text -

- Show quoted text -