Concatenate Formulas in Multiple Ranges

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

I have attempted to create a macro to do the following. Even though I know XL reasonably
well, Macros & VBA are not, at all, my strength.

My system is XP Pro with XL 2002.

Here is what I want to do in VBA:
1) For a selected Range;
2) Change the formulas in the Selected Range
From: ='Data Source'!B7 (Initial formula from Paste>Special>Past Link)
To : =IF('Data Source'!B7="","",'Data Source'!B7) (After addition of IF(empty))

A more involved version that I would like the same VBA code to handle:
To : =IF('Data Source'!D7=""," ",CONCATENATE(MID('Data Source'!D7,1,3)," ",MID('Data Source'!D7,4,3)," ",MID('Data Source'!D7,7,4)," ",MID('Data Source'!D7,11,3)," ",MID('Data Source'!D7,14,1)))

Is there a VBA "answer" that can encapsulate various "Paste>Special>Past Link "Selected" formulas with the IF(empty) formulas above?

Replacing data in a cell seems more simple but I am not sure how it best works with formulas.

Thanks in advance!

Dennis
 
Dennis,

The two macros below will do what you want: select the cells
containing the ='Data Source'!B7 type links that you want to convert,
then run the first to just have the simple IF, and the second to get
the MID formulas.

It's likely that line wrapping will screw up one or both: note that
the lines starting with myCell.Formula should be followed by the "Next
myCell" command, so remove any line wrap returns introduced into the
code by OExpress or your news reader, and get that code all onto one
line.

If you have problems, I will send you the code in a workbook so that
line wrap won't be a problem.

HTH,
Bernie
MS Excel MVP

Sub Form1()
Dim myForm As String
Dim myCell As Range

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myForm = Mid(myCell.Formula, 2, Len(myCell.Formula))
myCell.Formula = Replace("=IF(XYZZY="""","""",XYZZY)", "XYZZY",
myForm)
Next myCell
End Sub

Sub Form2()
Dim myForm As String
Dim myCell As Range

For Each myCell In Selection.SpecialCells(xlCellTypeFormulas)
myForm = Mid(myCell.Formula, 2, Len(myCell.Formula))
myCell.Formula = Replace("=IF(XYZZY="""","" "",
CONCATENATE(MID(XYZZY,1,3),"" "",MID(XYZZY,4,3), ""
"",MID(XYZZY,7,4),"" "",MID(XYZZY,11,3),"" "", MID(XYZZY,14,1)))",
"XYZZY", myForm)
Next myCell
End Sub
Dennis said:
I have attempted to create a macro to do the following. Even though I know XL reasonably
well, Macros & VBA are not, at all, my strength.

My system is XP Pro with XL 2002.

Here is what I want to do in VBA:
1) For a selected Range;
2) Change the formulas in the Selected Range
From: ='Data Source'!B7 (Initial formula from Paste>Special>Past Link)
To : =IF('Data Source'!B7="","",'Data Source'!B7) (After addition of IF(empty))

A more involved version that I would like the same VBA code to handle:
To : =IF('Data Source'!D7=""," ",CONCATENATE(MID('Data
Click to expand...
Source'!D7,1,3)," ",MID('Data Source'!D7,4,3)," ",MID('Data
Source'!D7,7,4)," ",MID('Data Source'!D7,11,3)," ",MID('Data
Source'!D7,14,1)))
Is there a VBA "answer" that can encapsulate various
Click to expand...
"Paste>Special>Past Link "Selected" formulas with the IF(empty)
formulas above?
 
Bernie,

Thanks for your help.

What is the syntax to store the effective "XYZZY" to an onscreen query where I can Ctrl-V the original linked formula to the variable "XYZZY"?

How would the systax fit into your previous examples.

Thanks again!

Dennis
 
Dennis,

I'm not sure what you mean by 'effective "XYZZY"'. "XYZZY" is simply a
placeholder string in the more complicated formula, used to denote
places where the link should go.

What exactly is it that you want to do? Copy and Paste links with the
formula already converted?

HTH,
Bernie
MS Excel MVP

Dennis said:
Bernie,

Thanks for your help.

What is the syntax to store the effective "XYZZY" to an onscreen
Click to expand...
query where I can Ctrl-V the original linked formula to the variable
"XYZZY"?
 
Bernie,

What I mean, knowing that XYZZY is a placeholder, how could the placeholder:

1) be initialized as a variable
2) be used in a screen query like: [Enter the Formula to be incorporated in the IF(Empty) code:______________]
3) Finally how would that screen query and variable code be entered into the VBA code previously sent to me?

Sorry for the misunderstanding and thanks!

Dennis
 
Dennis,

Try the sub below.

To use it, first select the cells with the link.

If your want start with, for example, =A1, and end up with
=IF(A1="","",A1):

In the first inputbox, enter (note the single quote at the start)
'=if(blah="","",blah)
In the second inputbox, enter:
blah

You can make formulas as complex as you desire.

HTH,
Bernie
MS Excel MVP

Sub UserInputFormula()
Dim myForm As String
Dim myCell As Range
Dim XYZZY As String
Dim newForm As String
newForm = Application.InputBox("What new formula?" & Chr(10) & _
"(Start with a single quote)")
XYZZY = Application.InputBox("What variable in your formula" & _
" do you want to replace?")

For Each myCell In Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
myForm = Mid(myCell.Formula, 2, Len(myCell.Formula))
myCell.Formula = Mid(Replace(newForm, XYZZY, myForm), 2)
Next myCell
End Sub


Dennis said:
Bernie,

What I mean, knowing that XYZZY is a placeholder, how could the placeholder:

1) be initialized as a variable
2) be used in a screen query like: [Enter the Formula to be
Click to expand...
incorporated in the IF(Empty) code:______________]
3) Finally how would that screen query and variable code be entered
Click to expand...
into the VBA code previously sent to me?
 
Back
Top