1

I have a macro for my footer that uses the RIGHT and LEN function to pull strings from various cells within the worksheet. I've tested on various sheets and this works.

ActiveSheet.PageSetup.RightFooter = Right(Range("D11"), 8) + Right(Range("D4"), Len(Range("D4")) - 24)

I did my homework and went with the 'record macro' method to format the footer and came up with the following:

 ActiveSheet.PageSetup.RightFooter = "&""Times New Roman,Regular"

I do not know how to combine the two without losing my values or getting a compiling error.

2
  • Do they have to be combined? Try leaving them as individual commands on separate lines. One will run after the other is complete.
    – CharlieRB
    Mar 10, 2015 at 0:41
  • When I apply the code as individual commands on separate lines, I either get a blank footer or I get the footer in it's default Arial 10 pt font.
    – dasMetzger
    Mar 10, 2015 at 13:19

1 Answer 1

1

Ah it's looking for quotation marks, like this -

ActiveSheet.PageSetup.RightFooter = "&""Algerian,Bold" & """" & Left(Range("A44"), 7) + " " & Mid(Range("B44"), 4, 11) 

For you -

ActiveSheet.PageSetup.RightFooter = "&""Times New Roman,Regular" & """" & Right(Range("D11"), 8) + Right(Range("D4"), Len(Range("D4")) - 24) 
5
  • @dasMetzger this should do it Mar 10, 2015 at 14:13
  • this works! did not need the extra & """" at the end though. do you know why this was looking for those quotes? i dont have enough rep to upvote you. but thanks
    – dasMetzger
    Mar 10, 2015 at 14:19
  • @dasMetzger something about it wanting a string, like you'd see if you recorded the macro it would do "&""font""what you typed" Mar 10, 2015 at 14:22
  • while troubleshooting i was trying to put the entire RIGHT formula into the quotes, but obviously ended up getting nothing in return by the physical characters in the formula.
    – dasMetzger
    Mar 10, 2015 at 14:24
  • 1
    Yeah, since VBA "" is technically a " in regular excel, you need """" Mar 10, 2015 at 14:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .