Code runs different in a commandbutton than a macro why?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
with worksheets("Paid Recievables")
set rng = .cells(.rows.count,1).end(xlup).offset(1,0)
end with
Click to expand...

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

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

or?

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

Don <donwiss at panix.com>.
 
set rng = worksheets("Paid
Recievables").cells(.rows.count,1).end(xlup).offset(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.
Recievables").cells(.rows.count,1).end(xlup).offset(1,0)<<

Will not work. For a range (or any object), you have to use Set to assign a
variable to it.
 
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
 
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.)
 
(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 said:
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.)



Click to expand...
 
Back
Top