Microsoft Excel

135K

Solutions

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.

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

Sign up to Post

I have a workbook that has numerous sheets in and a summary at the end.

What im trying to do is link of which I know how to do but I cannot copy and paste link and transpose all at once as the original data is on sheet horitiontally and new data needs to vertically but retaining paste link values.

Any Ideas?
0
OWASP: Forgery and Phishing
LVL 13
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

I created the following code to open an Excel file and select a particular sheet based on the Property Code that was previously copied into the clipboard.  HOWEVER, it stay minimized and blinking on my bottom toolbar.  I googled and try adding the 3 lines starting with Set MyObj=CreateObject("WScript.Shell, but that had no effect.  Any suggestions to bring it to the forefront and maximized.  Note, I start the whole process from a button in a Filemaker dbase layout so Filemaker is in the forefront and unfortunately remains there.  Thank you.

Dim xlApp, xlWb

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open("N:\EXCEL\Files\SHARED TOOLS\Sales Reports\SALES REPORTS.xlsm")

xlApp.Run "'" & xlWb.Name & "'!SelectSheet"

Set MyObj=CreateObject("WScript.Shell")
myobj.appactivate "Microsoft Excel"
set myobj = nothing

'if the macro takes arguments, use:
'xlApp.Run "'" & xlWb.Name & "'!NameOfMacro", "Arg1", "Arg2", "Arg3"


'xlWb.Close True
Set xlWb = Nothing
'xlApp.Quit
Set xlApp = Nothing
0
Excel - IF and OR statement

=IF(AND(P821>1001)*OR(N821="O",W821<>"Test",W821<>"Flush",W821<>"Open",W821<>"Run"),"HIDE","SHOW")

This should return a HIDE but it is returning a SHOW

P821 is  <  1001   but   N821 is   "O"   and   W821  is  <>  equal  to  Test or Flush or Open or Run

I think this is the order of what I need:
HIDE  - If   P   > 1001
then
HIDE -  If  N  =  "O"    AND   <>  Test or Flush or Open or Run
0
Dear Experts:

I got a set of ActiveX-Control checkboxes on the active sheet whose names all start with 'my_chbx_'

I got another set of ActiveX-Control checkboxes on the active sheet whose names all start with 'chbx_'

For the second set of these ActiveX-Control checkboxes (names starting with 'chbx_') I would like to insert a toggle button that performs the following action:

Set all checkboxes to Value = 1 and run the actions on that value
Set all checkboxes to Value = 0 and run the actions on that value

Help is much appreciated. Thank you very much in advance. Regards, Andreas
0
Dear Experts:

On the active worksheet I got 519 check box controls (normal form checkboxes, not Active X control checkboxes).

They are named as follows: m_cb0001, m_cb0002, m_cb0003 to m_cb0519

I would like to run a macro with the following requirements:

If All of these checkboxes have the ControlFormat.Value = 0 the macro has to say so and exit the sub. If not, another macro called 'MyMacro_2' is to be called up.

The checkboxes in question (m_cb001,m_cb002 etc.) may also be hidden, i.e. the rows in which they reside may be hidden. So the macro has to look for the controlformat.Value in all of them, no matter if the checkboxes are hidden or not.

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

Regards, Andreas
0
Hi Experts,

This is in reference to the following.
I would like to have the lunch column getting populated with the correct amount.

Thanks in advance.
0
Hello experts,

I am looking for the best way to built 3 procedures:

1-Display last used column letter in activesheet
2-Display last used row number in activesheet
3-Display initial Range and end range of activesheet example: A1:C4000

Thank you very much for your help.
0
I have a macro that comes up with an error and cant workout why.

I have the sheet in book 2 called NEW REC but comes up with a runtime error.  

Sub ()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh1a As Worksheet
Dim shary, sh2 As Worksheet, fn As Range, col As Variant, s As Long, colA As Long, colB As Long, colC As Long, colD As Long
Application.Calculation = xlCalculationManual
Set wb1 = Workbooks(1) 'substitute actual name for index #. eg. "MyWkbk.xlsx"
Set wb2 = Workbooks(2) 'always open this workbook after the one called workbooks(1)
Set sh1 = wb1.Sheets("Sheet1")
Set sh1a = wb1.Sheets("Sheet2")
Set sh2 = wb2.Sheets("New Rec")
shary = Array(sh1, sh1a)
    With sh2
        colA = .Rows(1).Find("NCLB", , xlValues).Column
        colB = .Rows(1).Find("MACHINE", , xlValues).Column
        colC = .Rows(1).Find("Date", , xlValues).Column
        colD = .Rows(1).Find("AMOUNT", , xlValues).Column
    End With
col = Array(colA, colB, colC, colD)
For s = LBound(shary) To UBound(shary)
    With shary(s)
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            Set fn = sh2.Columns(colA).Find(c.Value, , xlValues)
                If Not fn Is Nothing Then
                    For i = LBound(col) To UBound(col)
                        If sh1.Cells(c.Row, i + 1) <> sh2.Cells(fn.Row, col(i)) Then
                            Rows(c.Row).Interior.Color = vbYellow
                            Exit For
                        End If
              

Open in new window

0
I have one of my users that is having an issue in Excel.  It seems that any excel file he has, complex or simple calculations has this same issue.  If he leaves the file open, and does other things on his pc, or steps a way for a while, Excel is very slow to respond when he clicks a cell on the spreadsheet.  He has several spreadsheets he updates every day.  He is so used to this issue, that he knows to click on a cell and go work on other things for a minute or so, then comes back and works in spreadsheet.  It acts like excel is hung, but always comes  back and stats working.  You just have to wait.  Everything else on his pc responds great.  He said many other co-workers have told him they have the same issue.  We run Microsoft Office 365 ProPlus, and we do use cloud storage such as Microsoft's OneDrive.  I think most of his files are stored on SharePoint, and this also has cloud storage back to Microsoft.  Almost seems like Excel has its own hibernation mode.
0
I'm having a problem with getting a specific result with an Excel Formula.

As shown in the image below, I'm trying to match data from two Sheets (which represents data from two different sources. For the sake of the picture, the two sheets are shown together). The problem is that one sheet has multiple entries for the same Invoice #, where the other sheet has only one entry. What I need to be able to do is the following:
  1. If 'Value' from Sheet2 is greater than 0, and matches "Item C to match" entry in corresponding Invoice #, then 'pink' Conditional formatting does NOT Apply to any Item for corresponding Invoice # (See red rectangle). This is my problem I'm having, as it applies to items A, B & D which should not be highlighted in 'pink'. "Item C to match" here IS correctly NOT highlighted.
  2. If 'Value' from Sheet2 is greater than 0, and does NOT have a corresponding "Item C to match" entry for the corresponding Invoice #, then apply 'pink' conditional formatting to all items for that corresponding Invoice # (See green rectangle). This works correctly.
  3. If 'Value from Sheet2 is '0' and matches "Item C to match" entry for corresponding Invoice #, then apply 'pink' conditional formatting, but NOT for any item of the same corresponding Invoice #. This works correctly.

Tricky Matching Criteria

Current 'Conditional Formatting' Formula:
=IF(NOT(B1="Item C to 

Open in new window

0
Announcing the Winners!
LVL 13
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

I am running Windows 7 64 bit, Office 2010. At one point someone installed Office 2013 side by side with Office 2010 and I started to get the automation error.  

Since doing that, I now cannot open an Outlook instance through VBA, I am getting an Automation Error: 8008201d - Library Not Registered when I run the following command:

Set objExcel = CreateObject("Excel.Application").  Thus I had Office 2013 removed.  I still had the issue and thus implemented the below solution.  It worked for several weeks, but now I am getting the same error.  When I go back to registry though I no longer see 1.8 so why am I still getting this error.

Solution I used and it worked fro several weeks..
Step 1. Go to Run Prompt and Type “regedit” and click ok Step 2. Expand HKEY_LOCAL_MACHINE Step 3. Expand Classes Step 4. Expand TypLib Step 5. Expand 00020813-0000-0000-C000-000000000046 Step 6. Select 1.8 Step 7. Right Click on 1.8 and Delete this.


Lastly I should mention I do see that MS Info Path 2013 and MS SharePoint Designer 2013 is loaded to my PC - not sure if that is creating the conflict..
0
Macro to convert data in .txt into excel format

Attached is the .txt (input) and .xlsx (output) files.

-I have also stated the type of data in row 4.
-Not all the data in .txt needs to be transferred in xlsx, only those stated in .xlsx (output) needs to be considered.
-Ignore the logic of the numbers. This is just to show what and where the data needs to be placed.
PDF-Page-1-Example_INPUT.txt
PDF-Page-1-Example_OUTPUT.xlsx
0
I'd like to create a button in Excel that allows the Scribble tool to be used, and then left on until the button is repressed.  Is this possible?
0
I have an excel workbook with lots of sheets in it with the colour puple in some of the cells. I want to create a macro that I can run to search the whole workbook and where the colour purple is found replace it with the colour green. Mnay thanks
0
Excel cannot complete this task with available resources. Choose less data or close other applications.

For over a week, one of my coworkers has had trouble with Excel 2010 under windows 7 pro.

Several times a day Excel burps and she can no longer open new workbooks.
Every attempt to open anything gives the error message.
Even Ctrl N fails.
Just closing all workbooks has no affect. Exiting excel and restarting only solves the problem for a few hours.
Running in safe mode reduces the frequency a little.
Office repair does not help.
Multiple reboots do not help.
No addins are running (including excel, COM, XML etc)
Windows 7 is up to date.
There is nothing special about any of the workbooks being opened.
The coworker has had the same general workload for 3 months, but this problem only surfaced this week.

I welcome all suggestion, but we are 8 days away from a massively important deadline, so I wont try anything time consuming  (like sfc /scannow or windows reset) until the deadline passes.
0
Situation

I have two worksheets: one 'SheetA' with several conditional for matting rules and one 'Sheetb' with none.

The CF rules on SheetA are custom formula based.

This is in Excel 2013

Request

I need some VBA to copy all the conditional formatting rules, inc formats / colours, from one sheet to another - can anyone suggest how I can do this ?

I have tried cycling through all the rules on SheetA and then for each adding a rule to SheetB and passing the properties of the rule in SheetA but this fails as all the arguments to the 'add' function are not directly accessible as properties of the existing rules.

Limitations

  1. I can't use 'copy and paste' - as there is no ' PasteSpecial' parameter that only copies CF
  2. I need to let the user define the conditional formatting rules so don't want to set the rules from code (which would be easy)
0
I need the proper syntax to delete an excel file [PROBLEM_LOG.xlsfrom a folder prior to the running the following code.
The following code gets a syntax error. When i manually delete the file gets saved with NO problems.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProblemLog", CurrentProject.Path & "\" & "PROBLEM_LOG.xls", True

Open in new window

0
Gurus,

I would like to know , if there is calendar based entry ( at least current month info)  in excel cell, rather than using manual entries .

Sid .
0
if I make a text file using notepad with the below data and save it as a .csv file then open it up excel I get a scientific number in column 'C'

"2064","Other","889072736164324"

how do I stop that?.   or format it after opening it in excel to show the current information

see attachment
Allresults.csv
0
Build an E-Commerce Site with Angular 5
LVL 13
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

I have 2 workbooks open that I want to compare using view side by side function and also synchroinous syncing .

Is there anyway that I can create a macro so that it looks down the two workbooks  comparing data and if missing from 1st sheet it highlight it.

Looking  at the attachment I want to look in the 1st sheet on the tab selected in columns  B/C/D/E  and check to see if in 2nd sheet in columns A/B/C/D . If not in 2nd sheet can I highlight it on 1st sheet selected tab or put onto a new sheet.

I need it to look all the way down looking for a match in 2nd sheet in columns B/C/D/E.

Is this possible?
screenshot.jpg
0
I have a workbook that I have a tab  in it called "NEW REC" and I have another workbook that has data in it on sheet 1 that  I need to compare.

Data in sheet called "NEW REC" has data in columns A,B,C,D  and this also the case in other workbook as in data in columns A-D.

Im trying to make a compare macro that I can save to a personal macro  so I can use it in different workbooks. I think thats correct?

I will always have the workbook with the tab NEW REC open and I donno if I can set it up so it asks me where to look for other sheet to open to be able to compare.
If I can I then need it to look down the sheet called "NEW REC" and compare against data in new workbook Ive opened.

If there are matches in rows down worksheet  ive opened and also in NEW REC tab that are exactly the same I need them ignored in the  tab "NEW REC" and if dont match  highlight them in NEW REC tab.  

What I mean by this is lets say row 5 columns A-D  match with exactly the same data in workbook I opened in row 99 columns A-D then thats a match and ignore but as stated if nothing compares then need highlighted in new rec tab.


I had this macro but but it compares 2 sheets in the same workbook and as stated im trying to get it to ask me where I want to look to compare

Sub CompareData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim Val As String, ws1 As Worksheet, ws2 As Worksheet, i As Long, v1, v2, RngList As Object
    Set ws1 = Sheets("Sheet1")
 

Open in new window

0
Hello,

I have Data Names in Column A that I want to match up with Data Names in Column B that have a lot of info to go along with it. How can I match these Data Names side by side? Hoping this is possible because want to exract all matching Data Names with info into a new excel sheet.

I uploaded an Excel example.

Thank you,
Data-Match-Test.xlsx
0
i need to convert something like this into the hyperlinks in excel for each word, so then i can read this without the long urls in it just by looking at it and then click word with hyperlink if i desire. thanks!

Need to do this across col D where there is a URL in the cell

Withlocals (The Netherlands), travel tours and activities provider: $8M B led by Keen Venture Partners, participation from Inkef.
0
SharePoint 2013 & Excel 2016 , no PowerShell, owner of all groups with FC on site (no sca)
Doing in access would be most beneficial as well.

2 Questions
Using odata connections in excel  I can
   get a list of the permission groups in a sheet
   get a list of users in a specific group
how do I look through the groups and get users for each group
I did a record macro and created an odata to get the users for a group - however, when doing that it creates a connection file rather then exposing the code to programmaticly insert the group name

Separately,
I need to add/delete users from specific groups?
Don't see how to do that via some sort of REST call i would suppose.

Rather not restort to JS for this.  thanks!!!
I can get the list of SP groups into excel using simple oData connection as well as the members of a specific group.

How, in VBA,, can I add/delete specific members in a group.

I have Full control (only), PowerShell not available, VBA to run in either Excel or Access.  Would prefer not to do this JS.
I suspect through some kind of REST call but I'm fairly new to rest so a working example would be most helpful.
0
Experts Exchange (Martin Liss) helped me with this earlier but further testing (see https://www.experts-exchange.com/questions/29137599/Search-Excel-workbook-match-whole-word-and-extract-results-to-new-worksheet-or-workbook.html#a42812438), after the question was closed, revealed a flaw if the user-selected range was too large (or contained too many empty cells, not sure which). The problem lies with the latest version (29137599f).

It seems testing the Search All Sheets code was on a very small used range. But on March 6  I ran several tests, selecting both a few entire columns and a single entire column, and each time Excel crashed badly. This also happened if I put one small word in an otherwise empty column and ran the code on that entire column. Evidently, the code can handle only a limited range. I also tried using the Type 8 box to select a little more than a million rows (but not the whole column) and the application hung.  I did this with a lot more data than the test sheet (about 6300 rows with data), and as long as the search range did not include a large number of empty cells, it was okay.  To reproduce the error, click the Search button on the sheet, click the 'Search and Extract' button, enter 'test' as the search term, and select the entire columns A through D, or A through M - be sure it includes the whole columns or a large number of empty cells. That will hang the application.

The original macro had a hard-coded search range of from col A to AB and it …
1

Microsoft Excel

135K

Solutions

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.