Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Microsoft Excel

131K

Solutions

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

Hi,

Whats the easiest way to calculate the date of each of the following for any given year...

 New Year's Day
 Good Friday
 Easter Bank Holiday
 May Bank Holiday
 Spring Bank Holiday
 Summer Bank Holiday
 Christmas Day
 Boxing Day

Obviously I know some like Christmas be the 25/12, boxing day, 26/12 etc, but Good Friday, Easter Monday etc harder to identify...
0
Free Tool: Subnet Calculator
LVL 10
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I have date A in cell F5 and date B in cell AG11, and both are customized formated as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD"). Then I want to calculate time elapsed in years with one decimal since date A and date B. For example, it might have elapsed "0.5 year" (20171231-20170630).

Then I need the result formated as "0.5 year" or "2.5 years" for example (if more than 1.999999 then "years"; otherwise "year").

Is this possible to achieve without VBA?
0
I formated the cell as a customized date format as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD"). But when I do that I get #### displayed. Why, and how do I correct it? The width of the cell is more than sufficient.
1
I'm using the code below to try and delete all Excel workbook connections, however I get the following error:
Run-time error '424' Object required on line  Do While ActiveWorkbook.Connections.Count > 0
What additional code is needed to execute? Thanks


 Set objApp = CreateObject("Excel.Application")
         objApp.Visible = True
         Set wb = objApp.Workbooks.Open("C:\Users\vincent.Shis\Documents\ALL_Reports.XLSX", True, False)
     
 
   Do While ActiveWorkbook.Connections.Count > 0
      ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
   Loop


    wb.SaveAs "C:\Users\vincent.shields\Documents\ALL_Reports_Test.XLSX"
    wb.Close
    Set objApp = Nothing

    wb.Close
    Set objApp = Nothing

   objApp.Quit
     Set objApp = Nothing
0
The basic constituents are these:

'LOPP 1'!F5: date (current/source)
'LOPP 1'K5: place (current/source)
'Travbanor (tabell)! column B: place (to lookup)
Value for spring: column M (to lookup)
Value for summer: column N (to lookup)
Value for autumn: column O (to lookup)
Value for winter: column P (to lookup)

The function I need is this:

Find place from current/source ('LOPP 1'!K5) in 'Travbanor (tabell)'!column B, and
  if 'LOPP 1'!F5 is in the date interval 1st March-31st May, then for place="Jägersro" or "Åby" or "Kalmar" go to 'Travbanor (tabell)'!column M and retrieve this value to 'LOPP 1'!O28
 
 or if 'LOPP 1'!F5 is in the date interval 1st April-31st May, then for place="Solvalla" or "Färjestad" or "Örebro" go to 'Travbanor (tabell)'!column M and retrieve this value to 'LOPP 1'!O28

  (etc. etc.)

I will add quite a lot more of above in this same cell.

How should this formula best be entered, and how long can it be in one single cell?

I have formated the date as "ÅÅÅÅMMDD" (Swedish for "YYYYMMDD") in O28 and F5.
0
I have an excel workbook that has a range file that I named "SITRange". I need a macro that will open a new Excel workbook, select the file range named SITRange, copy the range contents to the new workbook (copy, paste special, values) in cell A1. I tried recording a macro but it gets stuck when it looks for "Windows("Book5").Activate". (below is copy of what I recorded)

Sub SIT_to_Corptax()
'
'
'
    Workbooks.Add
    Windows("900_16.xlsm").Activate
    Windows("Book5").Activate
    Windows("900_16.xlsm").Activate
    Application.Goto Reference:="SITRange"
    Selection.Copy
    Windows("Book5").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows("900_16.xlsm").Activate
End Sub
0
I need a crash course in using excel basics. I just started a new job and I need to work with spread sheets. When I update them or, paste info into them, I often wipe out the grid, especially when high lighting a row a different color. I wiped out several sections of the grid. How can I get it back?
0
i have the below code, i am trying to learn the Arrays in VBA from this example.

first of all,  this code works.   it puts the codes of first column of table into second sheet

my first problem is, i am trying to dump the entire result into single cell and not in different cells. how can the below be modified.

my second question is that if i do not want to use transpose, why the below Version2 does not work, when i removed the tranpose part from the code?


Sub PopulatingArrayVariable()
'PURPOSE: Dynamically Create Array Variable based on a Single Columned Table

Dim myArray() As Variant
Dim myTable As ListObject
Dim cell As Range
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")
  
'Create Array List from Table
  TempArray = myTable.DataBodyRange.Columns(1)
  
'Convert from vertical to horizontal array list
  myArray = Application.Transpose(TempArray)


  For x = LBound(myArray) To UBound(myArray)
    Sheets(2).Cells(x, 1).Value = myArray(x)
  Next x

End Sub

Open in new window


Sub PopulatingArrayVariableVersion2()



Dim TempArray() As Variant
Dim myTable As ListObject
Dim cell As Range
Dim x As Long

'Set path for Table variable
  Set myTable = ActiveSheet.ListObjects("Table1")
  

  TempArray = myTable.DataBodyRange.Columns(1)
  


  For x = LBound(TempArray) To UBound(TempArray)
    Sheets(2).Cells(x, 1).Value = TempArray(x)
  Next x

End Sub

Open in new window

Book1.xlsb
0
Hi All,

I have an excel workbook and would like to create a macro that deletes all rows where the value in column N = "N".

I don't want to use filtering as there are other Macro's running currently and the people that will be using the workbook are not Excel Savy!"

Any thoughts?

Thank you
0
Hi,

It's taking me very long time to clear out (clear contents) and paste formulas containing any of the above formulas, particularly match() and countif().
Range size is roughly 7000 rows
Calculation mode is set to manual

Is there anything I can do to speed this up?
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!

Hi experts,

   Here is the code that i would want to make it work on only filtered data (visible data). I have been trying various ways to make them count only the visible data but it either shows error or wrong value. Hope someone could help. Thank you.

Code:

=COUNTIFS(INDEX('Current Month'!$A:BJ,0,MATCH("Subject Name",'Current Month'!$A$1:$BJ$1,0)),"pampers",INDEX('Current Month'!$A:BJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0)),"POSITIVE")

Purpose of the formula: The data set is already filtered. Along with the filtration i have 2 other criteria stated in the formula. The formula has to count and show the number.


I also tried:

SUBTOTAL(103,INDEX('Current Month'!$A:BJ,0,MATCH("Subject Name",'Current Month'!$A$1:$BJ$1,0)),"pampers",INDEX('Current Month'!$A:BJ,0,MATCH("Dryness + Absorbency",'Current Month'!$A$1:$AJ$1,0)),"POSITIVE")
0
As you see from the attached screenshot, I am trying to create queries base don the information in the columns. Unfortunately, the date value in the NEXT_DELIVERY_DATE field does not transfer into my query as a date. I know there a way to format it using TEXT within the formula. Any help will be greatly appreciated.screenshot
0
I have created a goggle type search in a combo box on a worksheet. It works fine. However, I cannot get the down or up arrows to work once a list has populated the combo box. The list changes with each letter or number you type, narrowing down the list. The code you see under the "Key down" event is what I found on the internet but it doesn't solve the problem. Please see the attached file.

Thanks,
State-Contract-Price-List---VBA---M.xlsb
0
Excel 2010, have a user with docs that have shaded cells, docs will print with shading, but when viewing the docs, no shading is visible.  This is true for all Excel docs.  Assume it is a setting somewhere, just cant find it.  Any ideas?  Thanks.
0
Is there a way to make the Macro Window larger?
I have a lot of macros and would like to see more of them in one shot. You can't resize the window.
Is there a way to make this window bigger?
Macro Window
0
Hi,

I am using the Sort function in the Win32::OLE package for Perl.
I would like to sort all my columns based on the first 4 columns (sort by: col1 then col2... col4)
For now I am able to sort by the 3 first columns but when I try to do on 4 I get an error in GetIDsOfNames for "Sort" at MyFile.pl

Here's my code:


my $SortOrd1 = $SheetC->Range("A2");
my $SortOrd2 = $SheetC->Range("B2");
my $SortOrd3 = $SheetC->Range("C2");
my $SortOrd4 = $SheetC->Range("D2");

my $Range = "A" . ($dcmRowA+2) . ":" . chr($LastColC+64) . ($LastRowC);
my $Selection = $SheetC->Range($Range);
my %hash = (Key1 => $SortOrd1, Order1 => xlAscending, Key2 => $SortOrd2, Order2 => xlAscending,  Key3 => $SortOrd3, Order3 => xlAscending, Key4 => $SortOrd4, Order4 => xlAscending);
$Selection->Sort(\%hash);

Open in new window



again, if I remove the Key4 => $SortOrd4, Order4 => xlAscending) the code works fine, but I need to sort according to the first 4 columns

Would you know why I get this error with 4 criterias but not 3?

Thanks in advance
Have a good day :)

Rapha
0
Using latest Excel 2016 build 4600 / win10.

How does one get to the property sheet for "on worksheet" objects, such as the button below
xlIt has been a long time I have done Excel work but I am sure there was a possibility in previous versions to select a control and access to said control properties (say size, color, etc). How to I get to it ? I have tried to click on design mode but it doesn't seem to help (or do anything for that matter - the button remains active, contrary to my expectation)
0
Hi there Experts,

I have an Excel workbook with various Formulas and it is expanded from cell A1 to Cell EZ598.
The actual size of this Excel file is 6,6MB and has various records from 2014 till 2017.

I was planning to reduce the Size by splitting the Excel file in two pieces.
Hence I have tried to delete the rows dated before 2017.

When I press the “Delete Rows” option I receive the message:

operation-warning.jpg
After confirming the above action I receive one of two following messages:

Error1.jpgError2.jpg
I am running a Vitual Machine with Win7 Pro and Excel 2013 Std x64.
I have tried to increase and allocate the RAM on the VM to 60GB with 8vCPUs with no luck.

It seems that there is some corruption with this Excel file since I receive the above errors
even when I try to delete a single row (for example from A598:EZ598)

Any ideas?

Thank you in advance,
Mamelas
0
I have this VBA that works great but I want to add a specific sheet name to it.  The sheet name is MAIN_PN.  What would the code be with sheet MAIN_PN added to it?

Sub Trim()
Dim c As Range
For Each c In Range("A1:A8225")
   c = Application.Trim(c)
Next
 
End Sub
0
Free Tool: IP Lookup
LVL 10
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

What is the Access function that provides the same results as MS Excel proper(A,1)
0
hi,
I have about 50 sheets in my excel with  different headings for each sheet. When i search by selecting Options and then Within workbook it is searching only the content not the sheet names.


lets say my excel name is world.xls
i have different sheets like usa, uk, australia, india

usa sheet i ahve state names like georgia, michigan etc


if i search on usa by Options and then Within workbook it is searching only the content not the sheet names and i never get search results which i am looking(like usa state names)
how to include sheet names also in the search.

please advise
0
I'm creating an excel file from a database using a vbscript.  It all works great... but, in a few columns, I have a very long column name, for example, "Number of Missed Appointments" and I want to put that on 3 lines in a cell so it looks like:
Number of
Missed
Appointments
And I want the cell to autofit to the width of the widest line (in the above example, Appointments).

Maybe this will help convey what I'm trying to do/want:

Example of what I get, don't want, and need
I'm using:

WorkSheetObject.Cells(2,13).Value = "No of missed" & vbCrLF & "appointments"
WorksheetObject.Cells(1,13).EntireColumn.Autofit

Open in new window

0
Attached is a workbook which shows the assistance needed. If a value in Col A has a duplicate, or triplicate, all the rows need to be deleted for these entries. Example: Rows 3,4, and 5 would be deleted and Rows 13 and 14 would be deleted from the workbook example.  On a typical day, we could be searching over a thousand rows...very time intensive to be done manually...it seems a VBA solution may be needed.

Cook09
Deleting-Rows-with-Same-Traclking-N.xlsx
0
Hi There,

I'm having a lot of difficulty with a project that I'm working on, about transferring data from Access to a premade Excel worksheet. What I need to do is:

-From an Access table that contains the data, create a query to be able to pull the data for a specific instructor in regards to their schedule.
-Export that data into the appropriate fields in an Excel worksheet that we are required to use.

I've attached both the Excel file that it needs to go into and the database that contains the information that needs to be transferred. The fields that are highlighted in the Excel worksheet are the ones I want to populate from Access, as well as the schedule block at the bottom if that's possible.

If it's possible to do this in an easier way just using Excel, I'm also open to that solution! The only caveat is that this will be shared with colleagues that have minimal Excel and Access skills, so it needs to be user friendly for these people.
1518_InstructionalPlanningWorksheet.xlsx
2018SP.accdb
0
I had this question after viewing Excel how to update data connection link from cell value.


the attached workbook used to work. now it does not.  i get error access denied on the    .LoadSettings strNewURL

how can i overcome this issue,  if i manually import the same string it will work, but it does not work with the VBA

Sub ChangeDataSource()
Dim tbl As ListObject
 
strNewURL = Sheets("mySheet").Range("A1")
 
Set tbl = Sheets("Data").Range("A1").ListObject
 
With tbl.XmlMap.DataBinding
    If .SourceUrl <> strNewURL Then
        .ClearSettings
        .LoadSettings strNewURL
        .Refresh
    End If
End With

End Sub

Open in new window

EE.xlsb
0

Microsoft Excel

131K

Solutions

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.