p = p + 1 is not working?

[Darkcloud617]

Hopefully a quick questions. The code below works like I need it to, however, it does not seem to copy the row to the other sheet. In fact... excel seems to not do anything about the p = p + 1. I am sure its an easy mistake made but I cannot seem to find the problem. Any ideas on why that may not be happening? Very much appreciated.

Sub Pending(): Dim wp As Worksheet, wa As Worksheet, a As Long, p As String
Set wp = Sheets("Pending"): Set wa = Sheets("Projects"):
p = Application.Max(3, wp.Range("A" & Rows.Count).End(xlUp).Row + 1)
For a = 5 To wa.UsedRange.Rows.Count


If wa.Range("Q" & a) = "Move" Then
wa.Range("A" & a).Resize(1, 19).Value = wa.Range("A" & p).Resize(1, 19).Value
wp.Range("A" & p) = "": p = p + 1:


End If
Next a
End Sub

 

[Joe4]

You declared p as a string, not a number. So you cannot do mathematical computations on it:

p As String

 

[Fluff]

Should this line be

wa.Range("A" & a).Resize(1, 19).Value = [COLOR=#ff0000]wp[/COLOR].Range("A" & p).Resize(1, 19).Value

 

[Darkcloud617]

Thank you for the quick feedback.

I defined p as integer and changed that line per your request Fluff but it still seems to have the same issue. For some reason it refuses to copy the row to the 'Pending' sheet. It still almost seems like excel cannot see the p = p + 1.

Sub Pending(): Dim wp As Worksheet, wa As Worksheet, a As Long, p As Integer
Set wp = Sheets("Pending"): Set wa = Sheets("Projects"):
p = Application.Max(3, wp.Range("A" & Rows.Count).End(xlUp).Row + 1)
For a = 5 To wa.UsedRange.Rows.Count


If wa.Range("Q" & a) = "Move" Then
wa.Range("A" & a).Resize(1, 19).Value = wp.Range("A" & p).Resize(1, 19).Value
wp.Range("A" & a) = "": p = p + 1:


End If
Next a
End Sub

it is supposed to:

1. Copy all cells on activated row
2. erase the cells
3. *paste copied cells to first blank row in 'Pending'

Thank you again, I have played more than I care to say trying to get this working.

 

[Fluff]

If you add this line

p = Application.Max(3, wp.Range("A" & Rows.Count).End(xlUp).Row + 1)
[COLOR=#ff0000]MsgBox p[/COLOR]
For a = 5 To wa.UsedRange.Rows.Count

what does the msgbox return?

 

[Darkcloud617]

It seems to return "3" every time.

Odd, I could have swore the first time I ran that it returned "8" . Maybe my eyesight is going bad.

 

[Fluff]

Ok, first you are trying to copy to the wrong sheet. Try

Sub Pending()
   Dim wp As Worksheet, wa As Worksheet, a As Long, p As Integer
   Set wp = Sheets("New"): Set wa = Sheets("pcode"):
   p = Application.Max(3, wp.Range("A" & Rows.Count).End(xlUp).Row + 1)
   For a = 5 To wa.UsedRange.Rows.Count
      If wa.Range("Q" & a) = "Move" Then
         wp.Range("A" & p).Resize(1, 19).Value = wa.Range("A" & a).Resize(1, 19).Value
         [COLOR=#ff0000]wp.Range("A" & a) = ""[/COLOR]: p = p + 1:
      End If
   Next a
End Sub

Second, what are you trying to do with the part in red?

 

[Darkcloud617]

That line is supposed to be searching 'wa' not 'wp'. I was playing before posting and forgot to change those values back, apologies. It is supposed to blank out the cells instead of deleting or cutting the row.

wp.Range("A" & a) = "": p = p + 1:

1. erase data in row in 'wa'
2. paste that row into 'wp' in first blank row

 

[Fluff]

Change that line to

wa.Rows(a) = ""

& try the code in post#7

 

[Darkcloud617]

You are an absolute genius. It works perfect now.

Just a quick question... Why did the Code wp.Range("A" & a) = "" break the area for p = p + 1 ? If I am thinking about it correctly it should have just searched the range and moved on to p = p + 1.

Sorry if a silly question, I am starting to piece together how to code in VBA and issues like these throw me way off.