Macros and formulas

[Mark_G]

Hello,

I am trying to make a macro to replace some of my formulas. The one I am having trouble with is the following formula:

='Stratum 2'!$AP$77&(IF('Stratum 2'!$AQ$77="- ","",(IF('Stratum 2'!$AR$77="- ",("; "&'Stratum 2'!$AQ$77),("; "&'Stratum 2'!$AQ$77&"; "&'Stratum 2'!$AR$77)))))

Any suggestions on how to write this as a macro. What it does is brings together 3 cells into one, but only those that are populated. THe first cell is always populated, whereas the other two may not be.

Thanks.

 

[acw]

Hi

What do you want the macro to do? Put in the result of the existing formula, evaluate the cell without the formula being there, or build the formula???


Tony

 

[nbrcrunch]

A much simpler approach is to merely use the concatenate function.

Example:
Across row 1 in columns A thru D put one word each:
Apples, are, for, kids
(without the commas)

In a blank cell write:
=concatenate(a1,b1,c1,d1)

You can create a named formula so that anytime you type the formula name, it does the concat relative to its position on the grid.

 

[Mark_G]

What I am trying to do is have a macro that will look to see if a worksheet exists, and if it does exists, look at what is in 3 cells. These three cells will either have no number in them and just be a " - " or have a two digit number followed by a " - " and a %, for example "02 - 100%". The reason concatenate does not work in this case is that some times the resule would be " 02 - 100%; - ;-", which I am sure you would all agree does not look as good as "02 - 100%". It is important to point out that this a report that gets printed, and I am trying to make it look good, and do not have to be able to manipulate the numbers any further. So looks are the most important thing here.

The reason a straight formula does not work is that the formula will exist before the other worksheet is created. So I was thinking a macro that would look to see if a worksheet existed ie "stratum 2", and then if it did exist, look at the values in 3 cells, and depending on the values in the 3 cells, it will always put the value of the first cell in, and then sometime the second, and sometimes all three cells.

I am having a bit of trouble with the formula in the macro as I am not great at the macros.

Mark

 

[acw]

Mark

Sorry to take so long. Try

Sub aaa()
 CheckSheet = InputBox("What is the name of the sheet to check")
 FoundIt = False
 For Each ce In Sheets
  If UCase(ce.Name) = UCase(CheckSheet) Then FoundIt = True
 Next ce

 If FoundIt Then
  holder = Sheets(CheckSheet).Range("ap77")
  If Len(Sheets(CheckSheet).Range("aq77")) > 3 Then holder = holder & "; " & Sheets(CheckSheet).Range("aq77")
  If Len(Sheets(CheckSheet).Range("ar77")) > 3 Then holder = holder & "; " & Sheets(CheckSheet).Range("ar77")
  Range("a1").Value = holder
 Else
  MsgBox "Sheet " & CheckSheet & " does not exist in the workbook"
 End If
End Sub

Tony