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

i have a column of data that needs to be replaced from the left. the string that needs to be replaced has an inconsistent length due to a set of digits (8-15 in length) but it has a definite stopping point at an open parenthesis.

I thought something like this would work
Columns("C:C").Select
Selection.Replace What:= _
        Left(Selection.Value, InStr(1, Selection.Value, "(")) _
         , Replacement:= _
        "", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False

but it doesn't. I suspect because my selection is the entire column. any help would be appreciated.
0
Industry Leaders: We Want Your Opinion!
Industry Leaders: 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!

Devart has released new Devart Excel Add-in Packs — an excellent way to purchase either all Devart Excel Add-ins together or add-ins only for databases or only for cloud apps, and get a huge discount.
0
Dear Experts:

I would run a macro that performs the following actions:

If row 1 and row 2 (both conditions have to be met at the same time) have entries, copy the second row and place it right below the second row so that 3 contiguous rows result.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
I have a code that creates a code in a module, but when it does the VBA window flashes up while the code is running.
I have tried 'Application.screenupdating = false', and it does not work, I have also tried    'ThisWorkbook.VBProject.VBE.MainWindow.Visible = False' at the beginning and end of the code but the window still pops up for a short time. I would like to run the code without the vba window being visable at all.

this is the code:



Sub code
   
        Set vbp = Application.VBE.ActiveVBProject
    Set vbc = vbp.VBComponents.add(vbext_ct_StdModule)
    vbc.Name = "CreateButton"
    strCode = "Sub CreateButtons()" & vbNewLine & _
    "ActiveSheet.Buttons.Add(840, 10, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""ClaimsLog""" & vbNewLine & _
    "Selection.Characters.Text = ""Claims Log"" " & vbNewLine & _
  "  With Selection.Characters(Start:=1, Length:=35).Font " & vbNewLine & _
      "  .Size = 10" & vbNewLine & _
   " End With" & vbNewLine & _
 "ActiveSheet.Buttons.Add(840, 40, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""UNM23Report""" & vbNewLine & _
    "Selection.Characters.Text = ""UNM23 Report"" " & vbNewLine & _
  "  With Selection.Characters(Start:=1, Length:=35).Font " & vbNewLine & _
      "  .Size = 10" & vbNewLine & _
   " End With" & vbNewLine & _
 "ActiveSheet.Buttons.Add(840, 70, 95, 25).Select " & vbNewLine & _
   " Selection.OnAction = ""SPLEtool""" & vbNewLine & _
    …
0
Hi,

In a MS Excel sheet I have two sheets.
SheetA = Addresslist1 (Name, address, phone, mobile)
SheetB= Addresslist2 (Name, address, phone, mobile)
SheetA and SheetB contain different names and addresses.

The first three columns on SheetB are used to match an Address from SheetA to the Address on SheetB.
On SheetB I want to select a Name (Naam) (dropdown) from SheetA which then will be shown in the field. Addionally it needs to retrieve the phone (telefoon) and mobile (mobiel) number belonging to that Name.

One more important point:
The addresslist on SheetA is regulary updated completely. Data is then copied from a different Excel file. The lookup in SheetB must remain available, but there must be an easy way to reset/clear all selected addresses in SheetB, so a whole new selection can be made in SheetB. The addresses in sheetB wil also have regular inserts or updates.

Attached is an example which might be useful for clearification.
In order to understand how it needs to be setup, I prefer to get some instruction with it.
Excel-Lookup.xlsx
0
Hi Experts. I was able to source this VBA code from a question answered but I need to modify it slightly. I have X number of workbooks with Y number of worksheets in each book. I have information that I would like to compile onto a single master data workbook. The code below is able to gather the data from the first sheet from all the selected workbooks. I need it to do the same for the remaining worksheets. Please help me with this request! Would really appreciate the help. :)

Sub MergeTest()

    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim LastRow As Long

    ' Create a new workbook and set a variable to the first sheet.
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Open the file dialog box and filter on Excel files, allowing multiple files
    ' to be selected.
    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Loop through the list of returned file names
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        ' Set FileName to be the current workbook file name to open.
        FileName = SelectedFiles(NFile)

        ' Open the …
0
Hi,
Using these codes (in the project),
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;
            Row row;
            if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }
            if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                Cell refCell = null;
                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }
                Cell newCell = new Cell() { CellReference = cellReference };
                row.InsertBefore(newCell, refCell);
                

Open in new window

0
Getting a Type Mismatch error in excel vba when trying to open a form.schedules.xlsm
0
Today I noticed that when bigRng has multiple cells bigRng.activate and bigRng.select seemed to do the same thing.

That was really weird because I always thought that .select was used to select multiple cells, and that .activate would make ONE of those cells active.
So, my typical code might be
[a1,c1,e1].select
[c1].activate

After some confusion I discovered the .activate logic surpisingly subtle.  I believe the following code describes exactly what happens for bigRng.activate


Option Explicit

Sub myActivate(anyRng)
    If Intersect(Selection, anyRng.Cells(1)) Is Nothing Then
      ' if rng's first cell does NOT overlap current selection, then rng.select and rng.activate are identical
        anyRng.Select
    Else
       ' otherwise, if rng's first cell DOES overlap current selection, that cell is activated
        anyRng.Select
        anyRng.Cells(1).Activate
    End If
End Sub

Open in new window

Does anybody disagree with this interpretation?  Also, I spent quite a bit of time googling for any documentation of what happens when bigRng has multiple cells, but I never found anything that confirmed or denied my interpretation. I will award extra points to anybody that can find such documentation.

rberke

p.s.  I used the following code to convince myself that my interpretation is correct. If anyone disagrees, perhaps they can use similar code to prove me wrong.



Sub test()
Dim rBefore As Variant, anyRng As Variant, correct As String, mine As String
0
I need help with formulas or macro(s) to pull information from one sheet (the "CF" sheet) and populate another sheet ("Buy Sell Sums" sheet).  I think it's too complex for Sumifs.   My CF sheet is dynamic and the number of rows can change from day to day.  In a nutshell for the upcoming trading day I need to sum buys, sells, interest (if any), maturities (if any) and transfers (if any) for each account.  I've filled in the values with formulas in the Buy Sell Sums but need a way to automate this daily so I can click a macro and re-populate it.  The tricky part would seem to be the accounts since the row it falls in can always change from day to day depending on the level of activity.
Cash_Flow-06-26-17-PM-Test.xlsm
0
On Demand Webinar: Networking for the Cloud Era
LVL 8
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

I am hoping that it is possible to reference a date string in the worksheet tab name in a formula.

Production worksheet tab name format:
Planning_DD-MMM-YY

Number of production worksheet tabs per workbook:
3

Deadline lookup table:
Deadline_Dates tab

Formula I am looking for to determine if a WO is completed on time or not:
Based on date in production worksheet tab (ie., Planning_12-Oct-17), the formula would check the 'Deadline_Dates' lookup table for the deadline listed for 12-Oct-17 (in this case 05-Apr_17) and determine if the deadline has been met by comparing it against the date listed in Col G (Date_WO_Completed), and then populate Col H as follows:
  • If date in Col G is < or = to deadline date, then Col H = 'Yes'
  • If date in Col G is > than deadline date, then Col H = 'No'
  • If Col G is blank, then Col H is blank.

I have included a sample spreadsheet with the 3 production tabs and the desired results listed in red.

Thanks,
Andrea
Index_Match_Date_Deadline_Worksheet.xlsx
0
I have a shared workbook that has buttons which perform different commands (save, print, create new files, flag results, etc.) based on VBA macros.  A fresh new workbook is created every day from a master workbook.  After the workbook is created, lab results are entered throughout the day by three people who share this workbook.  Over a 24 hour period, the buttons on the worksheet will sometimes change size and shape, and sometimes they will not trigger the macro when clicked on.  Most days, there are no problems with the buttons.  The buttons are form controls, not Active X.

Screenshots of the worksheets with normal and misshapen buttons are attached.  Also attached is the master workbook with the macros that are attached to the buttons.

What can cause the buttons to change shape and size and lose functionality?
Worksheet-with-good-buttons.JPG
Worksheet-with-bad-buttons.JPG
Shared_Master-AB3-and-Q1-Log.xls
0
I have the following function in a google spreadsheet and need to adapt it so that it does not show duplicate values.
=IMPORTRANGE("tracker!A3:A1000")

Open in new window

0
a                        b                      c                 d                    e                        f                      g                 h
1  start_dates      end_dates      code        6/5/2016      6/12/2016      6/19/2016      6/26/2016      7/3/2016
2  6/12/2016          6/19/2016           house                                    x                  
3  6/19/2016          6/26/2016             flat                                                                x            
4  6/19/2016           1/1/2017            car                                                                x                       x                   x

                 a                            b                         c
1      week_beginning           code                  sales
2      6/5/2016                   bike                     100
3      6/5/2016                   house                  75
4      6/5/2016                   car                        64
5      6/5/2016                     flat                       350
6      6/19/2016                   car                       0
7      7/3/2016                      bike                    65

So basically there are promotions that last for varying lengths. The x's indicate the start and end dates for those promotions. I need to pull through the sales for the correct 'code', for the specific dates. I am not sure how to go about this so I would be hugely grateful for any help!!

So for example, I want to know all of the sales for 'car' on 6/19/2016, 6/26/2016 and 7/3/2016. So I need a formula that will find the specific 'code', 'date' and if there is an x pull through the …
0
Hello,

I am attempting to go through an excel sheet with about 8000 job titles. All of these job titles have been typed in differently, for example CEO, C.E.O, Chief Executive Officer, etc. I want to go through and make everything have the same format. How do I make it so when it sees C.E.O, it types Chief Executive Officer? I will then be able to change the code myself to the other job titles. I just need a starting off point.
0
I have this solution

need to filter after  it  query de data

for exmple

with number 803 it found 7 records

but i need select just 3 sets  of that

to move to a new sheet call ToSee
29040080a--1-.xlsm
0
Hello,
i have an excel spreadsheet 2013:


Here are my conditions:
Cells: A1, B1, C1 ,D1 and E1

if A1 <> '' , clear B1, C1, D1 and E1
if B1 <> '' , clear A1, C1, D1 and E1
if C1 <> '' , clear A1, B1, D1 and E1
if D1 <> '' , clear A1, B1, C1 and E1
if E1 <> '' , clear A1, B1, C1 and D1


how to write formula to do the above task.

Thanks
0
After searching EE, I did not find anything.  Trying to do:

I am using the following definations.

Set Wb1 = Workbooks.Open("C:\_Excel\CompareSheets\Book1.xls") 'Target file (gets the red)
Set Wb2 = Workbooks.Open("C:\_Excel\CompareSheets\Book3.xls") 'Master file
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Ws1LR = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
Ws1LC = Ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set ColRng = Ws1.Range("A2", Ws1.Cells(Ws1LR, Ws1LC))

Open in new window


Then based on this ColRng

For Each c In ColRng
    celladdress = c.Address
    If c <> Ws2.Range(celladdress) Then
        c.Interior.ColorIndex = 3
    Else
        c.Interior.ColorIndex = 0
    End If
Next c

Open in new window


So as a result, some cells in a row will have red cells and others not.

My goal is, before, it starts checking the next row down, to check the current row .  Is there a way to check the row?
If there are no cells in this row that got highlighted red. Delete the entire row, shifting them up.  Or after the process runs, go back and start removing rows that have no highlighted cells in it?  Thus the only thing left would be rows that have a least one red highlighted cell in it.  The code that highlights the cells red is below.  Please advise and thanks.

Sub CompareTwoSheets()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Ws1LR As Long, Ws1LC As Long
Dim c As Range, ColRng As Range
With Application
'    

Open in new window

0
Work around - close errors and open files from recent list within excel

The template for excel is corrupt

How do I delete the normal.dot version for excel ?

Tried office repair - same
0
Technology Partners: We Want Your Opinion!
Technology Partners: 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!

I found the following code on internet and appears to do exactly what I need to do, however there is a bad line, Ln12.  How would I rewrite this line, so the code works correctly?  Please advise and thanks.  I would like the cells that are different highlighted in each sheet tab, assuming there is only one sheet tab per workbook.


'This code will compare two excel sheets.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\1.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\2.xls")

Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorksheet2 = objWorkbook2.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
    If cell.Value &lt;&gt; objWorksheet2.Range(cell.Address).Value Then
        cell.Interior.ColorIndex = 3
    Else
        cell.Interior.ColorIndex = 0
    End If
Next

Set objExcel = Nothing

Open in new window

0
Set ws = Worksheets("PartsData")  Means ?
0
Hello experts, we're having an odd issue.  Woring in Excel a couple of users are constantly presented with "Not enough system resources to display correctly" followed by both monitors flickering black then back on, then a video card crashed error.  I've updated the Video driver to the latest, I've gone into excel and checked ignore DDE, same issue removed check for Igfnore DDE same issue.  Not sure what else to try.

Anyone have an ideas?  Thanks in advance.
0
I have an odd issue where duplicate records are showing in Excel through an ODBC query. When I run the same query in SQL or looking at a web browser the records are not duplicated. Any ideas? I tried opening the spreadsheet on different machines and also created a new spreadsheet
er.png
0
public HttpResponseMessage ER_GenerateWBLWorksheet2()
    {

        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        if (xlWorkSheet == null)
        {
            Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
        }

        xlWorkSheet.Cells[1, 1] = "Account Number";
        xlWorkSheet.Cells[1, 2] = "Amount";
        xlWorkSheet.Cells[1, 3] = "Code123";
        xlWorkSheet.Cells[1, 4] = "Date";
        xlWorkSheet.Cells[1, 5] = "Audit";
        xlWorkSheet.Cells[1, 6] = "ID";
        xlWorkSheet.Cells[1, 7] = "Customer Name";
        xlWorkSheet.Cells[1, 8] = "Payment Source";
        Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range("A2", "A2");//add this drop down list to all rows of first column
        Microsoft.Office.Interop.Excel.DropDowns xlDropDowns;//code to add drop down list
        Microsoft.Office.Interop.Excel.DropDown xlDropDown;
        xlDropDowns = ((Microsoft.Office.Iterop.Excel.DropDowns)(xlWorkBook.ActiveSheet.DropDowns(Type.Missing)));
 …
0
C2=12/31/16
C3=and
C4=12/31/15
C5=X

If C5=X
DECEMBER 31, 2016 and DECEMBER 31, 2015
BUT iF C5=is not checked (X'd)
then
December 31, 2016 and December 31, 2015
NOTE: REGARDLESS OF THE SELECTION, C3 IS PRINTED EXACTLY AS ENTERED (This is because sometimes the report needs to read DECEMBER 31, 2016 and DECEMBER 31, 2016.)
EE-Beginning-and-Ending-Date.xlsx
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.