Microsoft Excel





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 received this code pasted below from Shums and I need to update it for the following.
1.Instead of using the salesman name use the Employee Id to sort and group, but in the result tab show both ( the ID and Name)
2. On the result tab I would still like the results sorted by salesman name and not Id. ( the order for the name and id on the results tab does not matter.I will have to hide the ID anyways.)

Sub AddingSubTotals()
Dim Ws As Worksheet
Dim LR As Long, i As Long, t As Long, f As Long, FinalRow As Long
Dim StartTime, EndTime

'Disable Events
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
End With

StartTime = Now()
'Set Variables
Set Ws = Worksheets("Data sheet")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row + 1

'Remove previous SubTotals
For t = LR To 2 Step -1
    If Ws.Cells(t, 1) = "Total" Then
        Ws.Cells(t, 1).EntireRow.Delete
    End If
Next t

'Add new SubTotals
For i = LR To 3 Step -1
    If Ws.Cells(i, 1).Value <> Ws.Cells(i - 1, 1).Value Then
        Ws.Cells(i, 1).EntireRow.Insert xlShiftDown
        Ws.Cells(i, 1).Value = Ws.Cells(i - 1, 1).Value
        Ws.Cells(i, 3).FormulaR1C1 = "=SUMIF(R[-" & i - 1 & "]C[-2]:R[-" & i - (i - 1) & "]C[-2],""" & Cells(i, 1).Value & """,R[-" & i - 1 & "]C[0]:R[-" & i - (i - 1) & "]C[0])"
        Ws.Cells(i, 4).FormulaR1C1 = "=SUMIF(R[-" & i - 1 & …
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!

I need to create some vba code that will give me the first cell in the same column of any cell i select in a worksheet.  For example if i select cell A15, i need a variable that will result in cell A1.  If i select cell C555 i need it to give me cell C1.
A user is using and excel spreadsheet and adds the date to a spreadsheet but then wants to show the day by adding a formula of =day(A2) where A2 is the field with the date in. The user then right clicks on the box formats the cell from the Custom category and adds ddd under the type so the day of the week appears.

Up to now, the days of the week have been correct but now and new dates that are added show any random day.

The original spreadsheet had further calculations in it when we noted the discrepancy in the date and so we tested with the straight forward formula above to show that there was no error in the original calculations.

The user is an Office 365 user but we have tested with non-Office 365 users at different companies to rule out a data-centre and this occurs with all versions of Excel we tested with (2016, 2010, 2007 & 2003)
Column B contains 241 numbers that are not listed in Column A.
The request is to be able to find and list the 241 numbers separately in Column C.
NUM sheet and NUM_VER sheet need have column F
is possible to add 2 buttons to the blue area (header)

1- to move to the last row with data
2 and one to move to the top
Dear Experts:

I would like to set a 'dynamic' range in VBA. See Screenshot.

The trouble is I cannot use below code snippet for the setting of this range since there is lots of data below
LastRow = Cells(Rows.Count, "C").End(xlUp).Row

Set MyRange = Range("A71:C" & LastRow)

The current range method is also not working since that command would select A69:C84
The workaround I could very well live with is the current range command tweaked in some way so that only A71:C84 gets selected.

The whole thing should not be hard coded since I would like to have it dynamic.

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

Sincerley, Andreas
Hi experts!

I'm looking to find a way to copy one row (or just range A2:C2) from a source sheet into a destination sheet (cell A1) within the same workbook (I'll then call another sub that formats and exports a pdf and then clear the contents of the cells) and then copy and paste the next row into the same destination cell in the same destination workbook and do this until the next cell in column A is empty.

This is the last piece of a puzzle I've been working on and I'm desperate to get it finished so any help would be much appreciated!

I am after some help changing the format of my data, I have attached an example but I have many rows of data and on many different sheets but the same logic is needed for each data set.

Basically flipping the data to rows (access upload) rather than columns (membership and subs), columns A&B are always the same.

Any help to automate this would be really appreciated.

Any questions please ask.

Thanks so much as always

EE Pros,

I'm in need for some help with a macro.  

I need a macro that changes the color in the Cell from blank to white (vblack) when you double click on the cell or merged cells.  It needs to only be active for a specific range of cells.

Help would be appreciated.

Free Tool: Subnet Calculator
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.

Ever since i added the 3rd worksheet to my workbook, I'm getting a run time error 9, out of script every time i attempt to click on the Auto Liab Only button on Sheet1.  schedules-revised.xlsm

Any help would be greatly appreciated.
i have lists of words in excel column across 20k rows

COL A                                      COL B
apple, orange, pear 
beer, wine, rum         

i need it to do this

COL A                                    COL B
On the attached spreadsheet I need to pull the row "Total" for each sales Person, from the  " Final Amount" column "I"  to a new sheet.
In the new sheet I need the name to show up with all the middle fields empty( but still showing up)
I tried to do a vlookup but it does not look like the smart way to do it.
I have put in a result tab to show what I need the pulled data to look like.
A student is giving the test in 12 rounds and every round has 4 subjects. I want to make sheet where I can show student result of all rounds on choose subject. For example, if I choose English subject then the formula should show a result of every round which he was taken .
Sample is attached

I have attached a spreadsheet with some code and functions, but I want to know if I can achieve the following.

Spreadsheet has 7 tabs.  The tabs I am interested in is tabs 2 - 7

Each tab has one row of data.  Now if you go to tab 2 - Risks, the go to column J and in the dropbox click Action.  You will now see that the row has gone.

Go to the Action tab, and you will see that the row appears here, and that is correct.  I column B you will see an R.  As it was moved from the Risk tab, it was enetered as a R for Risk.  But as it now resideds in Action, I want the cell automatically update to an A.

This movement and changes will only aplly for rows that move between Tabs 2-4,  Risk, Action, Isse and Dependancy.

Please let me know if any more info is needed.
then ORD-M sheet and ORD_VER need have column J
How would I write a vba inputbox (Ok and Cancel) that does the following:
-dialog title of "Enter ECM"
-gives user a head start with a value of 5000000
-result needs to be 12 characters and begin with a "5".  Error: You entered an invalid value: strECM  'That's my InputBox variable name, please try again
-Cancel button or Red X in upper right corner, Exits Sub
-Blank input, and chars < 12, errors and says try again.

So, 500000012370 is a valid input,    400000012370 is not.  50000001237 is not....
Please advise and thanks.
then ORD sheet  and ORD_VER need have column I
then NUM-M  and NUM_VER  need have column G
[Webinar] Learn How Hackers Steal Your Credentials
[Webinar] Learn 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

Hi Guys I just wanna ask.I do have here codes for generating Request # but its randomize and I need a combination of alpha-numberic characters that will generate request # by sequence not randomize and combine with aleast 2 letters.Please see the attached file below.
I am attaching a file that has two Start entries (C4 and C31). This is just a sample set so this would be repeated for thousands of rows. I want to know how to do two things: how do I count the number of entries between each Start e.g. it should be 26 - but to do that for all the rows in the data set. Secondly how do I show the time elapsed (in hours) between each occurrence of start. (cell T7) - again for each occurrence  of this start...basically the idea is that a pump starts...does its thing. Stops and then starts again...
Need to have an query for 4 num exact to be show in  NUM sheet ans NUM_VER sheet (first to get done)

then show the 13 set brging location 1

In this file is very importante show

column F location 1 in yellow background (to be audit)

column L

column M

column N
The attached example file contains three identifier columns with the main identifier being a code in column C.
There are duplicate codes at random down column C - Rows 3 & 4,  114 & 115, 185 & 186, and 196 & 197, are examples shown in red.

The objective is to have the contents of the lower duplicate row added to the contents of the cell above and then the lower row to be deleted.
The columns affected are from D to KQ.

The actual files have about 60,000 rows.
I have two tabs in the attached file. I am trying to create a "Result sheet" based off my  "Data sheet". I need a standard subtotal for every "Sales Person" in these fields:

1) "Amount Collected"
2) "Amount Put Back"
3) "Amount tendered"
4) "Returns"
5) "Amount"
6) "Amount removed".

But the "Final Amount" field is a different calculation. It needs to be the last "Final Amount" that was greater than 0 for that "Sales Person", BUT if there were any "Amount Put Back" AFTER the date of the last "Final Amount", these need to be added to the "Final Amount". But note that is said AFTER. If these "Amount Put Back" were BEFORE the last "Final Amount" that was greater than 0, they do not need to be added. Individual explanations for each "Final Amount" is in the spreadsheet.
need query column I location 1

and show 13 set  coulmns L , M , N
in  sheet  ORD,  ORD_VER

Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.