formula in macro

[Guest]

I have a macro set up as follows

Public WithEvents App As Application


Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
CoName = InputBox("Division Name", "Add Division Name to Footer")
Dim finalstring As String
With ActiveSheet.PageSetup
..LeftHeader = ""
..CenterHeader = ""
..RightHeader = ""
..LeftFooter = ""
..CenterFooter = ""
..RightFooter = ""
finalstring = "&8" & CoName
End With
For Each sht In ActiveWorkbook.Sheets
sht.PageSetup.RightFooter = finalstring
sht.PageSetup.CenterFooter = "&8" & "Unaudited - For Management Purposes Only"
Next sht
End Sub

Which this works by bringing up the Input box before I print to insert the
division I am running in the footer, but would like it to automatically place
the division name in the footer based on a formula. For instance CoName = if
cell (a1) = 1 then "TestCo1" if (a1) =2 then "TestCo2" as an example. I
guess I don't know how to make it apply my named based on a condition or
formula.
I hope that makes sense but if you could help me automate this I would
appreciate it

Thanks

 

[Don Guillett]

try
sht.PageSetup.CenterFooter = "Test Co"&range("a1")

 

[Guest]

I've done things with named ranges similar to what you're talking about.
Here is an example of a line using multiple named ranges

.LeftFooter = Range("ToolName").Text & Chr(10) & _
"Issue Date: " & Range("Issue_Date").Value & Chr(10) & _
"Issue Number: " & Range("IssueNumber").Value

 

[Guest]

I understand how this works but what I want to happen is if my cell "a1"
equals a certain value to then place "Test Co" in my footer if "a1" equals a
different value to place "Test Co 2" in the footer instead, I just want the
name put in the footer I don't need the contents of the cell in it as well.
Is that possible to place Text based on a certain value?

 

[Don Guillett]

A select case statement may be what you want. You didn't say what you really
wanted. Why can't people say what they mean?

 

[Gord Dibben]

Just a note first.........your code and this amended code will add the same
Division name to all sheets in the workbook.

Is that what you want.

You have a number in A1 of Sheet1

In B1 enter this formula =IF(A1=1,"testco",IF(A1=2,"otherco",""))


Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim sht As Worksheet
CoName = Sheets("Sheet1").Range("B1").Value
Dim finalstring As String
For Each sht In ActiveWorkbook.Sheets
With sht.PageSetup
..LeftHeader = ""
..CenterHeader = ""
..RightHeader = ""
..LeftFooter = ""
..CenterFooter = ""
..RightFooter = ""
finalstring = "&8" & CoName
sht.PageSetup.RightFooter = finalstring
sht.PageSetup.CenterFooter = "&8" & "Unaudited - For Management Purposes Only"
End With
Next sht

End Sub


Gord Dibben MS Excel MVP

 

[Guest]

What I "MEAN" is that right now I have my CoName set to pop up an input box
and I have to enter a name to put in the footer. What I would like to do is
set the CoName to equal a certain condition or if statement.