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

have this tool need to improve it

in the View sheet need to Select  then create the VER sheet with the selected

need an way to select the set  or sets need to  see in Ver Sheet
29040693DDD.xlsm
0
Salesforce Made Easy to Use
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Good day,
I have an excel file at 407KB and around 2,000 rows. I'm trying to filter the information to copy it to a different sheet within the workbook. however, when I try I always get a prompt saying excel is out of memory. Also, when I copy the entire data without filters, it works fine. Is there any way to fix this?
I am running Office Pro 2016 64bit, 8GB RAM, Win 10. Thanks.
0
I have an Excel spreadsheet that has a training date and in the column next to it, how many people are to be trained on that day.
What cell formula or VBA would I use to print  a roster with that date on that many rows of the roster? For example, if a certain class on July 13 has 25 students, how could I have code that would print a roster with 25 rows, each row with the date and a line beside it for a person to sign in?
Training-Dates.xlsx
0
Hello,

I have a database (attached) that allows for the cost of bids to be calculated. Right now, the button Generate Work Items allows for new sheets to be created based off the amount of rows in the Work Item column in the summary sheet. These new sheets are based off of a template and are linked to the summary page as well as the rate sheet.

However, I now want to change the purpose of this Generate Work Items button to something different .. I now need the button to instead just create a new row in the summary table and a new worksheet for that new row. But still having that new worksheet linked to the summary table, template, and rates sheets as it is now in the macro. Also when the new row is created the total at the bottom of the table should also move down a row. And when the button is clicked to create a new worksheet it would be great if excel could take the user to the new sheet directly afterwards.

For the name of new sheet, it should be the Work Item number from column B in the summary sheet, but the user will not have input that yet when creating the new sheet.. (Right now the work item number in column B is connected to cell B3 on the template sheet that the new worksheets are based off of) Maybe when the user clicks the Generate Worksheets button a prompt could come up that asks the user for the work item number and then the sheet would be created and whatever the user entered as the work item would be the name of the sheet?

Thanks so much, please…
0
Hello,

This is another in a series of questions I have posted recently to come up with Excel VBA code that will selectively reformat a specified string (or strings) of text within a cell, but not the entire cell.

For example, suppose you wanted to reformat a sentence in cell B2 to that shown in D2:

2017-06-27a_EE.PNG
Note that the particular formats shown in this screenshot are arbitrary as the main objective is to have a code which can apply any desired formatting.

Thanks
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,
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
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
Enroll in June's Course of the Month
LVL 9
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

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
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
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
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
            
=PROPER(TEXT($C$2,"MMMM D, YYYY")&" "&LOWER($C$3)&" "&PROPER(TEXT($C$4,"MMMM D, YYYY")))      
      
Returns:
December 31, 2016 And December 31, 2015      Why is the "a" in and being capitalized although I specified LOWER       

Sample file attached with code
EE-Beginning-and-Ending-Date-2.xlsx
0
PeopleSoft Has Never Been Easier
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

C2=12/31/16
C3=and
C4=12/31/16

=PROPER(TEXT($C$2,"MMMM D, YYYY")&" and "&TEXT($C$4,"MMMM D, YYYY"))
Returns
December 31, 2016 And December 31, 2015

I DO NOT want to capitalize the "a" in the separator "and"

Sample worksheet which contains the formulas above is attached
0
Hi Everyone - thanks in advance for taking a look and helping.

I'm trying to build a UDF in VBA that handles cohort math for digital media biz.

It takes as input several 1d arrays, builds a 3d array, then sums along two dimensions of that built array to output a 1d array.

Here's what I wrote in R, which I need to translate into VBA.

 
maus <- function(
  nreach,
  conversion,
  dispersion,
  dl,
  seasonal,
  sl,
  retention
) {
  
  
  n <- length(nreach)
  
  mau.array <- array(rep(0, n * n * n), dim = c(n, n, n))
  
  for (i in seq(n)) {
    for (j in seq(n)) {
      for (k in seq(n)) {
        if (!(i <= (k - j + 1))) {
          next
        }
        mau.array[i, j, k] <-
          nreach[i] * 
          conversion[i] *
          (
            (seasonal[((i + j) %% ds) + 1] * dispersion[j]) /
              sum(
                c(seasonal[(((max(i, j) - 1) %% ds) + 1) : ds], rep(seasonal, floor(dl / ds)))[1 : dl] *
                dispersion[1 : dl]
              )
          ) *
          retention[k - i - j + 2]
      }
    }
  }

maus <- colSums(colSums(mau.array))
return(maus)
  
}

Open in new window



Also attached is a sample workbook where I've tried to start this effort so you can see how I would use the function in Excel.  

I don't know VBA so would appreciate help translating the function I wrote in R into Excel VBA.  

Thank you in advance I really appreciate the help.
Cohort_Math.xlsm
0
Hi,

I am having difficulty setting up what should be a simple formula. I think the issue is that I am trying to copy a date as I believe that IF does not recognize dates

What I am trying to do is:  If F9 = “1”, I want to copy a date from A9 into E9 so I can then count how many time that each month appears in E9 for a yearly report. I have this formula already. F9 will either be empty or will be a 1 which is calculated using a formula.

I can add an additional coulum to copy the date and wrap it in a date value function  and then hide that column if necessary. It would be a bit messy but I just want something to work.

I have also tried
=IF(ISNUMBER(SEARCH

Any help would be greatly appreciated.

Thanks
Dot
0
Hello,

Is there a way, in Excel, to use Conditional Formatting (CF) to set the cell alignment?

For example, the regular Format Cells box includes a tab for Alignment:

2017-06-25b.png
However, the Format Cells box in CF does not include an Alignment tab:

2017-06-25a.png
I've looked around to see if that option is maybe buried in one of the other tabs but with no success.

If modifying the Alignment is not possible in CF, can it be done with VBA code or is there another workaround?

Thanks
0
I have a macro workbook (Macro.xlsm) and a second (already open) workbook (Student.xlsx), with information on "Sheet 1", row 1 that I want to copy to all of the other sheets in the second workbook, in row one.  I found some code online (see below), which works if I have the source sheet in the macro, but I need to have the source to be in the second workbook. Can you please suggest the changes needed?  I have been unsuccessful at numerous attempts to alter this code.

Also, wondered if you can advise how I may copy Row 1 to all sheets except Sheet1 and Sheet2 (I planned on deleting the row on Sheet2 later, but proper coding would save the extra step to delete this row).  Thank you all in advance for your kind assistance.

‘Insert one row on all tabs, except SHEET1, then
‘COPY the first row from SHEET1 to Row 1  in all tabs, except Sheet1

    Dim ws As Worksheet, Source As Worksheet
    Set Source = ThisWorkbook.Sheets("Sheet1")        
    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
   
        If ws.Name <> Source.Name Then
            ws.Rows("1:1").Insert Shift:=xlDown
            Source.Rows("1:1").Copy
            ws.Rows("1:1").Insert Shift:=xlDown
           
        End If
    Next ws

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
0
I have a workbook that has VBA code which produces a number of active worksheets by testing for the presence of an entry in cell E10. However, I want to make that determination by the presence of an entry in any cell E10 through E25.

How do I modify the code to accomplish this task?

Function countCellAddress(cellAddress As String) As Double
Application.Volatile
For Each sht In ThisWorkbook.Sheets
    If sht.Name Like "*-JE-PG-*" Then
        If Len(sht.Range("E10").Value) > 1 Then
            countCellAddress = countCellAddress + 1
        End If
    End If
Next sht

End Function

Workbook is attached.
EE-1612-Journal-Entry-Workbook-Rev1.xlsm
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.