VBA Code...

[MarkHear1]

I have written the following code however i get the error "Type
Mismatch" and the code "C1"
Has anybody got any suggestions as to how i can resolve this problem?

Private Sub Worksheet_Change(ByVal Target As Range)

Call inputs("C1")
Call inputs("C2")
Call inputs("C3")
Call inputs("C4")
Call inputs("C5")
Call inputs("C6")
Call inputs("C7")
Call inputs("C8")
Call inputs("C9")
Call inputs("C10")
Call inputs("C11")
Call inputs("C12")
Call inputs("C13")
Call inputs("C14")
Call inputs("C15")
Call inputs("C16")
Call inputs("C17")
Call inputs("C18")
Call inputs("C19")
Call inputs("C20")
Call inputs("C21")
Call inputs("C22")
Call inputs("C23")
Call inputs("C24")
Call inputs("C25")
Call inputs("C26")
Call inputs("C27")
Call inputs("C28")
Call inputs("C29")
Call inputs("C30")
End Sub

Private Sub inputs(rangeref As Range)
If rangeref.Value = "yes" Then
rangeref.EntireRow.Delete
End Sub


Regards,
Mark

 

[Chip Pearson]

Mark,

Your function "inputs" is defined to accept a Range object as input, but you
are passing in a String value. Change

Call inputs("C1")
to
Call inputs(Range("C1"))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Jim Thomlinson]

"C1" is a text string. You want to send in a range. Try something like this...

Call inputs(Range("C1"))

 

[Joel]

You are trying to pass a range, but passing a string use

Set myRange = Range("C1")
Call inputs(myRange)

or
Call inputs(Range("C1"))

 

[Bob Phillips]

In addition to Chip's response, one question and one suggestion.

Question. Why would this be triggered by Worksheet_Change. This is normally
used to work upon the cell(s) that is changed, not some group of unrelated
cells.

Suggestion. You can simplify this code down to one call and loop in the
called procedure

Private Sub Worksheet_Change(ByVal Target As Range)
Call inputs(Range("C1:C30"))
End Sub

Private Sub inputs(rangeref As Range)
Dim cell As Range
For Each cell In rangeref
If cell.Value = "yes" Then
cell.EntireRow.Delete
End If
Next cell
End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tom Ogilvy]

I think Bob was focused on the initial problem and overlooked the problem
when looping forward when deleting rows. This modification would insure all
the desired rows are deleted.

Private Sub inputs(rangeref As Range)
Dim cell as Range, frow as Long, lrow as Long
Dim i as Long
frow = rangeref(1).row
lrow = rangeref(rangeref.count).row
For i = lrow to frow step -1
set cell = rangeref.parent.cells(i, rangeref.column)
If cell.Value = "yes" Then
cell.EntireRow.Delete
End If
Next i
End Sub

 

[Jim Thomlinson]

Since the code is triggered by the change event and the change event fires
when the row is deleted how about this to clean things up a bit more.
Additionally I have added the lcase function to account for any cpitalization
in the word "yes"...

Private Sub Worksheet_Change(ByVal Target As Range)
Call inputs(Range("C1:C30"))
End Sub

Private Sub inputs(rangeref As Range)
Dim cell As Range, frow As Long, lrow As Long
Dim i As Long
On Error GoTo ErrorHandler

Application.EnableEvents = False
frow = rangeref(1).Row
lrow = rangeref(rangeref.Count).Row
For i = lrow To frow Step -1
Set cell = rangeref.Parent.Cells(i, rangeref.Column)
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Delete
End If
Next i
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[MarkHear1]

Since the code is triggered by the change event and the change event fires
when the row is deleted how about this to clean things up a bit more.
Additionally I have added the lcase function to account for any cpitalization
in the word "yes"...

Private Sub Worksheet_Change(ByVal Target As Range)
Call inputs(Range("C1:C30"))
End Sub

Private Sub inputs(rangeref As Range)
Dim cell As Range, frow As Long, lrow As Long
Dim i As Long
On Error GoTo ErrorHandler

Application.EnableEvents = False
frow = rangeref(1).Row
lrow = rangeref(rangeref.Count).Row
For i = lrow To frow Step -1
Set cell = rangeref.Parent.Cells(i, rangeref.Column)
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Delete
End If
Next i
ErrorHandler:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson







- Show quoted text -

thank you all for your suggestions...
I have now changed the request and if the value is "yes" i want that
row to be copied to sheet 2 can anybody help with this please?