Posts by JimmyB

Well I can't find anything that should be interfering with the code that you provided. (and it works on your computer arggh) Will keep trying

Jimmy

ok Carim. I'm quite sure you've given me exactly what I need...I just need to incorporate it in. I'm sure that I'll find out what is wrong on my end. The syntax is right and that is where I often struggle. It works on your end and that's what I need to hear. So Thanks a ton. It is really very much appreciated.

You know, you've helped me a lot on this program over the past months. I'm almost finished with the program. If you'd like to see what you, (and others at OzGrid), have been helping me develop, just click on the link of my You Tube channel in my profile. There is a series of videos, showing myself actually running the program as an instructional feature.

There's also my website etc...

Thanks again

Jimmy

Hi Carim.

Sorry to say, it deletes the initial row in "VALUES for MEALS" (Sheet12) but doesn't touch 13 to 21...no error, just does nothing

Jimmy

Ok Carim

Thanks a ton

Hi Carim

Have you had a chance to check out the issue?

Thanks Jimmy

Silly ME

If you place this code in the workbook event it will allow macros to run in password protected sheets. If that doesn't work the password is right there in the code

Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Protect "Pamela491", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
    Next ws
    'This allows the macros to run on protected sheets

Still not running. I got error: Object required on Line 14...but nothing else happened.

You mentioned that if you had the workbook, you could probably help better, so I pasted the extra 8 sheets into the file that you previously sent me. Hope that this helps solve the issue.

To help eliminate confusion, I adjusted all of the sheet #'s to match my workbook.

Thanks Jimmy

Test DeleteRow JimmyB .xlsm

Sorry Carim,

There's no error code, but when it runs it only deletes the original target in sheet 12...none of the other sheets (13 to 21)

Hi Carim

This is pretty well what you had sent me yesterday, except you adjusted the sheet designations on line 7, but that isn't my problem. I've adjusted for that.

My issue is with line 11. Shouldn't we be referring to the main sheet 'Sheet12' which is the actual target? Also shouldn't it be outside of the loop, so it only deletes once? instead of Sheets(i) which mimics the deletions orchestrated in line #10

The code that you sent me in thread #11 had structure that worked, but we're not applying the same here. If you go back to that thread, and compare the event code in that file to what we have now, you'll see what I mean.

in any event I'm getting error "object required" on line 11 either way

Thanks for your patience... Jimmy

Odd that it worked great until we amended the code

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

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

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

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

HEY DAVESEXCEL...IT WORKED!

perfectly. You are a genius. I don't know how you guys do this with the limited information you get from us (me especially I guess).

Thanks to all of you that contributed Dave, Uncle S, RoyUK & Carim (originally)

HEY Dave...Thanks...I didn't know about the 'Copy Icon' very very helpful info.haha

Actually I re-copied your sub again using the 'Copy Icon' and must have missed something when I originally did it manually. The copied file in in my folder . Only problem now is that I'm getting a transport error when it tries to send, because the email password is deleted prematurely in the original file. But we're much closer...almost got it

I moved the deleted line to after the saveas and it sent but not with deleted content. Tonight I'll just ad a resave line of code after the cell deletion so it saves before it sends.

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, 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

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

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