Posts by KathCobb

    Hello, All,


    Have adapted a few sample codes that I have found (below) to try and automate an email process. I need to use a specific send account, attach one or two PDF files in specified folder with file names that do not change and also check if there are not any attachments, then send nothing. This is what I have modified below that will work-for one attachment only. Note: I found several code samples for SendUsingAccount but this is the only one that would work with an attachment.



    I have tried a number of different methods but cannot get it to work. I'd appreciate any suggestions.


    Thank you,


    Kathy

    Thank you! I am confused about this section:


    cell.Offset(, 3).Value = myLookupFunction(cell.Value, lookupSheet.Range("B2:B52"), lookupSheet.Range("D2:D52"))


    My LookUp Values in my Look up Table are all in Column A. My first set of Return Values is Column B, My second set of return Values is Column C--these return values are also based on the same look up Value. So for example in my table: A2 = 389.50 B2=125.50 C2= New. So what I am doing is for every Value in Column A, I need the corresponding results from Column B and C. Of course the values need to be placed in different directions...The first return value is offset 0,2 and then next return value is offset 0,-1.


    The code so far has been beyond helpful. I sincerely appreciate it.

    Thank you for the update. I did actually go ahead and do another loop. It is working great. I am using every cell in column 17 because sometimes it can be 20 rows and other times it can be 250 rows of data. I know I could use the Last Row function...but I really struggle to get that to work properly sometimes. I have used in in many of my ranges but suddenly it has stopped working is certain code...something I need to troubleshoot. And I have no idea really how to properly set up use of variables, thats why I avoid it. If you have a suggestion, I'd give a try. I really am a novice at this and know just enough to automate some things with a little help from google and forums like these. I am thrilled that, with your help, the code and lookup tables work. That alone is an incredible time saver.

    Yes an exact match--no to formulas. The column is set to accounting. And I did go through and remove anything with zeros because I had that issue with my Case statements. 450.00 is really just 450 in the cell. One of the numbers is 238.33 in the lookup field and should then produce the return value of 83.33. But it does nothing. no values are returned at all.


    OMG!!! YAY!!!! I changed the numbers to "general" and it WORKED!! I am so excited!! Now I need to know if I can add another return value. For example Lookup Value = 283.33, move over two, return value = 83.33, move over 3 = New. The range where New will be would be C2:C52. Where would I include that?

    I had originally gotten a compile error but figured out that was due to naming conventions. Now the code runs but it does not find any matches. Is it because there are decimals? I am not sure how to troubleshoot from here.....


    Code
    Sub LookUp_Table()
    Dim returnValue
    
    
    For Each cell In Sheets("MySheet").Columns(17).Cells
    returnValue = myLookupFunction(cell.Value, Workbooks("LookUpTable.xlsx").Worksheets("RMR_NY_All").Range("B2:B52"), Workbooks("LookUpTable.xlsx").Worksheets("RMR_NY_All").Range("C2:C52"))
    If returnValue <> "No Match" Then cell.Offset(, 2).Value = returnValue
    Next cell
    End Sub





    Thank you so much for all this help. This is going to save me so much time if I can get it to work. I cant say enough how grateful I am


    Kathy

    This is beyond my skillset. I love the recommendation and would love to implement it but I wouldn't know where to begin to edit what you have given me. I was unsure of whether I put the sheet names in the correct place or the subroutine. :/ I googled using a lookup function and found several examples but all were looking for one specific word or number. I need, as you pointed out, to look up in the table. If you are aware of any tutorials that use the method you have shared, I'd greatly appreciate it.

    Can the lookup range be a separate workbook and worksheets? I do not know how to name a range and refer to it in vba. Using your code above My Look Up Range would be in Workbook: LookUpTable, worksheet: Rate_All, the cells are B2: B52. My Return Value Range would be same workbook, same worksheet and Cells C2:C52.


    I tried this:

    Code
    Sub LookUp_Table()
    Dim returnValue
    
    
    For Each cell In Sheets("MySheet").Columns(17).Cells
    returnValue = myLookupFunction(cell.Value, Workbooks("LookUpTable.xlsx").Worksheets("RMR_NY_All").Range("B2:B52"), Workbooks("LookUpTable.xlsx").Worksheets("Rate_All").Range("C2:C52"))
    If returnValue <> "No Match" Then cell.Offset(, 2).Value = returnValue
    Next cell
    End Sub

    I got an error on myLookUpFunction. Compile Error Sub or Function not defined.

    I would not know how to automate that....I do have all the amounts listed in a table ...but it is actually several different tables. I would need help trying to figure out how to add that in. Many things happen in my code such as, lots of excess columns deleted, autofiltering and copying to new worksheets, then again to a new workbook. And this is done with a brand new workbook each week. I used to do all this manually until I thought there must be a better way...enter VBA into my life. I can finally have all my data ready for me with the click of a mouse. I am trying to do zero things other than check there are no "glitches" and move on to the next task.


    I also am not good with naming variables, but what I would hope to be able to do (and understand) is be able to use a Elect Case statement that looks more like what i have seen in examples, such as:


    Case 1: 200

    Case 2 250

    Case 3: 300


    For Each cell In Sheets("MySheet").Columns(17).Cells

    Case 1 cell.Offset(0, 2).Value = "-50.00"

    Case 2 cell.Offset(0, 2).Value = "-25.00"


    Or however it would work. That would at least make it easier for me to see my data instead of scrolling through hundreds of line of code....But I don't know how to write a multiple case statement.....

    I will try the above code...most of my code is sadly not very "clean".


    Yes, I could use a formula but this is part of a much larger code that does many things that I can just push "start" on and walk away. If I use a formula, then I am manually filling down to get it to look at every line, correct? I don't use formulas often either. My snippet of code above actually appears in my overall code over 150 times ...it needs to run through two different columns looking for specific amounts and when it finds those amounts move to the proper column and put in the assigned amount. Each worksheet I run the code has hundred of lines of data. So all I know how to do is use a separate Select Case statement for each individual amount I am looking for. I am sure there is a neater way to write the code with in one long block of case statements but I have no idea how to do that but I sure would love to if it is something you could help me with...

    Hello,


    I have very basic excel vba skills and I've (thankfully) managed to automate some excel tasks. I am not at all good at understanding loops or Select Case Statements. The only examples I can ever find are about changing number grades to letter grades. Helpful to teachers, but I don't see how to adapt it for what I need to do. Hoping someone here can tell me if what I would like to do is possible and how to go about it if a Select Case statement isn't correct. Right now I have a bunch of these:


    Code
    For Each cell In Sheets("MySheet").Columns(17).Cells
    Select Case cell.Value
    Case "-29.75"
    cell.Offset(0, 2).Value = "-8.33"
    End Select
    Next

    Basically this checks a column for a specific number, moves over two cells and adds the assigned number to go with it. I probably have 150 different lines of code like the above for each number set....I am sure there is probably ad easier way but this is what I know how to do and has been working, so I just stuck with it. Now I have an issue that some of the numbers in the Case statement can have two different values assigned to it. So of the above example Case "-29.75" could have to put "-8.33" or "-10.50" in the offset cell.


    What I am wondering is if there is away to say: For each cell in Column 17 that has a value of "-29.75" AND in Column 11 has the date value of 02-01-2022 then move over two cells and enter "-8.33". Or if Column 17 has "-29.75" AND column 11 has date value 03-01-2022 move over two cell and enter "-10.50". Is this possible? I may also need to offset one more cell after entering the "-8.33" and add text "Replacement". I know how to do that in my current select case statement shown above, I just do not know if it would change if I can look for two values.


    Any help in this would be greatly appreciated. :)


    Thanks,


    Kathy

    Ok, with a lot of help I have been able to create several excel vba modules and they have been unbelievable helpful, but I do not know where to begin with this one.


    I have Column D that has a master number. In column E is misc info, Column F is the Main field that I need (product in this case) . In Column G thru AK are acceptable product names that match the master number. So what I need is to figure if there is a way to write a code that says:


    For every number in column D, Check column G through AK for text. For text found in each column, I'll need to copy that master number, insert a row, put that master number in column D of new row and move the text from the original column G through AK to Column F in new row.


    So my excel sheet looks like this:


    Column D Column E Column F Column G Column H Column I Column J Etc

    12345 XXXXXXX Soap Soap Bar Soap Crystal Soap Liquid Soap Foam

    12222 XXXXXXX Soda Soda Can Soda Bottle blank blank


    And I need it to become this:


    Column D Column E Column F Column G Column H Column I Column J Etc

    12345 XXXXXXX Soap

    12345 XXXXXXX Soap Bar

    12345 XXXXXXX Soap Crystal

    12345 XXXXXXX Soap Liquid

    12345 XXXXXXX Soap Foam

    12222 XXXXXXX Soda

    12222 XXXXXXX Soda Can

    12222 XXXXXXX Soda Bottle



    Is there a way to do this in code? I know how to search for specific data and copy it and move it with Select Case but asking it to look thru 31 columns ignore if they re blank and then copy the text in that column and another specific column is way out of my league. I have zero experience with loops.


    Any and all code assistance would be greatly appreciated.


    Kathy