PDA

View Full Version : Merge & compare excel formula



naris
08-15-2008, 03:32 AM
I want create a excel macro which can merge or compare two or more excel formulas, anyone can help to give me the vba script for it ? ..


Thanks,
Naris

naris
08-15-2008, 05:03 AM
Sorry I didn't explain clearly my previous question, I want create a macro to compare or merge the formulas in cell B7 & D7 to cell F7 with result same as cell F8

The formula in cell B7 is =SUM(B4:B6) & formula in Cell D7 is =SUM(D4:D6) and I want compare the formula into cell F7 with result like as = SUM(B4:B6)+SUM(D4:D6), I add the formula with symbol ?+? (Plus).
For the detail please see attachment enclosed.

Anyone can help me to solve this ? ..

Thanks,
Naris

Tommy
08-15-2008, 06:05 AM
Hi naris, :hi:

This really should be in the excel forum. The below macro will merge the 2 formulas as you are requesting.


Sub aa()
Dim a As String, b As String, c As String
a = Range("B7").Formula
b = Range("D7").Formula
c = a & Replace(b, "=", "+")
Range("F7").Formula = c
End Sub


Let me know if you require more assistance.

naris
08-18-2008, 06:51 PM
Hi Tommy,

Thank you for your good advice, and I tried other macro same as this macro to do it too, but I have it’s still problem to used it, because this macro will replace all symbol “=” (equal) with “+” (plus) like the macro below :


Sub Trial4()
Range("D7").Select
Selection.Copy
Range("D8").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Replace What:="=", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("F9").Value = Range("B7").Formula & " + " & Range("D8").Value

End Sub



Therefore I tried with this macro and than the result, only one equal symbol in the first digit of the second formula will be delete and than will be added with plus symbol, I have tried in the three example worksheets enclosed.



Sub Sample()

Dim Formu1 As Range
Set Formu1 = Range(Me.RefEdit1.Value)
Dim Formu2 As Range
Set Formu2 = Range(Me.RefEdit2.Value)

ActiveCell = Formu1.Formula & "+" & Mid(Formu2.Formula, 2, 1000)

End Sub

I need this macro, because I think it will be useful when I will write a long formula, I’m not must write in one cell, but I can split in many cell to write the formula and than merge all of the formula in one cell.

One more time, I am very thanks & appreciated for your help, have a nice day …


Regards,
Naris

EDIT: Added VBA Code Tags Tommy

naris
08-18-2008, 07:31 PM
Dear all,

I want create a excel addin but when a load this addin, I get a error massage like this : ?error in loading DLL? , what I have to do for solve the problem ? ..
The addin enclosed.

Thanks you.
Naris