Save selected sheets to a pdf file

3 Responses to “Save selected sheets to a pdf file”

  1. Bert van Zandbergen says:

    Hi Oscar
    First of all thanks for your beautiful macro to copy an Excel file to PDF. If you want to copy a wide horizontal field to PDF, it can cause a split in the PDF: you get divided the text, graphics and / or object on two pages). That's not what you want!

    To make a perfect horizontal layout, I recommend to define first in the worksheet: Print Range, Page Layout (on ribbon). Create with File / Print Options other necessary instructions, for example, choose settings like "print to fit", borders etc.etc. The actual print layout is displayed on the right side.

    Instead of a normal print, you can now activate the Macro. These simple adjustments don't give a distribution on two pages, but an appropriate horizontal PDF format. So, it's a contribution to your readers.

    Best regards,
    Bert van Zandbergen

  2. Михаил says:

    Хотите войти в профессиональный мир покера и начать зарабатывать, но не знаете с чего начать? Наши специалисты Вам с радостью помогут. У нас есть бесплатные уроки, проходят вебинары. Анонсы предстоящих событий из мира покера, чемпионаты, аналитика, которая помогает новичкам не теряться в распространенных ситуациях. Всё это и многое другое на нашем ресурсе.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Find cells containing formulas with literal (hard coded) values

10 Responses to “Find cells containing formulas with literal (hard coded) values”

  1. Given that a Boolean defaults to False, you can simplify your CellUsesLiteralValue function like so...

    Function CellUsesLiteralValue(Cell As Range) As Boolean
      If Cell.HasFormula Then
        CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"
      End If
    End Function
    

    Actually, if you use the single line form of If..Then, then you can reduce the code to a one-liner (the code area will probably line wrap the single line of code due to its length)...

    Function CellUsesLiteralValue(Cell As Range) As Boolean
      If Cell.HasFormula Then CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"
    End Function
    
  2. Well, my previous attempt did not come out so well. I do not understand why, though, as I used the vb code tags as suggested just below the "Leave a Reply" heading. I don't know if this will work or not, but I am going to try. Here is the three-line code without any code tags...

    Function CellUsesLiteralValue(Cell As Range) As Boolean
    If Cell.HasFormula Then
    CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"
    End If
    End Function

  3. That actually looks normal, so here is the one-liner version of it (remember, it might line wrap because of its length)...

    Function CellUsesLiteralValue(Cell As Range) As Boolean
    If Cell.HasFormula Then CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"
    End Function

  4. Oscar says:

    Rick Rothstein (MVP - Excel),

    thank you for commenting.

    • Seán says:

      Oscar, you had given me this formula to show items that are in Column B, but not in A.Array formula in cell C2:=INDEX($B$1:$B$5, MATCH(0, COUNTIF($C$1:C1, $B$1:$B$5)+COUNTIF($A$1:$A$5, $B$1:$B$5), 0)) + CTRL + SHIFT + ENTER. Copy C2 and paste it down as far as needed.
      I added a condition to this formula :
      C2:=INDEX($B$1:$B$11, MATCH(0,IF($E$1:$E$11="AB", COUNTIF($C$1:C1, $B$1:$B$11)+COUNTIF($A$1:$A$5, $B$1:$B$11)), 0))
      Oscar, so column E is criteria for column B. It works for that one condition. I would like to add a condition for column A, but it's returning the wrong result. See below.
      INDEX($B$1:$B$11, MATCH(0,IF(($E$1:$E$11="AB")* $F$1:$E$5="DB") , COUNTIF($C$1:C1, $B$1:$B$11)+COUNTIF($A$1:$A$5, $B$1:$B$11)), 0))
      How would you do this?

    • Oscar says:

      Seán,

      $F$1:$E$5 is a cell ref to to two columns, is this a typo?

      Try this:

      INDEX($B$1:$B$11, MATCH(0,IF(($E$1:$E$11="AB"), COUNTIF($C$1:C1, $B$1:$B$11)+IF($F$1:$E$5="DB",COUNTIF($A$1:$A$5, $B$1:$B$11),0)), 0))

  5. Seán says:

    That was a typo. Will that make a difference to the formula?

  6. Sean says:

    Oscar,
    I'm comparing the A column to B.
    =INDEX($A$1:$A$11, MATCH(0,IF(($B$1:$B$11="AB"),COUNTIF($E$1:E1, $A$1:$A$11)+IF($D$1:$D$11="DB",COUNTIF($C$1:$C$11, $A$1:$A$11),0)), 0))
    A B C D
    1 DX 1 MN
    2 DX 2 MN
    3 DX 3 MN
    4 AB 4 MN
    5 AB 5 DB
    6 AB 7 DB

    7 AB

    Formula Result 4
    6

    Result should be 6
    This is trickier than I thought.
    Seán

  7. Seán says:

    Oscar it doesn't make sense that it's returning the incorrect result. You want to i loo return an array of numbers after using the if condition. r

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

LOOKUP function

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

How to enter array formulas in merged cells

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

INDIRECT function

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file