Posts by Huhenyo

  • Compare Variable To Determine If Between 2 Variables

    Re: Compare Variables Of Type Double


    Snb,
    Thank you! I didn't know that the variables would become variant if I declared them that way. I defined them all seperately and that took care of the problem.


    As a follow-up, is there a way to define more than one variable with one dim statement without all but the last one become a variant type variable? In other words, do I have to define all the variables like this:


    Code
    dim var1 as double
dim var2 as double
dim var3 as double


    Thanks again,
    Nick

  • Compare Variable To Determine If Between 2 Variables

    Hi all,
    I have read in various places that problems can easily arise when comparing variables that aren't integers. I have a macro that ask for the user to input 2 variables of type double. These two variables then need to be compared to 2 other variables are defined in the code, or I've also tried to have the user input the value with different results even when the variable vaules are the exact same. I've listed the lines of code where the values are given to the variables of type double. These values are compared. There are 9 different scenarios that can happen, but the code is unable to determine which is the case. I've listed the different cases below as well. If I enter 2.3 for INR1 and 1.9 for INR2 the code says that scenario #1 is correct, but clearly this is not the case. I need help with a work around so I can compare these variable of type double. I've also attached the full excel file for completeness.



    Thanks for the help,
    Nick

  • Standard Toolbar Options Are Missing

    For the past little while whenever I use Excel I don't have the Save As option under the file menu. There aren't any macros running or anything like that. I can start a new workbook and then save it, make some changes, and then then try to save it again using Save As, but the option isn't there.


    Does anyone know what might be causing this?


    Thanks.

  • Disable AutoComplete For Specific Cell

    Re: Disable Autocomplete For A Specific Cell


    I actually do have an error set already. The user can only imput "s" but I just want to prevent the autocomplete from coming up so the user doesn't have to take an extra step to hit backspace to get rid of the unwanted "astus." I'm making this for a health care team and I want it to not have little problems like this. Thanks for your help.


    I think the problem is that activecell only references the cell once you try to do something with it. I tried target earlier and that didn't work either. What in the world is the cell considered before anything is done to it?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Here is an bit of my workbook that I'm having trouble with. The first sheet seems to work fine now. The problem I'm still having is that when you go to the second sheet and the cursor is on the column in question and you type an "s" it tries to autocomplete. I think this is because the cell isn't activated yet or something. I hope this helps to make what I'm saying a little more clear.


    Thanks so much for the patience. I'm just learning here.

  • Disable AutoComplete For Specific Cell

    Re: Disable Autocomplete For A Specific Cell


    I actually already have validation set up so that the user can only enter "a","c","n", or "s", but above A35 there is a vertical text in the column "A" that says "status." So what ends up happening is that when the user enters an "s" it tries to auto complete with "status." I need autocomplete to be on in every other cell but this one. I wish I could upload my spreadsheet but it is too big (78Kb) and I guess we are only allowed 40Kb or something like that.


    Thanks anyway.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I've also tried this. What does VB call the cell that you just tabbed to but you haven't done anything with yet? Is that considered the active cell? For instance I type something in A34 and hit enter. The cell that is highlighted then becomes A35. What is that cell considered at the very moment it becomes highlighted and before I do anything with it?


    Code
    With Application
        If ActiveCell.Address = "$A$35" Then
            .EnableAutoComplete = False
        Else
            .EnableAutoComplete = True
        End If
    End With

  • Disable AutoComplete For Specific Cell

    Re: Disable Autocomplete For A Specific Cell


    VB Noob,
    I'm going to attach my spreadsheet. The code you gave me above works but not until something is entered into cell A35 (sorry, it was A11 but not it is A35). You may first want to protect the sheet so you can see what the problem is. The user needs to first enter a date, then hit tab. This takes the user to cell A35. If the user enters "s" the word Status will try to autocomplete. I need the spreadsheet to not do that. If I then enter just an "S" into cell A35 and hit tab or enter, the autocomplete will then be deactivated. What I need to have happen is for autocomplete to be deactivated the second the cell A35 is highlighted and before anything is entered. I'm sure that I'm just doing something wrong, but I messed with it quite awhile now and I can't seem to get it to work. I can change the code to use
    N1 instead of A35 and then after the user enters the date and hits tab or enter it will turn off the autocomplete and then "S" can be entered into cell A35 without problem. This is fine for sheet1, but Sheet2 starts the user right on A35. Does this make sense? Sorry if I'm being confusing.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Sorry, VB Noob, I didn't realize that my file didn't attach. I guess it is too big and so I won't be able to attach it. Do you understand what I was trying to say above? I'm sure there is a way to make VB make the change to the autocomplete before anything is added to the cell, but I don't know how to do it. My file is 78Kb so I can't attach it. Sorry.


    Thanks for all the help,
    Nick

  • Disable AutoComplete For Specific Cell

    Hi all,
    I would like to know if anyone knows how to disable autocomplete for a specific cell. There is only one cell (A11) that I don't want to use auto complete but I want every other cell to be able to still autocomplete. I think it may have something to do with matchentry being set to 2, but I'm not sure how to do this. If you could provide a code example I would greatly appreciate it.


    Thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I don't know what sintax I need to use. I've tried this line of code but it doesn't work


    A11.MatchEntry = fmMatchEntryNone


    Please help.


    Thanks.

  • Find Cell Value In Column & Return Text In Adjacent Cell

    Re: Find Cell Value In Column & Return Text In Adjacent Cell


    Stephen,
    Thanks. I've now have an input of "Amiodarone" automatically putting in "Heart Rhythm." I also have code that is automatically capitolizing input in column "A." I now have a small problem if you could help me with. Everything works fine and I can delete cells individually, but if I put in say "a" in column one and then "Amiodarone" in column B (which automatically populates "Heart Rhythm" in colum C and then I highlight them and right click and select "clear contents" I get this error:
    Runtime error '13' type mismatch. I'm not sure why I can delete cells individually, but if I do a clear contents it gives me this error. Here is my code:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = "Amiodarone" Then
        Target.Offset(, 1) = "Heart Rhythmn"
    End If
    If Intersect(Target, Range("A11:A29")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub


    Thanks again. You all have been very helpful.

  • Find Cell Value In Column & Return Text In Adjacent Cell

    I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30. I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.


    I want the user to be able to choose from the list or type the drug name in.
    Thanks,


    Nick