Macros and formulas

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

Board Regular
Joined
Aug 6, 2004
Messages
123
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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

Which adblocker are you using?

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
Top