Microsoft Excel

134K

Solutions

226

Content

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Excel VBA: share procedures / macros with users

Hello experts,

I have multiple vba procedures which I would like to share with final users so they can re-use it when they open excel application. I was wondering what is the best way to automatically share with them those procedures. I was thinking to add all those procedures into an add-in file and then share the file however I don’t know if it is the best way.

Thank you very much for your feedback.

3 Comments

LVL 1

Question by

agwalsh

3h agoSolved

Can I set up a table that allows users to add comments?

I have a spreadsheet with an Excel table. There is protection applied to the sheet but the table is unlocked for data entry. However, there is no option to add comments to cells. How can I keep the table format and allow users to add comments. Or is that possible when a table format is being used? Thanks

4 Comments

In Excel I have a worksheet that has data in columns. Last Name ,First Name, Address, Email, Phone Number.
Last Name: Smith
First Name:John
Address:15 Main Street
Email:Johnl@gmail.com
Phone Number:888 195-1234
Last Name: Jones
First Name:Bill
Address:25 South Street
Email:Bill@gmail.com
Phone Number:888 123-4567
Etc.
In a second sheet I have the formula =RIGHT(Sheet1!A1,LEN(Sheet1!A1)-FIND(":",Sheet1!A1)) in cell B1 and =RIGHT(Sheet1!A2,LEN(Sheet1!A2)-FIND(":",Sheet1!A2)) in B2
etc for the first 5 columns

I want to take lines 6 through 10 and put the data in row 3.
I know I can write the formula =RIGHT(Sheet1!A6,LEN(Sheet1!A6)-FIND(":",Sheet1!A6)) etc and so on for data in Sheet 1 rows 11-15 etc.

I tried cell references =RIGHT(INDIRECT("Sheet1!"&"A"&CELL("row")),LEN(INDIRECT("Sheet1!"&"A"&CELL("row")))-FIND(":",INDIRECT("Sheet1!"&"A"&CELL("row"))))

etc but it seems to jump all over depending on the last cell clicked on.
Is thera a way that it can use the actual cell as a reference?
So the data from sheet1 will be populated in sheet 2 with the description lines stripped out.
So in this example I will see:
Last Name First Name Address Email Phone Number
Smith John 15 Main Street Johnl@gmail.com 888 195-1234
Jones Bill 25 South Street Bill@gmail.com 888 123-4567

ETC. I could have 25 to 100 or more contact names

Just …

3 Comments

How to copy and paste data with conditional formatting format from minitab worksheet into excel ?
I tried but they copy in values and the format that i had in minitab was not reflected in excel.

2 Comments

Question by

ADRIANA P

13h agoSolved

create sheet for Double digit

create an new sheet Call DD with only the doubles digit

as shown

here the file
29119277a_3N_to_DD_sheet.xlsm

2 Comments

I use a vb script to reformat CSV I receive from a vendor on a daily basis. I've started receiving a new batch of fields (columns) that I need to concatenate AND insert separators between values. Currently, my tortured method is below:

objSheet.Range("X2:X" & lastrow).FormulaR1C1 = "=RC[5] & "";"" & RC[6] & "";"" & RC[7] & "";""& RC[8] & "";"" & RC[9] & "";"" & RC[10] & "";"" & RC[11] & "";"" & RC[12] & "";"" & RC[13] & "";""

However, this results in a whole lot of orphan semi-colons whenever a column is empty. I've tried TEXTJOIN like so:

objSheet.Range("X2:X" & lastrow).FormulaR1C1 = "=TEXTJOIN("";"",TRUE,RC[5]:RC[16])"

but that failed.

Can somebody provide an alternative method that won't end up creating entries like this?

;;;;subject5;;subject7;;;;subject11

9 Comments

Question by

ADRIANA P

21h agoSolved

3N eliminated repeat after keynumber

eliminated the follows after the repeat key number repeat

as shown

3N_follows_to_dd.xlsm

5 Comments

I had a GREAT spreadsheet until I added some lines and columns.

In the account number fields (found throughout) when you entered 1234
you got 1234-00-00
When you entered 12
you got 1200-00-00
When you entered 123456
you got 1234-56-00

Suddenly, not only is that routing not working, once active content is enable, any entry to a custom or numeric field gets a Debug error. Sample spreadsheet is attached.

I know the problem is in VBA, but even when I update the routine with the appropriate cell numbers, ranges, I still get the error.
ABC---Last-National-Bank---1801-Ban.xlsm

5 Comments

HTML5 and CSS3 Fundamentals

LVL 12

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Question by

MATO0618

1d ago

Copying formula down column

I am trying to copy a formula in a range of cells. I placed the worksheet activate and nothing is happening. What am I missing.

Private Sub Worksheet_Activate()
Range("B6").Select

Range("B10:B1000").Formula = "=IFERROR(MID(C17,FIND(" * ",SUBSTITUTE(C17," \ "," * ",LEN(C17)-LEN(SUBSTITUTE(C17," \ ",""))))+1,LEN(C17)),"")"
Range("D10:D1000").Formula = "=LEFT(B10,10)"

End Sub

2 Comments

Question by

Ian Bell

1d agoSolved

find a word in a string

Hi,
I would like a formula to detect a word within a string of words.
example: Detect the word 'pear' from the following situated in cell C2 I picked a pear (or) I picked a plum
Return 1 if word exists and zero if it doesn't.
Many thanks
Ian

6 Comments

Hi Experts,

Hoping someone can help me with a solution to automate a manual daily task
I need top download a file from a http (url - report server) site and save it to a folder at regular intervals ( hourly )

The manual process is as below
1) Go to the website/url using IE
2) Click on a drop-down item and select "Export to Excel" (Actions --> Export (option 3) --> Excel (option 5) --> Its a sub-menu see html below
3) A pop-up appears to Open or Save the .xlsx file
4) Click on Save button
5) Save it to a folder location on my pc.

I cant use any 3rd party tools or software as I cant download it due to restrictions in my organisation.

Preferably excel/vba solution or if there is an inbuilt windows solution to capture and record actions above and then schedule it at regular intervals.

Thanks

HTML:

            <td class="ms-toolbar sqlrv-ToolBarCell" nowrap="nowrap">
      <div id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu">
                              <span style="display:none"><menu type='ServerMenu' id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ctl00"><ie:menuitem id="m_sqlRsWebPart_RSWebPartToolbar_ctl00_RptControls_RSActionMenu_ReportBuilder_forRendering" type="option" onMenuClick="STSNavigate2(event,'http://project/PWA/_layouts/ReportServer/RSAction.aspx?RSAction=ReportBuilderViewer&ReportPath=http%3a%2f%2fproject%2fPWA%2fReports%2fPSD+Extract.rdl');" text="Open with Report Builder" menuGroupId="2147483647"></ie:menuitem>
                              …

0 Comments

The current formula in Column E, shown below, concatenates Columns A, B, C and D to create a unique Product Number:

=A2&IF(B2<>"","-"&B2,"")&IF(C2&D2<>"","-"&C2&D2,"")

Open in new window

I need to update the current formula whereby if the specific text string "-AAU" is in Column B, it is not to be included in the final concatenated Product Number in Column E.

I have included a sample spreadsheet that has the current formula in Column E, and the updated, desired results I am looking for in Column F.

Thanks in advance for assistance with this...

Andrea
Product_Numbering.xlsx

2 Comments

LVL 41

Article by

Subodh Tiwari (Neeraj)

Excel: Programmatically insert a Chart.

Charts are easy to insert manually but sometimes we need to automate the chart insertion and most of the people find it difficult to control the chart creation and its properties via VBA. In this article we will learn about how we can use VBA to insert a Chart and manage it's appearance.

0 Comments

Add Comment

Question by

ADRIANA P

1d agoSolved

selector for data by columns

after the TOTAL sheet is created
ias shown in this sample

then need an selector to select the data and create in SELECT sheet as shown
align

file
29119153_to_improve_0_1_find_total.xlsm

2 Comments

Im new to VBA and got some problems with making a index page from values in my msgbox.

dont under stand why this 2 lines of codes dosent work.

Set wsName = Me.BDClass.Value

Open in new window

and this one

shIndex.Range("B5").End(xlDown).Offset(1, 0).Select

Open in new window

Private Sub AddFromBtn_Click()

    Dim r As Range
    
    'Checks if BDClass allready exist
    If BDClass.Value <> "" Then
        Set r = wsIndex.Range("B:B").CurrentRegion.Find(BDClass.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en klassifikation med samme navn")
                Exit Sub
            End If
        Else
            MsgBox ("Der skal angives klassifikation")
            Exit Sub
    End If
    
    'Checks if BDName allready exist
    If BDName.Value <> "" Then
        Set r = wsIndex.Range("C5").CurrentRegion.Find(BDName.Value)
            If Not r Is Nothing Then
                MsgBox ("Der findes allerede en bygningsdel med samme navn")
                Exit Sub
            End If
        Else
            MsgBox ("Der skal angives bygningsdel navn")
            Exit Sub
    End If
    
    
    'Creates new sheet
    Worksheets("Temp").Copy After:=Worksheets("Index") 'copy Temp to after Index
        ActiveSheet.Name = BDClass.Value 'Renames the sheet to BDClass value
        
    
    'Writes value to new sheet
    ActiveSheet.Range("C3") = BDClass.Value
    ActiveSheet.Range("E3") = BDName.Value
    
    
    'Writes value to Index

Open in new window

…

2 Comments

Question by

ADRIANA P

2d agoSolved

create the find from total

create the find

the find total have to be exact match
then create the TOTAL sheet as show

29119060a_to_add_find_total.xlsm

8 Comments

Question by

Ajay Anand

2d ago

ways to make VBA code secure

I a am Civil Engineer and I am using VBA for Excel from last 10 years. Recently I developed a macro to generate payslips of employees and my friend's company purchased it.

I think more companies require that tool which can make their life easier and I am also interested in the monetary benefit.

But a person with a little knowledge in VBA can break the password, will be able to steal the code and I don't want that to happen. I am looking for ways to make the code secured.

In this regard I expect expert's help.

Thanks in advance
Ajay

4 Comments

Learn SQL Server Core 2016

LVL 12

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

LVL 1

Question by

Michael Keith

2d agoSolvedPrivate

Calcule a borrowing rate called TIC

I need some help coming up with a present value formula to calculate what we call in our industry the true interest cost of debt or TIC. In the attached file is the calculation produced by software that we use however it does not provide formulas. I would like to come up with a spreadsheet that I can use to calculate TIC on my own. About the spreadsheet that I have provided. At the top is the solution to the cash flows below. The dated date is the date that interest begins on the loan the TIC is the effective borrowing rate or the rate necessary to discount the amounts payable on the respective principal and interest payment dates to the purchase price (loan amount) received assuming compounded semi-annually. The first column is the dates payments are made, the second column is the amount of debt paid each semi period and the third column is the discount column calculated by the software to come up with the TIC (discount rate) that equals the target amount above of $10,513,798.96. I would like to setup a spreadsheet that will allow me to calculate on my on the TIC. Meaning if I know the target amount, Dated Date, and semi-annual cash flows the spreadsheet will calculate the TIC that discounts those cash flows so that they equal the target amount. I have tried using the IRR function but it did not work for me. Hoping to get other ideas how to set this up using this example to check that it works by trying to recreate the answer. I hope this make sense but …

2 Comments

LVL 2

Question by

WonHop

3d agoSolved

Remove Leading and Trailing Spaces From a String

Hello All. First let me start by saying "TRIM" DOES NOT Work.

Using Excel VBA, I used the InStr Function to separate the ID Numbers using the comma.
The problem is, this data is in an Excel Cell. The guy used the Alt + Enter to move to the next line after adding the comma.
When I pull it into a variable, I get one of two results.
" Paragragh 3.5.3" or "Paragragh 3.5.3 "
It has a blank space before or after each one. I am not sure it if is a Carriage Return or not.
I have not been able to get rid of it. I need that term to use a search. Because of the extra spaces, they are not being found even it they are there.

Fake Data:

PUNISHER1.2,3,
CYCLOPS,
VISION,
HULK,
GHOST RIDER,
THOR

strLeftToCaptureCheck = Left(strToCapture, 1)
If strLeftToCaptureCheck = "" Then
MsgBox "Check"
End If

As a test I used the code above to see if there was something there.
The result was strLeftToCaptureCheck = "". The "" is not a blank space. It skipped over the message box.

Is there a way to find the first character position or just get rid of whatever those blank space are?
I never know if they are going to show in the beginning or the end. I need a way to look at both ends.

Thanks
Nate

12 Comments

Question by

ADRIANA P

3d agoSolved

Take from data 0 1

I would like be able to get same output as this workbook but without any data in column 'D
29118917_to_add_d_column.xlsm

7 Comments

I use text files as source data to feed Access tables due to 2GB size limitation of MS Access. I am currently using two text files that are merged daily after the one is updated, one of the text files is 2016-2017.txt historical data merged with daily updated 2018.txt file. The merged file size is 1.24 GB and I can work just fine with it without any problems.

Lately I have decided to use a bigger size text file for a larger historical analysis like going back to 2012. I compiled text files into one 2012-2017.txt file using Windows commands on C:\ prompt and added my daily updated 2018.txt file into this history so I could work with one historical data set all the way back to the year 2012. This is the method that I use daily for my current daily updates anyway without any issues. The expanded file size is 3.45 GB and when opened in Excel Power Pivot it displays 90 fields. My goal is after loading the data model in Excel power pivot cerate various pivot tables for historical data analysis.

The problem is that when I merge daily refreshed 2018.txt file into the historical file then connect to the resulting merged file via Excel Power Pivot (Access fails to open this file due to size but power pivot works ok along with Power BI) and insert a pivot table what I see is that all the years' total sales dollars from 2012 to 2017 get displayed correctly but 2018 is incomplete by a huge margin. I tried to load and re-load several times in different Excel files and I …

2 Comments

I am importing from excel to ms sql table. I keep getting   in all my empty fields. Can someone help me to fix this so that the empty fields are just empty? All the data is correct just the blank fields.

5 Comments

LVL 1

Question by

LD16

3d agoPrivate

Excel VBA: remove lines based on attributes

Hello experts,
I have the following report attached.
I am looking for a procedure to remove blue lines.
Procedure should:
1-Remove Output sheet if it exists
2-Copy Input Sheet
3-Rename copied Sheet with Output name
Remove the various blue lines. They have in common the following attributes: Values of C column=As Code, values of D column=Resource Organization, values of J column=Projects, Values of K column Project ID
4-Add headers based on blue lines removed in topic 3

I attached example file.
If you have questions, please contact me.
Remove-lines-based-on-values.xlsm

5 Comments

First
Prev
1
2
3
4
5
6
7
Next
Last

Microsoft Excel

134K

Solutions

226

Content

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Related Topics

Microsoft Excel

Related Topics