| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code...
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
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) "MarkHear1" wrote in message oups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
"C1" is a text string. You want to send in a range. Try something like this...
Call inputs(Range("C1")) -- HTH... Jim Thomlinson "MarkHear1" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
You are trying to pass a range, but passing a string use
Set myRange = Range("C1") Call inputs(myRange) or Call inputs(Range("C1")) "MarkHear1" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
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) "MarkHear1" wrote in message oups.com... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
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 -- Regards, Tom Ogilvy "Bob Phillips" wrote: 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) "MarkHear1" wrote in message oups.com... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Bob Phillips" wrote: 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) "MarkHear1" wrote in message oups.com... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code...
On 23 Feb, 17:11, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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 "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Bob Phillips" wrote: 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) "MarkHear1" wrote in message roups.com... 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- Hide quoted text - - 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
| Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
| Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
| Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
| stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
Linear Mode
