# Macros and formulas

#### Mark_G

##### Board Regular
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

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.

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

Mark

Sorry to take so long. Try
Code:
``````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

Replies
3
Views
216
Replies
6
Views
521
Replies
8
Views
614
Replies
1
Views
165
Replies
9
Views
269

Threads
1,216,099
Messages
6,128,823
Members
449,470
Latest member
Subhash Chand

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back