excel vba Select Case loop with "and" possible?

    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

    You could work on something like this:

    Code
    For Each cell In Sheets("MySheet").Columns(17).Cells
    Select Case cell.Value
        Case "-29.75"
            If cell.Offset(0, -6) = "02-01-2022" Then
                cell.Offset(0, 2).Value = "-8.33"
            ElseIf cell.Offset(0, -6) = "03-01-2022" Then
                cell.Offset(0, 2).Value = "-10.50"
            End If
    End Select
Next

    Why do you use a Select Case in that example?


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

    In fact you don't even need VBA. It could easily be achieved with a formula.

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

    I think it would still make your life easier if you used a lookup table on a worksheet that the code can get the values from, rather than having hugely long Select Case or If...Then constructs.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

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

    Edited once, last by KathCobb ().

    That's not really how Select Case works. It evaluates the expression on the Select Case line and then each Case statement says what to do if the expression evaluated to the value in the Case statement. So something like this:


    Code
    Select Case cell.Value
Case "-29.75"
cell.Offset(0, 2).Value = "-8.33"


    is equivalent to:


    Code
    If cell.Value = "-29.75" then
cell.Offset(0, 2).Value = "-8.33"


    It would be better, in my opinion, to make a separate function that looks up a given value in a given table. Then you can simply maintain the table(s) and amend them as needed, rather than having to keep changing a long list of Select Case or If..ElseIf instructions. The actual processing code in each location would then become something like:


    Code
    Dim returnValue
For Each cell In Sheets("MySheet").Columns(17).Cells
returnValue = myLookupFunction(cell.value, range("lookup range here"), range("return range here"))
if returnValue <> "No Match" then cell.offset(, 2).value = returnValue
Next cell

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    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.

    Edited once, last by KathCobb ().

    myLookupFunction would need to be created - it's not an existing thing. That was just a demonstration of the principle of how it would simplify your calling code.

    A rough example:


    Code
    Function myLookupFunction(lookupval, findrange as range, returnrange as range)
dim matchPos
matchpos = application.match(lookupval, findrange, 0)
if iserror(matchpos) then
mylookupfunction = "No Match"
else
mylookupfunction = returnrange.cells(matchpos).value
end if
end function

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    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.

    All you should need to do is add the function code I just posted as a separate routine. Then the calling code would be just as you posted it before that.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    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

    Edited once, last by KathCobb ().

    It sounds like you renamed the module myLookupFunction? If so, change it to something else. It's never a good idea to use the same name for two things in VBA.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Matching decimals can be problematic due to the fact that not all decimals can be represented in binary, so they are often not stored as the number you think they are. A few questions:


    Is it supposed to be an exact match in the lookup table?

    Are the numbers entered manually, or the results of formulas?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    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?

    Edited 3 times, last by KathCobb ().

    You can simply do another lookup. Since you will already know if the first value was found, there is no need to test again if the match worked, so you'd change this:


    Code
    If returnValue <> "No Match" Then cell.Offset(, 2).Value = returnValue


    to this:


    Code
    If returnValue <> "No Match" Then 
   cell.Offset(, 2).Value = returnValue
   cell.offset(, 3).value = myLookupFunction(cell.Value, Workbooks("LookUpTable.xlsx").Worksheets("RMR_NY_All").Range("B2:B52"), Workbooks("LookUpTable.xlsx").Worksheets("RMR_NY_All").Range("D2:D52"))
End If


    I'd also suggest assigning a worksheet variable for the lookup data sheet.


    I note you are looping through every cell in column 17 which seems like huge overkill. You should really restrict the loop range to just the range with data.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    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.

    Here's an example:


    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login