Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jaja
 
Posts: n/a
Default Replace or Substitute for COMBIN function


I have lots of lines in this format to convert into COMBIN function

(39c3 - 37c3)

In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3))

I have so many lines in the above format. All I want to change the c to
COMBIN with values intact as I have give the example.

Some of the lines are in this format.
(45c6 / (6c5 x (39c1 - 37c1)))

The above line should become
((COMBIN(45,6))/((COMBIN(6,5)*((COMBIN(39,1)-((COMBIN(37,1))))))))

Thanks in advance.
  #2   Report Post  
Posted to microsoft.public.excel.misc
JE McGimpsey
 
Posts: n/a
Default Replace or Substitute for COMBIN function

One way:

I assumed you wanted the cells to become a formula. If not, delete the

"=" &

from the

sTemp = "=" & .Text

line. (I also didn't include a lot of your parens - but I couldn't
figure out your pattern):

Public Sub COMBFormula()
Const sCOMB As String = "COMBIN($$)"
Dim rCell As Range
Dim rTextCells As Range
Dim nPos As Long
Dim nStart As Long
Dim nChars As Long
Dim sTemp As String
Dim sTemp2 As String
On Error Resume Next
Set rTextCells = ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rTextCells Is Nothing Then
For Each rCell In rTextCells
With rCell
If .Text Like "*#c#*" Then
sTemp = "=" & .Text
nPos = InStr(sTemp, "c")
Do
sTemp2 = Replace(Replace( _
sTemp, ")", " "), "(", " ")
nStart = InStrRev(Left(sTemp2, nPos), " ") + 1
nChars = InStr(nPos, sTemp2, " ") - nStart
sTemp = Left(sTemp, nStart - 1) & _
Replace(sCOMB, "$$", _
Replace(Mid(sTemp, nStart, nChars), _
"c", ",")) & _
Mid(sTemp, nStart + nChars)
nPos = InStr(sTemp, "c")
Loop Until nPos = 0
End If
.Formula = Replace(sTemp, "x", "*")
End With
Next rCell
End If
End Sub


In article ,
"Jaja" wrote:

I have lots of lines in this format to convert into COMBIN function

(39c3 - 37c3)

In the above line I want to convert it to ((COMBIN(39,3)-(COMBIN(37,3))

I have so many lines in the above format. All I want to change the c to
COMBIN with values intact as I have give the example.

Some of the lines are in this format.
(45c6 / (6c5 x (39c1 - 37c1)))

The above line should become
((COMBIN(45,6))/((COMBIN(6,5)*((COMBIN(39,1)-((COMBIN(37,1))))))))

Thanks in advance.

  #3   Report Post  
Posted to microsoft.public.excel.misc
Jaja
 
Posts: n/a
Default Replace or Substitute for COMBIN function

Is this function for Excel? If yes then where do these lines go to? Sorry I
have never worked that way. Looks like its some form of programming like Java
or C language.
  #4   Report Post  
Posted to microsoft.public.excel.misc
JE McGimpsey
 
Posts: n/a
Default Replace or Substitute for COMBIN function

It's the VBA language, which is the language that Office macros are
written in.

See

http://www.mvps.org/dmcritchie/excel/getstarted.htm


In article ,
"Jaja" wrote:

Is this function for Excel? If yes then where do these lines go to? Sorry I
have never worked that way. Looks like its some form of programming like Java
or C language.

  #5   Report Post  
Posted to microsoft.public.excel.misc
Jaja
 
Posts: n/a
Default Replace or Substitute for COMBIN function

Sorry about the parenthesis disfigure. I have worked it out. Also I have
simplified it for each format as written after OR.

The original format
(45c6 / (6c5 x (39c1 - 37c1)))

OR
45c6
( _________________________ )
(6c5 X (39c1 - 37c1))


The above format should become like this
((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))

OR

(COMBIN(45,6))
( _______________________________________________ )
((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))


Easiest way to convert is just enclose it in bracket. For example

xcy should become (COMBIN(x,y))

x and y is enclosed in a bracket with COMBIN outside enclosed again with
outer bracket.

The rest of the bracket in the original format will stay intact.


  #6   Report Post  
Posted to microsoft.public.excel.misc
Dana DeLouis
 
Posts: n/a
Default Replace or Substitute for COMBIN function

xcy should become (COMBIN(x,y))

Just an additional idea. If you would like to try Regular Expressions, then
set a vba library reference to:
'// Microsoft VBScript Regular Expressions 5.5

This returns the following:
(45c6 / (6c5 x (39c1 - 37c1)))
(Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
678755

Sub Demo()
'// Your string variable:
Dim s As String
s = "(45c6 / (6c5 x (39c1 - 37c1)))"
Debug.Print s

'// Our Program:
Dim re As New RegExp
Const fx As String = "Combin($1,$2)"

With re
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "(\d+)c(\d+)"

s = Replace(s, "x", "*")
s = .Replace(s, fx)
End With
Debug.Print s
Debug.Print Evaluate(s)
End Sub

HTH. :)
--
Dana DeLouis
Win XP & Office 2003


"Jaja" wrote in message
...
Sorry about the parenthesis disfigure. I have worked it out. Also I have
simplified it for each format as written after OR.

The original format
(45c6 / (6c5 x (39c1 - 37c1)))

OR
45c6
( _________________________ )
(6c5 X (39c1 - 37c1))


The above format should become like this
((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))

OR

(COMBIN(45,6))
( _______________________________________________ )
((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))


Easiest way to convert is just enclose it in bracket. For example

xcy should become (COMBIN(x,y))

x and y is enclosed in a bracket with COMBIN outside enclosed again with
outer bracket.

The rest of the bracket in the original format will stay intact.



  #7   Report Post  
Posted to microsoft.public.excel.misc
Dana DeLouis
 
Posts: n/a
Default Replace or Substitute for COMBIN function

I have lots of lines in this format to convert into COMBIN function

If you wanted to call this function in a loop, one wouldn't want to
initialize Re with each call.
Perhaps make Re a public variable as in this example. You may want to
remove the Evaluate line as well.

Option Explicit
Public Re As RegExp

Function ToCombin(s As String, Optional Eval As Boolean = False)
If Re Is Nothing Then Set Re = New RegExp
Re.Global = True
Re.Pattern = "(\d+)c(\d+)"
ToCombin = Replace(s, Chr(120), Chr(42)) 'x - *
ToCombin = Re.Replace(ToCombin, "Combin($1,$2)")
If Eval Then ToCombin = Evaluate(ToCombin)
End Function

Sub TestIt()
Const s As String = "(45c6 / (6c5 x (39c1 - 37c1)))"
'What you asked...
Debug.Print ToCombin(s)
'If you want to evaluate the equation
Debug.Print ToCombin(s, True) 'True to return number
End Sub

Returns:
(Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
678755

--
Dana DeLouis
Win XP & Office 2003


"Dana DeLouis" wrote in message
...
xcy should become (COMBIN(x,y))


Just an additional idea. If you would like to try Regular Expressions,
then set a vba library reference to:
'// Microsoft VBScript Regular Expressions 5.5

This returns the following:
(45c6 / (6c5 x (39c1 - 37c1)))
(Combin(45,6) / (Combin(6,5) * (Combin(39,1) - Combin(37,1))))
678755

Sub Demo()
'// Your string variable:
Dim s As String
s = "(45c6 / (6c5 x (39c1 - 37c1)))"
Debug.Print s

'// Our Program:
Dim re As New RegExp
Const fx As String = "Combin($1,$2)"

With re
.Global = True
.IgnoreCase = True
.MultiLine = True
.Pattern = "(\d+)c(\d+)"

s = Replace(s, "x", "*")
s = .Replace(s, fx)
End With
Debug.Print s
Debug.Print Evaluate(s)
End Sub

HTH. :)
--
Dana DeLouis
Win XP & Office 2003


"Jaja" wrote in message
...
Sorry about the parenthesis disfigure. I have worked it out. Also I have
simplified it for each format as written after OR.

The original format
(45c6 / (6c5 x (39c1 - 37c1)))

OR
45c6
( _________________________ )
(6c5 X (39c1 - 37c1))


The above format should become like this
((COMBIN(45,6))/((COMBIN(6,5))*((COMBIN(39,1))-(COMBIN(37,1)))))

OR

(COMBIN(45,6))
( _______________________________________________ )
((COMBIN(6,5)) X ((COMBIN(39,1)) - (COMBIN(37,1))))


Easiest way to convert is just enclose it in bracket. For example

xcy should become (COMBIN(x,y))

x and y is enclosed in a bracket with COMBIN outside enclosed again with
outer bracket.

The rest of the bracket in the original format will stay intact.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA function for "Mean" using Array as argument ASokolik Excel Worksheet Functions 21 March 28th 06 10:05 PM
How to replace column letter in refferences with a function using the old column letter? Dmitry Kopnichev Links and Linking in Excel 6 October 13th 05 09:09 AM
creating function (vba) with range arguments Fredouille Excel Worksheet Functions 2 September 12th 05 11:01 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM


All times are GMT +1. The time now is 08:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"