Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spilt data into two columns
Function GetOt(rng As Variant, Noh As Variant, Amount As Variant) As Variant
If rng Like "HRS" Then GetOt = (Noh) & "-" & (Amount) Else GetOt = "" End If End Function The result is: DESC NOD/NOH AMOUNT RESULT DYS 31.00 550.00 HRS 60.00 150.75 60-150.75 DYS 10.00 460.30 DYS 10.00 115.10 DYS 31.00 450.00 HRS 60.00 123.00 60-123 DYS 31.00 450.00 HRS 80.00 164.00 80-164 HRS 9.00 22.14 9-22.14 DYS 31.00 950.00 HRS 105.00 455.44 105-455.44 HRS 23.00 119.72 23-119.72 DYS 31.00 1,000.00 HRS 89.00 406.06 89-406.06 HRS 23.00 125.92 23-125.92 This the is the result of my macro, but instead to segregate my result by( Data -text to column,) I want spilt this result into two columns directly by macro. Please I f any one can help. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spilt data into two columns
On Nov 5, 9:31 am, Alam wrote:
Function GetOt(rng As Variant, Noh As Variant, Amount As Variant) As Variant If rng Like "HRS" Then GetOt = (Noh) & "-" & (Amount) Else GetOt = "" End If End Function The result is: DESC NOD/NOH AMOUNT RESULT DYS 31.00 550.00 HRS 60.00 150.75 60-150.75 DYS 10.00 460.30 DYS 10.00 115.10 DYS 31.00 450.00 HRS 60.00 123.00 60-123 DYS 31.00 450.00 HRS 80.00 164.00 80-164 HRS 9.00 22.14 9-22.14 DYS 31.00 950.00 HRS 105.00 455.44 105-455.44 HRS 23.00 119.72 23-119.72 DYS 31.00 1,000.00 HRS 89.00 406.06 89-406.06 HRS 23.00 125.92 23-125.92 This the is the result of my macro, but instead to segregate my result by( Data -text to column,) I want spilt this result into two columns directly by macro. Please I f any one can help. Thanks Can you better describe your problem? I do not know what you are trying to do with this function. Brian Herbert Withun |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spilt data into two columns
If your data is in column A (before text to column), then the function would
only have one input parameter and return only one value (functions only return one value). this should be close to what you need. let me know if I can make any additional changes. call with =GetOt(A1) Function GetOt(rng As Variant) As Variant If Left(rng, 3) = "HRS" Then Noh = Trim(Mid(rng, 4)) Amount = Trim(Mid(Noh, InStr(Noh, " "))) Amount = Trim(Left(Amount, InStr(Amount, " ") - 1)) Noh = Val(Trim(Left(Noh, InStr(Noh, " ") - 1))) GetOt = Format(Noh, "0.00") & "-" & Format(Amount, "0.00") Else GetOt = "" End If End Function "Alam" wrote: Function GetOt(rng As Variant, Noh As Variant, Amount As Variant) As Variant If rng Like "HRS" Then GetOt = (Noh) & "-" & (Amount) Else GetOt = "" End If End Function The result is: DESC NOD/NOH AMOUNT RESULT DYS 31.00 550.00 HRS 60.00 150.75 60-150.75 DYS 10.00 460.30 DYS 10.00 115.10 DYS 31.00 450.00 HRS 60.00 123.00 60-123 DYS 31.00 450.00 HRS 80.00 164.00 80-164 HRS 9.00 22.14 9-22.14 DYS 31.00 950.00 HRS 105.00 455.44 105-455.44 HRS 23.00 119.72 23-119.72 DYS 31.00 1,000.00 HRS 89.00 406.06 89-406.06 HRS 23.00 125.92 23-125.92 This the is the result of my macro, but instead to segregate my result by( Data -text to column,) I want spilt this result into two columns directly by macro. Please I f any one can help. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Data into columns without replacing the columns with data | New Users to Excel | |||
Help in code Steve G wrote to move data from 4 columns to 21 columns | Excel Programming | |||
Spilt Entire Name into Two columns | Excel Discussion (Misc queries) | |||
Spilt value and currency | Excel Discussion (Misc queries) | |||
Date spilt into four cells | Excel Programming |