Posts by Brandtrock

  • Inscribing Cells

    Re: Inscribing Cells


    Thanks for the improved description. I think I better understand what you are trying to do. Could you place your inscription in a comment? Then choose None on the Tools>Options>View tab.


    I'm off to the dentist right now but will check back later.


    Regards,

  • Code Purpose Unknown

    Re: Code Purpose Unknown


    The code you have posted looks like it may have come from the macro recorder. The code itself is entering data in some cells, looking up a value in another sheet, multiplying a number, and some copy, pasting and filling.


    The RC[] business is one of VBA's methods to reference cells. The R1C1 method refers to cells by a row and a column number. The A1 method refers to cells by a row number and a column letter. It is common to use the R1C1 notation in coding as it is easy to "move" about a spreadsheet in this fashion. The negative numbers after the C represent movement to the left (positives are movement to the right) from the currently active cell. Movement up (negative) and down (positive) is done via the number after the R.


    Hope this helps you understand the code better.


    Regards,

  • Inscribing Cells

    Re: Inscribing Cells


    Quote from keekooceeaou

    Thanks for answers, but unfortunately it won't help.
    User can copy or move rows that contain those cells, so Your solutions won't work in this case.


    First off, setting the input message of a cell's data validation displays that message when the cell is selected. Therefore it doesn't appear that using that method would be covert with regard to your user.


    Second, data validation on a given cell will go along with the cell in a standard copy/paste operation.


    Third, a named range where the definition is a given list of cells will not amend itself if a cell is copied and pasted elsewhere. For example, MyRange =Sheet1!$C$3:$E$7,Sheet1!$G$3,Sheet1!$I$3,Sheet1!$K$3


    The following code will check to see if the active cell is in this range:


    Code
    Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("MyRange")) Is Nothing Then Exit Sub
    
    'Replace message box with your desired code
    MsgBox ("Insert code to do what ever you want to this inscribed cell")
End Sub


    If I have misunderstood what your desired outcome is, please post a more detailed description.


    Regards,

  • Special Day Of Celebration

    Re: Special Day Of Celebration


    Congratulations Rich (and Mrs. Rich)!!


    Looks like the couple celebrating 72 years has raised the bar for all of us young 'uns (you are included in this case as well Rich ).


    Well, this year (on 07/01) my wife and I will celebrate 18 years together. 3 sons, 2 careers, 20 moves (my wife's tracking skills seem to be pretty good too!), grandkids are still a way off. Seems that we've been blessed many times over as well.


    Keeping the faith,

  • If Formula To Test For 3 Values

    Re: If Formula To Test For 3 Values


    I found what I had done previously, then redid it a bit anyway. No Credit/Debit column in there yet. The explanations tell what is being done, it is up to you to determine if this is what SHOULD be happening. Change the notes for any incorrect results column to what SHOULD be happening and I can make the formula do what is needed for you.


    If you need/want explanations of how the formulas work when all is said and done, I'll be happy to do that as well.


    Sorry for the long delay.


    Regards,

  • If Formula To Test For 3 Values

    Re: If Formula To Test For 3 Values


    Don't know what I did to the "right one", so I'll redo it and post back tomorrow sometime. Really this time. I got sidetracked a bit with a broken tooth / eventual extraction and haven't been much good to anyone lately.


    Regards,

  • Inserting Cell Values Into A Textbox

    Re: Inserting Cell Values Into A Textbox


    You could try this:


    Quote from From the Tips and Tricks page on the main site

    Linked Picture
    A good alternative to a textbox or any shape is a linked picture that reflects any changes made to its reference. To make one, copy your cell(s), select the destination cell and holding down your Shift key go to Edit<Paste Picture Link.


    Regards,

  • Loop Stops Early

    Re: Loop Stops Early


    Kigol,


    I've given your most recent code a look and a test run. I'm not seeing what is going haywire. Charts are really not my bag of tricks.


    I'm sorry I'm not getting you where you need to go.


    Regards,

  • Soccer Game

    Re: Soccer Game


    You can try this out if you simply want one to use. If your interest is to learn how to make your own, then hang in there, give it a whirl and post questions as you go along.


    HTH,

  • And Formula To Include 0

    Re: And Formula To Include 0



    My comment was based on using the formula evaluator in the Tools>Audit menu, which steps through B2 (inserts cell value) and C3 (inserts cell value) and the greater than logical (inserts TRUE or FALSE), then returns the answer without showing any additional consideration of D3, E3, or F3.


    shg is correct in that AND(D3, E3, F3 does return TRUE when the cells all contain a value other than zero, including text or nothing. Glad to learn that little tidbit.


    Regards,

  • And Formula To Include 0

    Re: And Formula To Include 0


    The IF formula's construction is:


    IF(Logical test, condition if true, condition if false)


    I am sure you are already aware of that, just included it in case someone unfamiliar with that fact happens along this thread.


    Your logical test, originally, was:


    AND(B3>C3,D3,E3,F3)


    The AND function will return TRUE (and fire your condition if true) when ALL of the arguments evaluate to TRUE. As written above, you test for B3 being greater than C3 (will return TRUE or FALSE depending on the values in the cells). The next three arguments are simply cell references; no logical comparison is conducted when entered this way so the AND will return TRUE.


    Three members have offered the correct solution for performing the checks with AND on multiple cells. If you want to use the OR function instead, it behaves differently.


    If ANY argument is TRUE then the OR function will return TRUE. This has been offered as well.


    In either the case of the AND function or the OR function, you can use the logical comparison >= to check if B3 is Greater than or equal to the target cell. This may give you the desired control over how a zero is evaluated.


    HTH

  • If Formula To Test For 3 Values

    Re: If Formula To Test For 3 Values


    Firehorse,


    I added some notes to the workbook that I have attached. Included a Debit/Credit column. The explanations of what each column is calculating is simply to help you understand what each formula is doing. If any or even all of them need to perform differently, add a comment or a note telling exactly which numbers you want to include and when (W, P, Stakes, etc).


    Sorry I didn't get back to you sooner, but one of the posts on my car battery came out, so I spent most of the day bumming rides to school and the auto parts store. All fixed now though.


    Regards,

  • Sumproduct Formula Returns #VALUE! Error

    Re: Sumproduct Formula Retuns #value Error


    Code
    {=SUM(((LEFT(A1:A10,1)="v"))*(RIGHT((A1:A10),2)))}


    Using CTRL-Shift-Enter to place the formula above in the desired cell will return the correct answer unless text or a blank is encountered. More or less the same solution you already have though.


    Regards,

  • If Formula To Test For 3 Values

    Re: If Formula To Test For 3 Values


    Ok, I'll give it a look and post back tomorrow. It's getting late where I am and I have to get my sons to school in the morning. This shouldn't be much of a problem though.


    Regards,