Category Archives: Excel help and guidance

Check if cell is a number

This quick piece of code will run down the current sheet and if the value in the cell is NOT a number or is BLANK it will give you a messagebox of what the value is. This is useful for when you want to determine if a particular cell has a number, and if not […]

Add every other cell on a row

Occasionally when you have a large number of cells in a row that you want to sum, ideally it would just be in a range. ie A1:A7500 for instance. This would sum up everything from Cell A1 to Cell A7500. Now what if the data contained daily takings where 1 column was Gross and the […]

VBA Assign a Hyperlink to a shape

Once you’ve drawn your shape and added text if applicable, or added an image that you want a hyperlink assigned to you can use this VBA example. It is particularly useful to add Hyperlinks this way if the hyperlink needs to change address throughout your VBA script running. For instance you may have a list […]

Excel copy a text file into a backup folder

Copy a text file into a backup folder, delete the original, and add todays date and time to the backup filename. Dim FileUser As String Dim FileuserDocFILE As String Dim output As String ‘Move the updated file Dim d As String, ext, x Dim srcPath As String, destPath As String, srcFile As String srcPath = […]

Send excel row to text file where large amount of columns used

This example sends a large amount of data from a row into a text file in the same path as the excel file is saved. With thousands of columns per row available, it would take a long time and be open to errors to assign each cell on a row, set the delimit character between […]

Recreate each worksheet as values instead of formulas

‘Recreate each worksheet as values instead of formulas and scroll to top / A1 For Each ws In Worksheets ws.Activate ws.Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues ws.Range(“a1”).Select Application.Goto Reference:=ws.Range(“a1”), Scroll:=True Next ws This VBA can be applied to any workbook where you want the formulas to be copied and pasted as values – ie as text in […]

Create new worksheet for each unique value in a list

This quick script will go down column A of a tab named ‘Data’ and for each unique value it finds, it will create a worksheet with that name and then for each occurrence of the value in the list it will populate the new sheet line by line with the data alongside the value in […]

Count Months in a Pivot List (Formula)

=COUNTIF(A:A,”Jan”)+COUNTIF(A:A,”Feb”)+COUNTIF(A:A,”Mar”)+COUNTIF(A:A,”Apr”)+COUNTIF(A:A,”May”)+COUNTIF(A:A,”Jun”)+COUNTIF(A:A,”Jul”)+COUNTIF(A:A,”Aug”)+COUNTIF(A:A,”FSep”)+COUNTIF(A:A,”Oct”)+COUNTIF(A:A,”Nov”)+COUNTIF(A:A,”Dec”) This formula counts all the occurrences of a month in the list when using multiple years and you need to know how many months it covers.

VBA border around a cell

Border all around a cell Range(“K5”).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .ColorIndex = 0 .TintAndShade = 0 […]

TabIndex

This series of blogs created by datanology are updated as and when new features, problems & solutions or general tips regarding a particular function in Excel is experienced in our projects. The aim is to provide a very short title but to cover every aspect that we can with it. Where code or formulas can […]

Remove email addresses from customer database on unsubscribe

We have recently completed several projects where our client has a large customer database, names and email addresses in one list, and in another separate list is a download of all the email addresses that have chosen to unsubscribe or requested no further contact via email. A company’s reputation is at risk if they continue […]

Force #N/A error in cell

This may seem illogical, and we even asked ourselves why anybody would want to do this, when the whole point of a clean and fully functional excel project is to avoid errors and ensure nothing can get in the way of an accurate result or outcome. However, we were asked to create a formula that […]

Excel Binary vs Excel Standard

There are certainly far more benefits in saving larger database files managed in Excel as the binary format suffix of .xlsb over any drawbacks. Of course if it was 100% beneficial in every situation then Microsoft would have introduced this as the standard saving format, but the reality is that for a wider use and […]

Vlookup in Excel

Vlookup (and its counterpart Hlookup which we deal with later) is an incredibly powerful function in Excel. It’s ability to automate results in a workbook can save lots of time, and provide 100% accuracy when used correctly. It looks quite daunting at first but once the formula logic is broken down into plain English and […]

the webdesigner group security shielf
the webdesigner group logo

Close Button

Web Page Design by

The Web Designer Group