Code runs different in a commandbutton than a macro why?
 

ok here is my code it backs up a record to another sheet

Private Sub CommandButton2_Click()

For Each c In Worksheets("ART Report").Range("P3:P10")
If c.Value = "p" Then
c.EntireRow.Copy
Sheets("Paid Recievables").Select
Set rng = Cells(Rows.Count, 1).End(xlUp)
Rows(rng.Row + 1).Select
Selection.Insert Shift:=x1Down
End If
Next

End Sub

and the the macro I first created that works:

Sub Pselect()
'
' Macro1 Macro
' Macro recorded 3/12/2003 by me
'
For Each c In Worksheets("ART Report").Range("P1:P10")
If c.Value = "p" Then
c.EntireRow.Copy
Sheets("Paid Recievables").Select
Set rng = Cells(Rows.Count, 1).End(xlUp)
Rows(rng.Row + 1).Select
Selection.Insert Shift:=x1Down
End If
Next
End Sub

also I am trying to use this bit of code to set of the range of the for statement "Set rng = Cells(Rows.Count, 1).End(xlUp)" but have not got it to work as of yet?

any help is greatly appreciated

Code runs different in a commandbutton than a macro why?
 

My bet is your recorded macro is still in a General module, but the
commandbutton2_click procedure is behind a worksheet.

If you refer to an unqualified range in a general module, it means the range on
the active sheet. But references to an unqualified range behind the worksheet
refers to the sheet holding the code.

Option Explicit
Private Sub CommandButton2_Click()

Dim Rng as range
dim c as range

with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with

For Each c In Worksheets("ART Report").Range("P3:P10")
If lcase(c.Value) = "p" Then
c.EntireRow.Copy _
destination:=rng
set rng = rng.offset(1,0) 'for row to copy
end if
Next

End Sub

And watch out. Some day you might fix the spelling of Recievables on the
worksheet tab and your macro will break.

I added some Dim statements, too. And changed the check to look for upper/lower
case.

Neal Miller wrote:

ok here is my code it backs up a record to another sheet

Private Sub CommandButton2_Click()

For Each c In Worksheets("ART Report").Range("P3:P10")
If c.Value = "p" Then
c.EntireRow.Copy
Sheets("Paid Recievables").Select
Set rng = Cells(Rows.Count, 1).End(xlUp)
Rows(rng.Row + 1).Select
Selection.Insert Shift:=x1Down
End If
Next

End Sub

and the the macro I first created that works:

Sub Pselect()
'
' Macro1 Macro
' Macro recorded 3/12/2003 by me
'
For Each c In Worksheets("ART Report").Range("P1:P10")
If c.Value = "p" Then
c.EntireRow.Copy
Sheets("Paid Recievables").Select
Set rng = Cells(Rows.Count, 1).End(xlUp)
Rows(rng.Row + 1).Select
Selection.Insert Shift:=x1Down
End If
Next
End Sub

also I am trying to use this bit of code to set of the range of the for statement "Set rng = Cells(Rows.Count, 1).End(xlUp)" but have not got it to work as of yet?

any help is greatly appreciated

--

Dave Peterson

Code runs different in a commandbutton than a macro why?
 

Thanks, I had not even caught the misspelled word thanks for the help.

so if I wanted to parse the workbook for used cell to set the range here

For Each c In Worksheets("ART Report").Range("P3:P10")

would I use a simalar code as

with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with

New to VBA and VB what is a good learning source on the web?

Neal Miller

Code runs different in a commandbutton than a macro why?
 

On Sat, 06 Dec 2003 10:13:35 -0600, Dave Peterson wrote:

with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with

What do you gain by using those three lines instead of?

set rng = worksheets("Paid Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

or?

rng = worksheets("Paid Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

Don <donwiss at panix.com.

Code runs different in a commandbutton than a macro why?
 

set rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)<<

Should be:

Set rng = Worksheets("Paid
Recievables").Cells(Rows.Count,1).End(xlUp).Offset (1,0)

No period before Rows, otherwise your syntax will fail.

rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)<<

Will not work. For a range (or any object), you have to use Set to assign a
variable to it.

--

Vasant


"Don Wiss" wrote in message
...
On Sat, 06 Dec 2003 10:13:35 -0600, Dave Peterson wrote:

with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with

What do you gain by using those three lines instead of?

set rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

or?

rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

Don <donwiss at panix.com.

Code runs different in a commandbutton than a macro why?
 

Dav

I actually want to insert this row into the Paid Receivables work sheet and I want to pasrse the Art Report sheet to determine the range. I will post my effort toward this shortly I hope

Neal
----- Dave Peterson wrote: ----

My bet is your recorded macro is still in a General module, but th
commandbutton2_click procedure is behind a worksheet

If you refer to an unqualified range in a general module, it means the range o
the active sheet. But references to an unqualified range behind the workshee
refers to the sheet holding the code

Option Explici
Private Sub CommandButton2_Click(

Dim Rng as range
dim c as rang

with worksheets("Paid Recievables"
set rng = .cells(.rows.count,1).end(xlup).offset(1,0
end wit

For Each c In Worksheets("ART Report").Range("P3:P10"
If lcase(c.Value) = "p" The
c.EntireRow.Copy
destination:=rn
set rng = rng.offset(1,0) 'for row to cop
end i
Nex

End Su

And watch out. Some day you might fix the spelling of Recievables on th
worksheet tab and your macro will break

I added some Dim statements, too. And changed the check to look for upper/lowe
case

Neal Miller wrote
ok here is my code it backs up a record to another shee
Private Sub CommandButton2_Click(
For Each c In Worksheets("ART Report").Range("P3:P10"
If c.Value = "p" The
c.EntireRow.Cop
Sheets("Paid Recievables").Selec
Set rng = Cells(Rows.Count, 1).End(xlUp
Rows(rng.Row + 1).Selec
Selection.Insert Shift:=x1Dow
End I
Nex
End Su
and the the macro I first created that works
Sub Pselect(

' Macro1 Macr
' Macro recorded 3/12/2003 by m

For Each c In Worksheets("ART Report").Range("P1:P10"
If c.Value = "p" The
c.EntireRow.Cop
Sheets("Paid Recievables").Selec
Set rng = Cells(Rows.Count, 1).End(xlUp
Rows(rng.Row + 1).Selec
Selection.Insert Shift:=x1Dow
End I
Nex
End Su
also I am trying to use this bit of code to set of the range of the for statement "Set rng = Cells(Rows.Count, 1).End(xlUp)" but have not got it to work as of yet
any help is greatly appreciate

--

Dave Peterso

Code runs different in a commandbutton than a macro why?
 

After the correction posted by Vasant, there's no difference. But I like that
style.

If I did this, it would look cleaner:

Dim rng As Range
Dim x As Long
Dim y As Long
Dim z As Long
Dim w As Long
x = 3: y = 5: z = 10: w = 15 'just numbers for testing

With Worksheets("Paid recievables")
Set rng = .Range(.Cells(x, y), .Cells(z, w))
End With

than

Set rng = Worksheets("paid recievables").Range _
(Worksheets("paid recievables").Cells(x, y), _
Worksheets("paid recievables").Cells(z, w))

And I find I'm a lot less likely to leave an unqualified range this way.

(Yeah, yeah. I don't need to qualify rows.count, but I like to.)



Don Wiss wrote:

On Sat, 06 Dec 2003 10:13:35 -0600, Dave Peterson wrote:

with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with

What do you gain by using those three lines instead of?

set rng = worksheets("Paid Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

or?

rng = worksheets("Paid Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

Don <donwiss at panix.com.

--

Dave Peterson

Code runs different in a commandbutton than a macro why?
 

(Yeah, yeah. I don't need to qualify rows.count, but I like to.)<<

And so your code won't break when the next Excel version allows different
worksheets to have different total rows! <vbg

--

Vasant



"Dave Peterson" wrote in message
...
After the correction posted by Vasant, there's no difference. But I like
that
style.

If I did this, it would look cleaner:

Dim rng As Range
Dim x As Long
Dim y As Long
Dim z As Long
Dim w As Long
x = 3: y = 5: z = 10: w = 15 'just numbers for testing

With Worksheets("Paid recievables")
Set rng = .Range(.Cells(x, y), .Cells(z, w))
End With

than

Set rng = Worksheets("paid recievables").Range _
(Worksheets("paid recievables").Cells(x, y), _
Worksheets("paid recievables").Cells(z,
w))

And I find I'm a lot less likely to leave an unqualified range this way.

(Yeah, yeah. I don't need to qualify rows.count, but I like to.)



Don Wiss wrote:

On Sat, 06 Dec 2003 10:13:35 -0600, Dave Peterson
wrote:

with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with

What do you gain by using those three lines instead of?

set rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

or?

rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offse t(1,0)

Don <donwiss at panix.com.

--

Dave Peterson