Free Microsoft Excel 2013 Quick Reference

Vba function for concatenating with comma and space, remove trailing comma

Hello,

I need to concatenate invoice numbers and separate them with a comma and a space.

I don't want a trailing comma at the end.

I have repeated instances of the same invoice number but I only want the concatenation to pick up one instance of each.

For example, if I have the data:

30251111
30251111
30251111
30251112
30251112
30251112

I want it to appear as:

30251111, 30251112

Is anyone able to provide me with a VBA function to copy/paste into a module that will do this for me?

Many thanks.


Post your answer or comment

comments powered by Disqus
Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

Sorry I posted this in the wrong newsgroup originally.

Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

Does anyone know of any code for custom functions or VBA functions for Avg,
Std, Min, Max? I had been using the Excel functions (i.e.
application.worksheetfunction.) but have run into the 65326 contraint when
trying to apply these to an array populated by an Excel range. I need to
pass the VBA array to these VBA functions to get the Avg, Std, Min, Max.

Thanks

EM

I have the following scenario
A1 - 0.50
A2 -

I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

I'm up against a truly mind-boggling issue.

I'm attempting to create a dynamic config file in Excel that will be used by an application. This file needs to be strictly formatted. Here's a dummy example:

#I'm a header

#I'm another header with more information.

domain,hostname
domain,hostname
domain,hostname
The file needs to export as a .txt.file. I have this working 99% of the way.
My difficulty is that the resulting file looks like this:

#I'm a header

#I'm another header with more information.

"domain,hostname"
"domain,hostname"
"domain,hostname"
For the domain,hostname, I'm actually concatenating the domain and comman to the front of the hostnames from an external source. If I change the comma to something else, like a + (plus), I do not get the quotes. The issue is that the application I'm dropping this config file into requires the comma and I can't use anything else.

I suspect Excel is trying to be helpful by specifying the literal string whenever there's a comma. I do NOT want it to do this. I can't figure out how to prevent it, however.

I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.

To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.

If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.

Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

I'm trying to create an identifier on data extracts outs of the accounting systems where the identifier field/cell could have many different descriptions for the same thing. See attached. I would like to use a vba function where i could create a series of if/ then statements to identify each line item by a grouping type. On the attached sample the identifier fields would be type and unit price.

I seek your help with the following VBA use of SUMPRODUCT with Variables and OR-ing:

My Goal: Using VBA with SUMPRODUCT and EVALUATE and Variables, write code that will do the following (as an example):

If (Col A='PGV' or Col A='OAK') and (Col B='2008') and (Col C='Real') then add Col D

Worksheet Details:

---A-------B------C------D---
--PGV----2008---Real---400--
--OAK----2008---Real---200--
--BBB-----2008---Fake--100--
--PGV-----2007---Real---600--

The results of the above equation applied to the worksheet would be 600, record 1 and 2 would add, but records 3 and 4 would fail the conditional statement (record 3 because Col A = 'BBB', and record 4 because Col B = '2007').

Working VBA:

The following VBA code works perfectly:

Answer = Evaluate("SUMPRODUCT(--(($A$3:$A$1000=""PGV"")+($A$3:$A$1000=""OAK"")), --($B$3:$B$1000=""2008""), --($C$3:$C$1000=""Real""), --($D$3:$D$1000))")

PROBLEM VBA:

I need to make this code flexible to allow for multiple uses. If I replace the above code with variables, I have the following (this does not work):

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV"")+($A$3:$A$1000=""OAK""))"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

HINT #1 TO SOLUTION:

If I remove - from Piece1 - the second conditional statement, the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV""))"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

HINT #2 TO SOLUTION:

If I remove - from Piece1 - the second conditional statement, but keep the "+" operator (as an OR) the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV"")+1)"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

IN SUMMARY:

I'm trying to use SUMPRODUCT with EVALUATE with VARIABLES and ORing.

I'm able to write working code with all of these pieces, except that the ORing statement is making for sleepless nights.

Can you help me with this?

Thank you

Hi there,

I have run out of ideas on this one and now I've lost my fresh morning mind,
so any help would be much appreciated.

I have a list of garments and each style has a different number of sizes in
its size range from a minimum of 1 available size to a maximum of 24
available sizes, so I have 24 columns for sizes.

I want to concatenate the available sizes for each style separated by a
comma and a space.

A simplified version of the data as it currently stands is like this:

A B C D E etc.....out to 24 columns
1 Stock Item Name Available Sizes
2 Stock Item A 8 10 12 14
3 Stock Item B S M L
4 Stock Item C OS

and the result I want in one column (single cell) is:

A B
1 Stock Item Name Available Sizes
2 Stock Item A 8, 10, 12, 14
3 Stock Item B S, M, L
4 Stock Item C OS

The problem when I use concatenate or "&" across the 24 columns is that I
end up with commas and spaces after the available sizes for all of the
remaining blank cells.

I can't use IF because I can't embed that many IF statements in the one
formula.

Hope this makes sense. Thanks.

I want to program a custom function using VBA. The normal Excel function I would type in a worksheet cell is:

=INDIRECT(ADDRESS(ROW($M7),N$1627,1))

which would give the the result in the cell located at the cell address for the row and column numbers noted in cells M7 and N1627, respectively.

What would be the VBA code for a custom function to shorten this formula in order to derive the same result?

First off I am not expert in this area.

I am using excel 2000 on WinXP Pro SP2.

I have used a vba function for calculating like NetworkDays function except this custom function (NetworkDaysMisc) can calculate the number working week weekdays that you specify (e.g Mon to Sat). Code can be found here -> http://www.bettersolutions.com/excel...I327411311.htm

this works very well and does what I want. Then under the doubleclick subroutine of the worksheet I have code to add and remove rows depending on what the cells contents is. Here is my code;
 
Sub InsertRow()
    Dim DeleteRow As Boolean
    
    DeleteRow = False
    
    If ActiveCell = "Double click here to add SR" Then
        Selection.EntireRow.Insert
        ActiveCell = "New SR Entry"
        Application.SendKeys "{F2}"
        Application.SendKeys "{HOME}"
        Application.SendKeys "+{END}"
        'ActiveCell.Offset(1, 0).Select
        'ActiveCell.Font.Italic = True
        'ActiveCell.Font.ColorIndex = 15
    Else
        DeleteRow = True
    End If
    If ActiveCell = "New SR Entry" And DeleteRow Then
        'ActiveCell.Offset(-1, 0).Select
        ActiveCell.EntireRow.Delete 'shift:=xlShiftToLeft
        Application.SendKeys "{F2}"
        Application.SendKeys "{HOME}"
        Application.SendKeys "+{END}"
    End If
End Sub

Ok. Now on my spreadsheet I have a cell to calculate my number of working days of a date range using the vba function like I first stated. The cell formula looks like this

=module1.networkdaysmisc(K2,K3,I34:I48,J18:J24)

So now the problem I am experiencing that I hope someone can help me with please. When I double click my cell with contents of "Double click here to add SR" and new row is inserted above that cell. And the cell that uses the vba function to calculate the workdays displays correct value. But then if I double click the cell with contents "New SR Entry" that is in the newly created row, the row deletes as it should but now the cell calculating the networkdaysmisc becomes #VALID!. And I cant work out why? If I go to the cell using the networkdaysmisc function I find the reference cells and ranges are correct and so all I need to do on this cell is enter edit mode (F2 key) then press enter and is now display correctly.

I have tried moving/relocating the cell reporting the networkdays up above where the rows can be added and deleted but this make no difference. So it make me think that something like vba code needs to be restarted. I dont know really.

I figure I need to add some code, or the sheet is not refreshing/updating. Can anyone figure this out and help me?

I have though of vba code to move to this problem cell (ActiveCell.Offset(2,0).Select) and simulate the F2 and enter keys. But there must be a better way and a reason why this happen and how to avoid this problem.

Thanks in advance,

Paul.

I am currently tracking online PPC keyword reports with Excel and need to know if there is a function that will find and match words and phrases and then add the columns that are queried for the matching words/phrases. I think an example is definately in order.

December PPC
Keyword Clicks Cost Revenue
large dogs 45 .18 $12.00
small dogs 35 .25 $15.00

January PPC
Keyword Clicks Cost Revenue
large dogs 12 .14 $8.25
small dogs 18 .18 $5.35

Now using the example above I need a function or maybe even a macro that will scan all "keywords" and find a match for each keyword each month, say large dogs, and then add the clicks, costs, and revenue columns and post them on a particular row or rows.

So when running the function it will find and match "large dogs" for each month, it will then add the clicks for all months with "large dogs" in it and then populate a column or row with the total along with the keyword "large dogs" next to it. Is there a function that will do this or maybe even a macro?

New to all this, so please excuse the beginner question: I am building a small contact database and want to concatenate two address entries. As you might expect, I need a comma after the the first entry with a space following. The problem is that in most cases, there will not be a second address, i.e., that cell will be blank. If I add the comma by concatenating a comma and space between the two address entries, for example, =(b4&", "&g4), then the comma shows up whether there is a second address or not.

It seems like there should be a conditional statement that returns a comma and space plus the second address if there is a second address present, and returns nothing if the second address cell is blank. I can't find an easy way to do this. I can create a clumsy series of conditional statements like, "=CELL("type", address) followed by =IF(previous)="b", ", ", " "), and then concatenate some things together, but I can't quite get that work, either.

To summarize, is there a straightforward conditional statement that does what I'm looking for?

I've been trying to figure out the offset function for the past day and seem to be getting close, but am not quite there. I have read numberous threats about the function, but am not able to fully understand what is going on or how it works.

I have a spreadsheet with data from everyday for the past three years on it. I wrote a macro where the raw data is entered and the user clicks "update graph" it is then the raw data consolidated and formatted to be entered to a graph. I am running into trouble with setting the OFFSET function so the graph updates correctly.

- I want the graph to show the last 355 days of data.
- The data for date starts in A3, number of sales starts in B3, and the average sales start in T3.
- I want the the graph to show the number of sales, average number of sales, vs the projected number of sales per day.

I am having trouble with what to set for the arguments while naming the offset formula. Any help would greatly be aprreciated!

Hi,

Is there some kind of translation dictionnary between excel functions and vba function?

For example :

find in excel = instr in vba
lower in excel = Lcase in vba
etc...

Thank you for your help

Good day All!!!

I'm currently working on creating a new function that works with dates and calculates experation. In a way it sounds very simple but I can't get VBA to work correctly. The imput in to the function is a cell containing a date. The function suppose to purform the following task:
1. If the cell is empty - output of the function is ASAP
2. If the cell has N/A - output of the function is N/A
2. If (the date in the cell + 365 days) is greater than today's date - output is Missed
3. If (the date in the cell + 330 days) is greater than today's date - output is Soon

JavaScript Date and Time Functions

JavaScript Date and Time Functions - full listing of all JavaScript functions for dealing with date and time.

I have a data input file which is mostly composed of of numerical data.
However, after several rows of numerical data, there is a row of text to separate different test cases.

I want to code a "do until" loop that will look at each cell in column A. I want to include an "if then else" statement that will end the "do until" loop when it reaches a cell with text and then select that same cell with the text. I want it to stop when it reaches a cell with text period. Not just for a specific word or something. I'm not trying to delete the cells with text or anything either. I'm just trying to figure out how to write the "condition" for the "if then else" statement right now.

I'm trying to build a VBA Function for Excel 97 that will return TRUE if the word is valid, and FALSE if it isn't. I built the following, but the result is always FALSE. Any ideas?

Here it is:
Function SpellCheckTest(InputText)
SpellCheckTest = _ Application.CheckSpelling(InputText)
End Function

Dear Freinds,

I made a macro with the help of following VBA Code which delete some columns and formate the sheet. I want to add one more conditional formatting in below code.
I have Column "J" for Currency with "KWD" and "USD" and in Column "K" amount is mentioned and the amount in not in proper formate some has decimel and some has not.

I Need help. I want if the column "J" = "KWD" then the amount column "K" will add three decimel and if column "J" = "USD" then the amount column "K" will add only two decimel with amount.

Sub DeleteColumn()

Range("N:N,T:T,U:U").Select
Selection.Delete Shift:=xlToLeft
Range("B1:J250").HorizontalAlignment = xlCenter
Range("M1:N250").HorizontalAlignment = xlCenter
Range("P1:R250").HorizontalAlignment = xlCenter
Range("M1:M250").NumberFormat = "0"


End Sub


what i want to do is run a for loop that was do the following:

i have a userform, where a user insert the necessary information, then by clicking on a command button, the info should bestored in other textboxes on the same userform.

example:

1.user insert info in textboxes : TBnumber and TBdescription.
2. user then clicks on command button
3. TBnumber and TBdescription is then stored in TBno1 and TBdescrp1
4. TBnumber and TBdescription is then cleared
5. user types in info like in 1.
6. user clicks on command button
7. TBnumber and TBdescription is then stored in TBno2 and TBdescrp2
ect...

TBno1.... TBno5 and TBdescrp1.... TBdescrp5 is already created on the forum

i made use of a for loop, with a counter... but am lost!

Hi,

I have a set of four raw values that I want to put in a calculation - Debt/Ebitda, OAM/TA, OpProfit and QuickRatio. Now, each value must fall between the cap (max possible value) and the floor (min possible value) for that combination of value (four types in total, as above) and industry (ten types in total). Say OpProfit for Agriculture has a range from 0 to 0.4, so if I have a ratio of 0.5 I want to use 0.4 in my calculations. I have to do a VLOOKUP-esque VBA function for this currently.

Also for Debt/Ebitda it's slightly different - if the raw value falls below the floor, the cap value is used.

Is there a way of capping / flooring my raw values using VBA functions before I put them in my equation? I know you can do it using a series of IF() worksheet functions but I'm already running the calculations using a VBA function.

Thanks loads

Hi,

I have a problem and maybe somebody can give me some good advice.

The problem is that I need to replace delimiter dots with commas, otherwise numbers will be handeled as text. When I simply use Replace function from Edit menu, it work perfectly - dots will be replaced with commas and text will be converted to numbers.

I tried to record this function as a macro, but it doesn't work in macros. It replaces dots with commas, but text won't be converted to numbers and I can't use them in functions as SUM etc. Its strange because it works when done manually but doesn't work in recorded macro.

How can I fix that?

Thank you!


No luck finding an answer? You could always try Google.