Posts by Halvar

  • Formula to check if value is within range

    Re: Formula to check if value is within range


    Thanks for the input Kenneth - but what I am looking for is to check whether the result falls anywhere between -1 and 1. As in, if (A1+B1)=0.5, or 0.75, or 0.999, or -0.3 - this would all satisfy my criteria. I am essentially checking whether the total per two sheets in my workbook reconcile, but there's often a microscopic difference between the two due to rounding issues.


    Also, just as I was posting this I've realized I should be able to play with the =ROUND() formula which will allow me to check if the rounded sum is equal to 0. So, thanks again for the help - in an unexpected way this pushed me towards the solution:)

  • Formula to check if value is within range

    Hi all, I'm wondering if there's a formula that could help me simplify the following calculation. I am checking whether the result of my calculation is within a specific range (between -1 and 1). To give you an example, here's how I'm checking whether -1<(A1+B1)<1:


    Code
    =IF(AND((A1+B1)>-1),(A1+B1)<1)),"OK","Check calculation for error")


    Question: is there a way to simplify the

    Code
    AND((A1+B1)>-1),(A1+B1)<1)

    part? I.e., and I'm totally making this up, something along the lines of

    Code
    =BETWEEN((A1+B1),-1,1)

    .


    My issue is that the A1+B1 part of the calc is actually a lot more complex, and I'd like to clean my formulas as clean as possible and not repeat the summation formula multiple times.


    Thanks!


    p.s. Would prefer to avoid using VBA/UDFs for this.

  • error in running conversion macro

    Re: error in running conversion macro


    algaeginger, another general suggestion to make your code a lot clear - you actually never need to select anything in VBA (neither sheets nor ranges), it's an unnecessary step. For instance, your code can be written in the following manner:


    Code
    Worksheets("Raw").Range("A1").Copy destination:=Worksheets("Sheet1").Range("A5")


    This will copy cell A1 from the "Raw" sheet onto Sheet1 and paste it in cell A5.

  • Cannot change number of decimals displayed

    Re: Cannot change number of decimals displayed


    Found the solution myself. As I suspected, too many styles caused by data brought over from multiple workbooks into one file.


    Solution: go to Home >> Number >> choose "More Number Formats.." from the drop-down >> on the "Number" tab select "Custom" >> you'll see a list of custom number formats that exist in your workbook. Scroll down past the preset ones that cannot be deleted, and delete one or multiple styles you do not use. This will free up the memory for new styles.

  • Cannot change number of decimals displayed

    I'm working on a pretty big model in Excel and somewhere along the way I've lost the ability to increase or decrease the number of decimals displayed in the formatted cells. I've tried using Increase/Decrease Decimals buttons on the ribbon (Home >> Number). I also tried right-clicking on the cell, going to Format Cells, and increase the number of decimals on the Number tab there - after clicking ok, nothing happens, and when I go back to cell formatting it's set up to show the same number of decimals as before as if I haven't changed it.


    Anyone run into a similar issue in the past? Any suggestions? I've tried running "XLStylesTool.exe" to remove excessive styles (thought that might be the problem), but that didn't seem to help.

  • What's the shape type of drop boxes in VBA?

    Hi,


    I have some code that is executed if a shape is placed in a specific spot on the worksheet. I accomplish this by checking Shape.TopLeftCell property for each of the shapes on the worksheet.


    The code works fine with "buttons" (rectangular shapes) I have on my sheet. However, apparently Excel treats a drop-down box I have on my sheet as a Shape object as well, and since apparently drop boxes don't support .TopLeftCell property my code errors out when it loops through the drop box Shape.


    What I want to do: add an IF statement to ignore the drop box shape so the code don't test it. But I can't seem to find the proper language for this - I was hoping to put in something along the lines of


    Code
    If Shape.Type = DropBox then
Exit Sub


    Any suggestions would be appreciated.

  • VBA to Clear Row Contents Based on Multiple Criteria

    Re: VBA to Clear Row Contents Based on Multiple Criteria


    What do you have as far as code goes? If you're not using a huge data set (tens of thousand of rows) you should be able to accomplish this using For Each loops. It will step through each cell and check criteria using If statements, e.g. If Range("A1").Value <5 Then Range("A1").ClearContents.

  • Run Macro After Opening a specific Workbook

    Re: Run Macro After Opening a specific Workbook


    Are you saying your macro runs every time a new workbook is open, no matter if it's the workbook that contains the macro, or not?


    If so, try testing if the name of the ActiveWorkbook (the one that's been just opened) is the same as the name of ThisWorkbook (the workbook that contains the macro.


    I'm thinking something along the lines of


    Code
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
'Run macro
Else
Exit Sub
End If

  • Excel opens a blank workbook every time it's launched

    Hello all,


    Every time I start Excel (whether just the application itself, or a specific workbook) it opens a blank workbook instead of just starting the application. Anyone had the same issue? I've tried a few things (playing with Advanced options etc) but nothing seemed to be directly related to the issue. Would appreciate any help.


    UPD: the advanced options feature I was looking at is under File => Options => Advanced> General => At startup, open all files in...

  • Deselecting range on an inactive sheet after copy-paste

    Re: Deselecting range on an inactive sheet after copy-paste


    Thanks Kenneth, tried that before, but it didn't do the trick for some reason.. Probably because I'm also inserting columns - are you aware of any code that might clear selection resulting from inserting columns? As in:


    Code
    .Columns("AA:AB").Insert Shift:=xlToRight

  • Deselecting range on an inactive sheet after copy-paste

    Hi all, looking for a more elegant way to do something really simple..


    After copying and pasting a range on an inactive sheet that range remains selected after the code is done running and the user activates that sheet. Is there an easy way to select, say, cell A1 without activating the sheet affected by the copy-paste macro?