Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

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.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello,

This question is in follow-up to another recent thread located here:

Modify formatting of selected intracellular text

In that thread, a VBA solution was provided to alter the formatting of certain portions of an Excel text entry while leaving the remaining portions unchanged. That solution (as far as I can determine with my limited knowledge of VBA) operates by including, within the code, the literal strings of text (ie the specific words) which are to be reformatted.

The follow-up question now is:

Can the VBA code described above be modified so that in place of the strings of text (which need to be reformatted), it contains variables which obtain those strings from say, an adjacent column in the spreadsheet?

I'm attaching a file that illustrates two possible options for the functionality I've got in mind. The first option (in the sheet tab labeled Text_Strings) is shown in the first screenshot below. The second option (in the sheet tab labeled MID_Function) is shown in the second screenshot below.

Note: I believe the second option (MID_Function), if it is doable, is a much better way to go than the first since it allows for a more precise designation of the text strings to be reformatted and also eliminates potential problems due to duplicate or repeated strings.

Option #1 Text_Strings
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hello!

I have created a button that I want to allow the user to be able to print just certain sheets of the workbook. I am wanting to create a macro for the button that prints columns A-J, but NOT column C, from the worksheet "summary". Then I would also like the worksheet called "service rates" to print right after the "summary" sheet. It would also be great if both sheets fit to the page so that they aren't being cut off!

Thanks so much :)
0
i have this solution but need to improve with vba
to count the triples from 4 numbers
num4triple.xlsm
0
I had this question after viewing Set font smaller and allow box to expand.

Assistance is greatly appreciated.
0
Hi all

 I'm hoping you can offer some advice please!! I have googled as much as I can to get to this point. I have a workbook, and I would like some coding that identifies if a contract is a "Contract - Framework" AND has a status of "Contract Awarded" on the "Procurement Tracker" tab. I would then like the relevant rows that meet this criteria to be copied over to another tab called "Live Contracts" and hidden on the original Procurement Tracker tab.

 This is the formula I have so far that will move the rows that meet the "Contract - Framework" criteria, I would just like to know how to amend it so that the row meets both conditions;

 
Sub ReqToLive()
 Dim xRg As Range
 Dim xCell As Range
 Dim I As Long
 Dim J As Long
 I = Worksheets("Procurement Tracker").UsedRange.Rows.Count
 J = Worksheets("Live Contracts").UsedRange.Rows.Count
 If J = 1 Then
 If Application.WorksheetFunction.CountA(Worksheets("Live Contracts").UsedRange) = 0 Then J = 0
 End If
 Set xRg = Worksheets("Procurement Tracker").Range("F1:F" & I)
 On Error Resume Next
 Application.ScreenUpdating = False
 For Each xCell In xRg
 If CStr(xCell.Value) = "Contract - Framework" Then
 xCell.EntireRow.Copy Destination:=Worksheets("Live Contracts").Range("A" & J + 1)
 xCell.EntireRow.Hidden = True
 J = J + 1
 End If
 Next
 Application.ScreenUpdating = True
 End Sub

Open in new window

Hope that makes sense! Thanks in advance
 Amy
0
I have been attempting to use this Macro that I found through VBA Express.  The Macro successfully disables the ability to right-click cut/copy/paste but the keyboard shortcuts are still active.  Any help is greatly appreciated.  I have very little knowledge about writing Macros and usually go to Google to find already existing codes to use.  Please see below:

'*** In a standard module ***
Option Explicit
 
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     …
0
I had this question after viewing Produce the count value next to each filterable item in a spreadsheet.

I am using this macro with data from another sheet and the results are cut-off due to all the data not fitting in dialog.

If possible, the results dialog should automatically expand as necessary to fit all the text to the right, and be in a smaller font size.
CountItemsv2.xlsm
0
I have an excel sheet. One column has company names and another column has company 1 line description, there is multiple description entries (of different types) for same company. So, company ABC is repeated 10 times in column A with 10 corresponding different descriptions in column B, then company PQR is repeated 16 times in column A with 16 corresponding different descriptions in column B (after company ABC's entry).

Out come needed : Find all the unique words for company ABC from all the description lines corresponding to Company ABC and display count of every unique word for Company ABC ( e.g. "good" : 5 times , "the" :10 times , "big" : 7 times etc). And similarly for company PQR and other companies in the sheet.
0
Dear macro experts, please help.
I would like the macro to adjust (shrink or increase) row height anywhere in sheet depends of the text in cells. The rows and columns have Merged Cells and Wrapped Text. The text is always in other two rows and in same 11 column from B to L. Column length is static. That two rows need to be same size and the text need to fit.
Adjust.xlsx
0
Experts:

I have come across a wonderful (ready-to-use) Excel sample file that allows multiple values to be selected (in single cell) via a drop-down box.   I found the example at:  https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

I would like to modify the code provided by Sumit Bansal.   Specifically, I need multiple drop-down (i.e., for every record in an XLS, I need the drop-down for assigning POCs).

Sumit's code below allows to use the drop-down for cell C2 only though (line: "If Target.Address = "$C$2" Then").   For testing purposes, I tried modifying the code with only two cells.   The proposed VBA "If Target.Address = "$C$2" Or "$C$3" Then", however, does not work.

My question:   If I wanted the drop-down to work for, e.g., 50 rows, how can I change the code?   That is, "If Target.Address = "$C$2:$C$51" Then" does **not** work.

Thank you in advance.
EEH





Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$C$2" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue 

Open in new window

0
[Webinar] How Hackers Steal Your Credentials
LVL 8
[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I am attaching a file with dummy data. How can I format the scatter series on this chart so that the data points are clearer e.g in the CD111 control chart the data points are bunched together. in the PB 208 control chart the data points are a bit clearer but the labels are still overlapping. In SB121 control chart, they are also a bit melded together :-)
How can I make them easier to read? I'm using Excel 2010 so I wouldn't have all the capability of 2013...thanks.
Control_chart_multiple_data_points_.xlsx
0
Please help! I'm receiving a "Problems During Load" prompt when I try and open up my XML file. I am able to open it in NotePad, but I can't seem to get it open no matter what I do. I would really appreciate if someone could take a look!

I attached the file.
BCNMitosisCopy.xml
0
How do deselect a cell or range of cells in Excel Office 365 2016?

CTRL +click does nothing  -  clicking in an empty cell and hitting delete does nothing

Microsoft, where is the instruction?

Thanks to anyone who can help me get pass this.
0
I would like to add another condition to the current formula in Column C, which is based on the entry in Col B. The current formula is:

=IFERROR(IF($B2="","",IF(RIGHT($B2,3)="YES","",TEXT(TODAY(),"dd mmm yyyy"))),"")

The format for Col B is always:
AA-DD MMM YYYY-NO
or
AA-DD MMM YYYY-YES

I would like to add the following condition to the above formula, based on the entry in Col E:

If Col E = "Cancelled" OR "Deferred", then populate Col C with date from Col E (formatted as above — TEXT(TODAY(),"dd mmm yyyy")

The format for Col E is always:
Cancelled-DD MMM YYYY
or
Deferred-DD MMM YYYY

Lastly, if both Col B and Col E have entries, then I would like the date from Col E to be entered in Col C, not the date from Col B.

I have attached a sample spreadsheet to show the results I am hoping for.

Thanks!
Andrea
EE_Sample_TwoConditions.xlsx
0
Hello,

Is there any advantage to using the Excel =CONCATENATE() function vs simply stringing items together using ampersands?

The only difference I can discern is having an additional function (with its parentheses) present and comma delimiters in place of ampersands.

Thanks
0
I have an excel sheet with 6 different room types in a hotel. These room different items and quantities of those items in each room. One room may have 2 lamps, where as the other may have 1. I need to insert the aggregate data into 1 table in order to make an accurate budget of all the items that I will order.
Hotel-1.xlsx
0
I have this code which will find matching sets of three numbers (and colour them for ID) across a worksheet. (sample sheet attached 007-quad-ID-query-ee.xls )

Could it be adjusted to find those sets of three numbers which match 5 or more times ?
__________________________
Code :

Option Explicit

Private Type Sets
    strAddr As String
    strCells As String
    lngColor As Long
End Type

Sub IdentifyDuplicates()

    Dim lngLastRow As Long
    Dim lngLastColumn As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Dim DupeSets() As Sets
    Dim strSet As String
    Dim lngFind As Long
    Dim lngFound As Long
    Dim lngColors()
    Dim lngNextColor As Long

    lngColors = Array(13494512, 11599871, 13626575, 15723724, 15258845, 12178907, 8518399, 11461045, 14667418, 14136257, 10074816, 5369343, 9491089, 14071663, 12683685, 13233150, 11596768, 14541491, 15259071, 15654653, 10668797, 7791807, 12504966, 13674644, 13743867, 8759804, 6146693, 10728776, 12552565, 11963641, vbYellow)
    lngNextColor = 0

    ReDim DupeSets(0)
    lngLastRow = Range("A65536").End(xlUp).Row
    lngLastColumn = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column

    For lngRow = 4 To lngLastRow
        If Cells(lngRow, 1) <> "" Then
            For lngCol = 8 To lngLastColumn Step 4
                strSet = Cells(lngRow, lngCol) & "," & Cells(lngRow, lngCol + 1) & "," & Cells(lngRow, lngCol + 2)
                …
0
I have this code which sorts groups of 6 numbers into all possible sets of 3 (of which there are 20)

Could someone help and alter it so that it sorts 6 numbers into all possible sets of five please ?

Public Sub Triples()
    intNumbers = 6
    Set wsData = ThisWorkbook.Worksheets("Sheet1")
    For rowCurrent = 4 To wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
        colOutput = 8
        For i1 = 1 To intNumbers - 2
            For i2 = i1 + 1 To intNumbers - 1
                For i3 = i2 + 1 To intNumbers
                    wsData.Cells(rowCurrent, colOutput).Value = wsData.Cells(rowCurrent, i1).Value
                    wsData.Cells(rowCurrent, colOutput + 1).Value = wsData.Cells(rowCurrent, i2).Value
                    wsData.Cells(rowCurrent, colOutput + 2).Value = wsData.Cells(rowCurrent, i3).Value
                    wsData.Cells(rowCurrent, colOutput + 3).Value = ""
                    colOutput = colOutput + 4
                Next
            Next
        Next
    Next
End Sub
0
Hi Experts,

In Windows 10 and Excel 2016. using a Sharepoint 2013 site with a New Team Site and Logged in as a user or even the Site collection owner (global admin) when attempting to create a new list via the SharePoint app "Import from Spreadsheet" we get the error message "An unexpected error has occurred"

This only appears to happen in Window 10 not Windows 7, although on our Windows 7 computer we are using Excel 2013 so not sure if the issue is related to the version of Excel or not.

Steps to reproduce:
1) Create an Excel spreadsheet (using Excel 2016 in our case) with a single worksheet & basic data. Save and exit Excel.
2) Login to SharePoint online team site as site collection owner.
3) Site Contents-->Add an app-->Import Spreadsheet
4) Enter List name, then click "Browse" button to open .xlsx file location
5) Click "Import"

Result: we get the error message "An unexpected error has occurred" See attached screenshot

Have tried to add the sharepoint URL to the list of Internet explorers trusted sites and see the same result. Regardless where the excel file is saved (ie local or a network location) we see the same issue and seems to affect all users

Any ideas what's causing this or how to resolve?
unexpected_error.jpg
0
MS Dynamics Made Instantly Simpler
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Hello,

I have a macro that allows for new sheets to be created based off the amount of rows in the "Work Item" column in the posted excel database. Right now the "work item" updates into the worksheets created from the "template" spreadsheet, but the "description of work item" does not update into the "description job"  column in the templates, and I would like it to. Could someone please help me with this? Thanks!
0
Hi,
I'm the Systems Admin for a SME
Some word/excel doc's on our file server have my username populating the 'Last Saved By' field yet I have not accessed these files
Has anybody else seen this behaviour ?
I'm sure my account hasn't been compromised, I have however changed my password and run multiple anti virus/malware scans on my workstation & file server
0
In Excel 2007, how do I change for only one cell from dot to comma as separator for decimal value?

I want to keep the global language setting to English and keep global dot as decimal separator, and only change for this cell to a comma as decimal separator. Is that possible?
0
Thanks to Shums solution to my last question, I have now moved forward to the final question. The attached worksheet has a routine to add a new line on the first line where there is no value in column B, after B15. This is done with the Add a Line button. There is also an unused button now titled "Compound the Interest."

This routine needs to add a line using the same date in column B as the previous line AND copy the contents of the previous line columns C, F, G, H, I, J and K to the new line AND copy the amount of the previous line's column I to the new lines Column D and Column J.
Bill_Golden_NOTE-PAYABLE_v3.xlsm
0
Hi,

I have 20 worksheets with several data sheets (not every sheet), that I need to consolidate to a master file.  Not all worksheets are the same format.  I am saving them all in one folder.  What I need is a macro, to open all the files, copy from each one the data into a newly created mater workbook please.  On the first copy I would like all the sheets headers, but on the subsequent copies I would just want to copy the data starting at a certain row depending on which sheet (eg ignoring repeating headers).  I do also have hidden columns that need copying across and would like it all in paste special values.  Can you please help I am not great with macros...
0
Hey Guys


Hope you can help
Ive been asked to come with a way to price check invoices against what we have on our purchase order system

I have a report that pulls from our system with the prices that have been entered on there as well as the invoice from the supplier with their prices.

In theory, they should match but we are having some issues

Ive been trying to find a way in which to paste the internal report into Sheet 1 and the invoice into Sheet 2 and on Sheet 1, against the PO references, pull the prices from the invoice on Sheet 2.

I have tried INDEX and MATCHING, LOOK UP and ABS references but the problems I have are two-fold:-

1)      The formatting can change in the way the purchase number is displayed on the invoice – some have the PO012345 and some have just the last five digits. This would mean that the solution would have to search using a wild card plus the unique last five digits across the two sheets.

2)      Not all the invoices are the same in the way in which they are sent therefore, I have been trying to find a way to search the WHOLE sheet and not just a certain column etc, which has proven unsuccessful

I have attached a sample sheet. Sheet 1 has our internal report, Sheet 2 has the supplier invoice.

In column O, I have manually entered what should be the result of the formula / VBA. From this example you can see that O27 and O35 would not match and therefore would flag an anomaly between the two documents.

Ideally if there is anyone…
0

Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

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.