Change this VBA to a Formula. HELP PLEASE

[Mister H]

Hi:

I am trying to change this code to a formula but I am not having any luck. This is the code that I have that names a file but I need the name to go in a specific cell (titled FileName) as well.

ActiveSheet.Range("Min") & " - " & ActiveSheet.Range("LocationNo") & " - " & ActiveSheet.Range("LocationName") & " - " & Format(ActiveSheet.Range("TransactionDate"), "dd-mmm-yyyy") & "   " & Format(ActiveSheet.Range("TotalRevenue"), "$###,###,##0.00")

So, I tried this but I can't get it to work for me:

=UPPER(TEXT(Min & " - " & LocationNo & " - " & LocationName & " - " & Format(TransactionDate, "dd-mmm-yyyy") & " " & Format(TotalRevenue,"$###,###,##0.00")))

Min
LocationNo
LocationName
TransactionDate
TotalRevenue

These are all named cells within the workbook.

Can someone help me out please?

THANKS,
Mark
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>

 

[mikerickson]

Format is a VB function, not a worksheet function. Use TEXT throuought, instead of Format.

 

[perco754]

Hi,

You can not reference a named range as a string, which is exactly what you are trying to do. The TEXT() function will convert your attempt in referencing the named cell Min into the string "Min" which won't give you the desired result.
I can give you some hints regarding using named ranges in formulas.

Try this very simple example:

In a new sheet I name cell A1 as cellone and cell A2 as celltwo.
I put values cellone = 1 and celltwo = 2
In e.g. cell B1 I enter the formula: =SUM(cellone;celltwo) which would evaulate to the value 3.

As you can see I'm NOT writing =SUM("cellone";"celltwo").

BR,
perco

He

 

[Mister H]

THANKS to BOTH of you for your help. Here is the formula that seems to be working:

=UPPER(Min & " - " & LocationNo & " - " & LocationName & " - " & TEXT(TransactionDate, "dd-mmm-yyyy") & " " & TEXT(TotalRevenue,"$###,###,##0.00"))

Have a GREAT day,
Mark