Delete row of data on worksheet

Hi. I'm stuck near the last line of code. All of the rest works great but I can't get the syntax right to delete a row of data on the line: Active sheet.rows(Sres).Delete Shift:= shiftxlUp

When I step through it I get the error "Type Mismatch

Any help will be appreciated

THANKS

Sub DeleteSortedItem()

Dim Sitem
Dim RC As String
Dim SheetNum As StringShift:
Dim Rng As String
Dim rg As Range
Dim Sres




        Sheets("VALUES for MEALS").Activate
            Rng = Range("A65536").End(xlUp).Offset(4, 7)
            SheetNum = Rng
            'is the variable that holds the line number to be deleted
            
            RC = Range("A" & SheetNum)
            'is the variable that holds data in col "A"
        MsgBox Range("A" & SheetNum) 'this equalled "TEST" in col "A"
            
            ActiveSheet.Rows(SheetNum).Delete Shift:=xlShiftUp
        MsgBox RC 'This also equalled "TEST" in Col "A"
        
            'This block selects the line# to delete & saves data in variable RC _
            to compare with all category sheets (ie: calories, etc.) for deletion
            
            
           
            'Sitem = Sheets("VALUES for MEALS").Range("RC")
            Sitem = RC
            Set rg = Sheets("Calories").Range("A4:A600")
            Sheets("Calories").Activate
            Sres = rg.Find(Sitem)
            
        If Sres = RC Then
           Result = MsgBox("You are about to delete" & vbNewLine & "Meal Item..." & "*" & RC & "*" _
           & vbNewLine & vbNewLine & "To Continue Click  YES" & vbNewLine & "To Abort Click        NO", vbYesNo + vbExclamation, _
           "Delete Meal Item Box")
        
        MsgBox Sres 'Shows up as Test
        'MsgBox Sres.Address  'Shows up as A219
        
            If Result = vbYes Then
                ActiveSheet.Rows(Sres).Delete Shift:=xlShiftUp
            
                MsgBox "You just deleted" & vbNewLine & "Meal Item...." & RC
            Else
                MsgBox "No Problemo" & vbNewLine & "Just click 'OK' (below)" & vbNewLine & "and Try Again"
            End If
        End If
   End Sub

Hi Carim. I found an error in the code above the line I was having problem with, so I corrected the code accordingly, but I'm still getting the error message: "Type Mismatch" on the same line of code. Here s the amended code:

Thanks a ton

Jimmy

Sub DeleteSortedItem()

Dim Sitem
Dim RC As String
Dim SheetNum As String
Dim Rng As String
Dim rg As Range
Dim Sres
Dim RCa




        Sheets("VALUES for MEALS").Activate
            Rng = Range("A65536").End(xlUp).Offset(4, 7)
            SheetNum = Rng
            'is the variable that holds the line number to be deleted
            
            RC = Range("A" & SheetNum)
            RCa = RC
            'is the variable that holds data in col "A"
        MsgBox Range("A" & SheetNum) 'this equalled "Test" in col "A"
        MsgBox RC
        MsgBox RCa
            ActiveSheet.Rows(SheetNum).Delete Shift:=xlShiftUp
        MsgBox RCa 'This also equalled "Test" in Col "A"
        
            'This block selects the line# to delete & saves data in variable RC _
            to compare with all category sheets (ie: calories, etc.) for deletion
            
            
           
            'Sitem = Sheets("VALUES for MEALS").Range("RC")
            Sitem = RCa
            Set rg = Sheets("Calories").Range("A4:A600")
            Sheets("Calories").Activate
            Set Sres = rg.Find(Sitem)
            
        If Sres = RCa Then
           Result = MsgBox("You are about to delete" & vbNewLine & "Meal Item..." & "*" & RC & "*" _
           & vbNewLine & vbNewLine & "To Continue Click  YES" & vbNewLine & "To Abort Click        NO", vbYesNo + vbExclamation, _
           "Delete Meal Item Box")
        
        MsgBox Sres 'Shows up as Test
        
            If Result = vbYes Then
                ActiveSheet.Rows(Sres & ":" & Sres).Delete Shift:=xlShiftUp
            
                MsgBox "You just deleted" & vbNewLine & "Meal Item...." & RC
            Else
                MsgBox "No Problemo" & vbNewLine & "Just click 'OK' (below)" & vbNewLine & "and Try Again"
            End If
        End If
   End Sub

Hey

I changed the variables to your suggestion and I get Type mismatch on each of them designated as 'Long', when I step through the program

Yeah, I get it. I don't know how you guys do any of the help, with the limited information you're given. (especially from me, I guess).

As you can tell my nemesis it assigning types to my variables. That's why I let the programs assign as variants. I've read that it's not a good way to go, but should work anyway. I've tried to learn the types, but it seems like most of the time the assignment faults out, somewhere down the line. I just can't seem to grasp the logic in it.

I don't know if this will help but here is more information on my variables...

I very much appreciate any help that you're giving me.

Rng finds the value in sheet VALUES for MEALS in a specific cell that houses the line # to delete

SheetNum is the variable where Rng is stored

RC finds the cell location A & SheetNum where text is stored

RCa is the variable where RC is stored

Sitem = RCa

rg sets the range to search text and match for similarity to RCa

Sres is assigned to the .Find(Sitem) command

Hi Carim

Honestly, I tried what you suggested and due to my experience, (or lack there of), I've screwed it up, to beyond recognition...LOL. I feel that this should be soooo easy, but I can't seem to accomplish this task. This sub is one of the last of my problems, that I've muddled through over the last year, to build this program. I can finally see daylight! Another being a problem with my CDO email that 'Uncle Stringer" & 'DaveExcel' is helping me with. (I believe that you were my mentor & provided that code.)

I've attached a copy, which has just the 2 sheets that are the focus of my problem.JimmyB Trial.xlsm

What I'm trying to do is this:

I have a master sheet -"VALUES for MEALS" where row of meal item & categories go.

The user is allowed to add meal rows into the master sheet

It is then automatically sorted into the category sheets, ie: Calories, Total fat, etc..

That's cool..I got that down very well.

Need to make it possible to remove unwanted items. which I've uploaded into it,

by placing the line # in the box provided.

I will create a 'form control' button to activate the sub.

The sub needs to identify the line number IE: 220

then go to the line #... then check column "A" and store the text IE: "Test"

Then delete the row IE: 220 where the text "Test" is located

Then go to the "Calories" sheet

Search column "A" for the text "Test"

and delete that row

That's it. Simple right?

If you can help me with the syntax I can copy and adjust it to all of the other categories

IE: Cholesterol, Sodium, etc...

Thanks Again Carim...very much appreciate it

Oh yeah,

The sub that I worked on is trashed...best to pull up the most recent that I sent...Sorry

Yeah Carim. All of the sheets are identical and driven by the Values for Meals sheet. I must have deleted a few while trying different things. I'll double check them before i finalize.

The easy solution cannot work. The main sheet is what populates the Meals Data sheet. When the user picks a food item, it automatically goes to a daily consumption list, on a different sheet. It then calculates the total amount of calories, transfat, sodium etc., transfers the data to a table which in turn creates charts. Worked hard to get that going and don't want to change it. That is why I want to use the row numbers.

I didn't mean to send you all of the code, for the complete workbook...as rudimentary, (self taught), as it is, It's a secret...LOL. Planning to go public in the spring. I struggled all year to develop it...Hope you're as honest as you seem. LOL. I'd hate to put it out there and get sued because someone else claimed it.

Sooooo back to the drawing board? Appreciate it

Hi Carim

I mentioned, (in error), on my last thread, that I couldn't use the double click method that you suggested, but I was wrong. I have my original event setup, so it would not activate unless I activated it, using the (meal item selector) form control. Therefore your easy method works perfectly. I just added it below the original event and tweaked the sheet #'s to match mine. WORKS GREAT! and I love it...so easy for the user.

The only issue I now have is that it must preform the same calculations on the next 8 sheets. So I need it to search and delete accordingly on sheets 13 through 21 (Inclusive). (VALUES for MEALS being Sheet 12). I was going to copy the calculation and paste another 8 times and adjust. I'm sure there's a better way to incorporate it into your code. Can you help me with that?

Thanks Jimmy

BTW I don't think that I did send you all of the subs by accident. I must have had my program up & it picked them up from that.

Also, if you are helping me on the request from the last thread, can you also insert something. so that if an item match is not found on any of the sheets, it doesn't give an error? It just continues to the next sheet

Sorry Carim

I didn't see your response before I sent the last thread.

I had to change line 11 from the variable "i" to the actual sheet name. Shouldn't it be Sheet1 as this is the sheet that is what we are comparing from

When I run the code I get an error: Object required on line 9

Odd that it worked great until we amended the code