Blogs

Nick's picture

Nick's Day By Day Excel Experts Blog

Nick's picture

Corona Virus - COVID 19 Model - UK

If you have something to say, please join our Whatsapp chat!
Whatsapp Chat

3rd April

Please find a model and analysis of the current covid 19 pandemic.

The model will evolve over time as we obtain more data, and will be continually revised to match the data. Predictions are likely to change quite dramatically as the data comes in, and also as the government policies change.

multiple results changing a single cell

Story so far. In a work book have several dozen calculations based on a single cell lets call it C5

In a second workbook I have 5 columns of data with multiple rows derived from the value in C5

Column 1 in the second book has the value C5, How can I utilize the one workbook to use diffent values for subsequent columns in the second workbook

Workbook #1

C5 = 300

b10 = c5 *100
b11 = c5 /3.1415926
b12 = etc

Workbook #2

A1 A2 A3 A4

Almir's picture

Export all modules to another workbook

'Use this code to export all modules from Personal.xlsb file to another workbook. Prior to running macro, open target workbook and change its name in the code accordingly.
Sub ExportImportModule()
For Each Module In Workbooks("Personal.xlsb").VBProject.VBComponents
If Module.name <> "ExportImportModule" Then
Module.Export ("c:\temp\" & Module.name & ".bas")
Workbooks("TargetWorkbook.xlsm").VBProject.VBComponents.Import ("c:\temp\" & Module.name & ".bas")
End If
Next Module
MsgBox "All objects (modules, forms and classes) were copied!"

Required Help - Excel VBA

I require your help in Auto-filling data in specific cells of various sheets using Excel VBA ;for the purpose of computing Balance Score Card for my Team.

I would be really thankful if you can assist me with the same. This will save a lot of my time.

• Data from Master Sheet must auto-populate in remaining sheets, with the Individual Agents names.
• I have coloured the cells for 1st Agent – Akhlaq Khan. Data from Yellow cells (from Master sheet) must auto-populate in Yellow cells (in sheet name ‘Akhlaq’). Similarly, for Pink and Orange cells.
• Similarly, for remaining Agents.

Nick's picture

XLA Routines: EE_TableFreeHeading

Finds the next available cell to put a heading on a data table
Function EE_TableFreeHeading(sht As Worksheet) As Range
'http://excelexperts.com/xla-routines-eeTableFreeHeading    for updates on this function
' finds the next free cell to the right of a table
    
    Set EE_TableFreeHeading = sht.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
 
End Function
Nick's picture

XLA Routines: EE_ExtractRow

Extracts a row of data and transposes it - useful for analysing records with many columns
Sub EE_ExtractRow(Optional SourceSht As Worksheet, Optional TargetSht As String, Optional RowToExtract As Long, Optional wb As Workbook, Optional blnTranspose As Boolean = True)
' takes the selected cell row as default
' copies and transposes onto a new sheet
'http://excelexperts.com/xla-routines-eeExtractRow    for updates on this sub routine

    If SourceSht Is Nothing Then
        Set SourceSht = ActiveSheet
    End If
 
    If wb Is Nothing Then
        Set wb = ActiveWorkbook
Nick's picture

XLA Routines: EE_ReplaceErrors

Replaces the errors on a sheet
Sub EE_ReplaceErrors(Optional rng As Range, Optional ReplaceWith As String)
'http://excelexperts.com/xla-routines-eeReplaceErrors    for updates on this sub routine
' takes a range and replace the errors in the range

    Set rng = EE_TableDefault(rng)
    On Error Resume Next
    rng.SpecialCells(xlCellTypeFormulas, 16).value = ReplaceWith
    rng.SpecialCells(xlCellTypeConstants, 16).value = ReplaceWith
    Err.Clear: On Error GoTo 0: On Error GoTo -1
 
End Sub
Nick's picture

XLA Routines: EE_AddTableCalculatedColumn

Advanced function for adding a calculated col to a table
Sub EE_AddTableCalculatedColumn(Header As String, Formula As String)
'http://excelexperts.com/xla-routines-eeAddTableCalculatedColumn    for updates on this sub routine
' adds a calculated column to a table on the activesheet
Dim HeaderCol As Long
Dim myData  As Range
 
    If Cells(1).CurrentRegion.Rows.Count = 1 Then Exit Sub ' no data
    Set myData = EE_TableGetColumnData(Header)
    myData.ClearContents
    HeaderCol = EE_TableHeadingCol(Header)
    Cells(2, HeaderCol) = Formula
    myData.value = myData.value
Nick's picture

XLA Routines: EE_FormatCols

Useful routine for formatting columns from an imported file
Sub EE_FormatCols(rngSource As Range, Optional rngTarget As Range)
'http://excelexperts.com/xla-routines-eeFormatCols    for updates on this sub routine
' takes a source range containing the heading and format in 2 cols
' looks in the target range and formats the target range
Dim rngHd As Range
Dim rngFound As Range
 
    Call EE_Start
 
    Set rngTarget = EE_TableDefault(rngTarget).Rows(1)
    For Each rngHd In rngSource.Rows
        Set rngFound = rngTarget.Find(rngHd.Cells(1).value, , xlValues, xlWhole)
 
Nick's picture

XLA Routines: EE_ReverseSignInRange

Sub EE_ReverseSignInRange(rng As Range)
'http://excelexperts.com/xla-routines-eeReverseSignInRange    for updates on this sub routine
' takes a range as input and reverses the sign of numbers in the range
' turns positive numbers negative and negative numbers positive
Dim theCell
 
    Set rng = Intersect(rng.Parent.UsedRange, rng)
    ' rng.select
    For Each theCell In rng
        If Application.IsNumber(theCell.value) Then
            theCell.value = theCell.value * -1
        End If
    Next
 
End Sub
Nick's picture

XLA Routines: EE_ListSheetNames

Creates an index sheet with hyperlinks
Sub EE_ListSheetNames(Optional NewShtName As String)
'http://excelexperts.com/xla-routines-eeListSheetNames    for updates on this sub routine
' lists the names of the sheets on a new sheet
Dim sht As Worksheet
 
    If NewShtName = "" Then
        NewShtName = "Index"
    End If
 
    Call EE_ReplaceSheet(NewShtName)
    i = 1
 
    On Error Resume Next
    For Each sht In ActiveWorkbook.Sheets
        Cells(1).Offset(i) = sht.Name
 
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(1).Offset(i), Address:="", Sub
Nick's picture

XLA Routines: EE_PivotTurnOffNonBlank

Sub EE_PivotTurnOffNonBlank(pvtField)
'http://excelexperts.com/xla-routines-eePivotTurnOffNonBlank    for updates on this sub routine
' turns off non-blank items from pivot table
' assumes 1 pivot on page, no error checking

    Dim pvtItem
 
    pvtField.CurrentPage = "(All)"
    pvtField.EnableMultiplePageItems = True
    For Each pvtItem In pvtField.PivotItems
        If pvtItem.Name = "(blank)" Then
                pvtItem.Visible = True
            Else
                pvtItem.Visible = False
        End If
    Next
 
End Sub
Nick's picture

XLA Routines: EE_PositionInRange

Function EE_PositionInRange(Header As String, rng As Range) As Long
'http://excelexperts.com/xla-routines-eePositionInRange    for updates on this function
' Takes a heading name, range
' returns the position in the range as long
' returns 0 if not found
    On Error Resume Next
    EE_PositionInRange = Application.WorksheetFunction.Match(Header, rng, 0)
    If Err.Number <> 0 Then
        EE_PositionInRange = 0
    End If
 
End Function
Nick's picture

XLA Routines: EE_TableDefault

Creates a range object that represents a table of data - used to pass to other subs that require data
Function EE_TableDefault(Optional rngTable As Range) As Range
'http://excelexperts.com/xla-routines-eeEE_TableDefault    for updates on this function
' returns the currentregion around the first cell - a good guess for a table of data's range
    
    Set EE_TableDefault = rngTable
    If rngTable Is Nothing Then
        Set EE_TableDefault = ActiveSheet.Cells(1).CurrentRegion
    End If
 
End Function

Conditional formating based on the sum of cells

Hi, I am trying to identify cells sum of which equals to cerain value/amount. Is there a way of doing this at all? Thank you.

Need multifunctional formula or If condition in same cell repeated

I want to increment by one unit going left 18 spaces, and at the same time increase by 18 in column 18 columns total. Possible?

Help Adding the +/- button to the first item in a Group

I am trying to create a simple drop down or expandable field that allows me to simply click a +/- in the top cell of a group to expand that group so I can enter data...then close that group and open a new group. Very similar to just creating the group with the plus and minus on the left of the worksheet....but I want a button in the top cell of each group. Is that possible?

Thanks so much.

Charles

Getting MODE if certain criteria is met

In my data I have a list of sales (amounts) by cities. How can I get the mode for the sales only where the city = chicago? I have sorted the list by city and written the mode formula based on my sort to get only Chicago sales (rows 10:20). This works as long as the list stays sorted because the mode range is specific to to the sort results.

Thanks

Looking for differences in similar data sets

Hi There.

I am analysing a sales order book on a week to week basis. The Sales order reference remains the same but order values can change daily based on shipped/cancelled and amended orders. Can anybody suggest the best way to reconcile these differences in excel please?

Vishesh's picture

Export spreadsheet data to Access

Following macro can be used in Excel VBA to export Excel data into Access.

Sub TestMacro()

Call ExcelToAccessTransferSpreadsheet("G:\ExcelExperts\ExcelAccessTest.mdb", "DBTestTbl", "G:\ExcelExperts\Test.xlsm", "Sheet1", "A1:C8")

End Sub

Sub ExcelToAccessTransferSpreadsheet(strDBPath As String, strDBTableName As String, strExcelFilePath As String, strSheet As String, strRange As String, Optional blnClearTableBfrUpload As Boolean = True, Optional blnDropTableBfrUpload As Boolean = False)

'Should have access on the system
'Creates a new table in Access if not found

Mail Merge Help

Dear Sir,
I have an excel sheet with email ids of people and also the email ids of their reporting managers.
Now I have to create multiple letters, which I am contemplating to do with Mail merge and create the messages, but when I send out the output I want the mails to be sent with CC to the reporting manager. But in the Word the email is only sent to one id and no field to capture the email id for CC.
I understand it can be achieved with macro.
I tried to record and do but then failed.
Some one could help me on this would really be grateful.

excel help

We have a friendly league where everyone drafts 9 teams and picks one. The person with the most wins at the end of the season wins. I have made a manual entry excel sheet to enter wins from each college football team and nfl team. I was wondering if there is a way for the win totals to update automatically. Thanks
see attachment

Truncating text files to extract only email addresses

Hello does anyone know a formula to extract just the email addresses from files with records as follows:

aaasilv@terra.com.br;85.72.201.151;Athens;Attiki;Greece;20/07/2015;Nao;Sim;;;;1430833559
aabmeneses@terra.com.br;187.90.44.115;Salvador;Bahia;Brazil;20/07/2015;Nao;Sim;;;;3143249011

I have a total of 25,000 records for this extraction so a truncation solution would be appreciated!

The text required for each of the records above is:
aaasilv@terra.com.br
aabmeneses@terra.com.br

Thanks

Excel cannot open .xls file

Hi
Got a new customers pc here. she had a virus last week (one of the ucash ones) . she then ""spent all day trying to fix it following instructions on various forums""
She has no idea what she actually did. the virus isnt there anymore, but now nearly nothing works on the pc. sys restore doesnt work and every single document is giving me the error ""the open office xml file xxxx.docx cannot be opened because there are problems with its contents"" details: ""the file is corrupt and cannot be opened"". this also happens with xls, xlsx, docx, doc, csv

User friendly differential equation solver

This is kind of hard to explain.

I am creating a numerical solver that gets input from students in the form of an equation. An example being x^2 + y^2. I'd like to take this input and somehow put it into VBA as is.

This equation is going to be in a for each loop, where the values of x and y are changing with each iteration.

For i = 1 To n
m = x^2 + y^2 'Equation input from students
y1 = y + m * dx
y = y1
x = x + dx
Next i

VBA Macro to send Email to added recipients

Hi,

I have a macro file, where as per my selection (Customer Name Selection), I;m able to send email to the specified customer. Like - Once I select Xerox Co. from the filter and click on Send E-mail button, all the fields of Xerox Co gets copied to new workbook and I'm able to send email to the recipient mentioned in the VBA code.

Now what I want is - instead of having the email id mentioned in the vba codes. I'll like to have a column of email id of all the customer in column B for each customer name (abc@xerox.com, xyz@xerox.com).

Thanks for your help in advance

Regards,

How to filter multi column data and then appear filter data in seperate sheet

I have excel sheet that have more than 100 column. I want to make easy interface for user, my requirement is below.

detail of sheet are like

1- Column A to H are fixed and I want to filter the remaining column like I to Z.

2- When I filter the column I want the result that it will show only column that I filter and starting column A to H.

3- user interface is like drop down search of column I to Z.

Need macro to remove item and reset back to zero

I need help creating a macro that will remove a stock item from one tab and reset to zero so the macro can be run multiple times. The crux I am facing is being able to use this as a counter but in a negative sense. On the 'PRODUCTION'tab, I need to have the capability of removing stock item "TANK SIDE" from 'DS TANK' tab when a serial number is entered on the 'PRODUCTION' tab. The 'DS TANK' and 'PS TANK' tabs our inventory tabs and need the ability to add and subtract parts as they are produced and taken for assembly. I have attached a file for your perusal.

Adding value (5,2,1 year) to a cell value based on another cell value (DOB)

Hello All,
I have multiple excell worksheets with list of columns. Column C is Birth Year,column D is date of Approved, I want in Column C to calculate based on this criteria:

If Column C is <35 years add 5 years to Column D
If Column C is 35-45 years add 2 years to Column D
If Column C is >45 years add 1 years to Column D.
Please See my attached file!!
I used IF funciton but the outcome is not what i want
My formula:
=IF(TODAY()-C2>45,DATE(YEAR(K5)+1,MONTH(D2)+0,DAY(D2)+0),IF(TODAY()-C2<35,DATE(YEAR(D2)+5,MONTH(D2)+0,DAY(D2)+0))).

Syndicate content