mrkeck Sharing the Love

6Jan/140

Substring Search in Excel

SEARCH(needle, haystack) returns 1 if found and if not #VALUE


Example use:
A1 has "There is some text in here"
=IF(ISNUMBER(SEARCH("text",A1)),"Found", "Not Found")

It's a yes or no, not a location.

Filed under: Excel No Comments
19Aug/130

Excel VLOOKUP not working with number

Let's assume you have number in A1 that is not working in a VLOOKUP. Put this formula in B1 "=IF(ISNUMBER(A1*1),A1*1,A1)". Then point the VLOOKUP to B1 instead. That formula will work for numbers and anything else.

Filed under: Excel No Comments
26Apr/130

Excel Combine Multiple Columns into One

'Put all data from B1-Z1000 into column A
Dim maxRow As Integer
Dim maxCol As Integer
Dim cnt As Integer
maxRow = 999 'Controls how many rows
maxCol = 25 'Controls how many columns
cnt = 0
Range("A1").Select 'Where to start putting data
For j = 1 To maxCol
    For i = 0 To maxRow
       If ActiveCell.Offset(i, j).Value <> "" Then
            ActiveCell.Offset(cnt, 0).Value = ActiveCell.Offset(i, j).Value
            'Remove original data
            ActiveCell.Offset(i, j).Value = ""
            'Get ready for next one
            cnt = cnt + 1
       End If
    Next i
Next j
Filed under: Excel No Comments
5Oct/120

Handy Excel VBA settings

'Turn Off AutoCalc -- speeds up copying and pasting until you are finished
Application.Calculation = xlCalculationManual
 
'Stops the screen from flashing while it is working
Application.ScreenUpdating = False
 
'Return excel to normal flashing while doing stuff
Application.ScreenUpdating = True
 
'Turn On AutoCalc
Application.Calculation = xlCalculationAutomatic
Filed under: Excel No Comments
5Oct/120

Excel Wrap Text align left

I was having an issue with Wrap Text. Every time it would center to the middle of the text. I wanted it to start with the beginning but not run into other cells.

To do it set the horizontal alignment to "Left" or "General" and the vertical alignment to "Top" and, obviously, turn on Wrap Text.

You will need to adjust the row height as well to keep the spacing regular.

Filed under: Excel No Comments
23Mar/110

Excel Loop through Cells

Loops come up in every programming language. This is how I like to loop through Excel Cells

1
2
3
4
5
6
7
8
9
10
11
12
13
Range("B4").Select
For i = 0 To 100
'Offset(row, column)
If Mid(ActiveCell.Offset(i, 0).Value, 1, 1) = "N" Then 'If the first letter is N
  If Worksheets("Sheet").Range("D3").Value = "thing" Then 'value on different Sheet
    ActiveCell.Offset(i, 0).EntireRow.Hidden = False 'Show this row
    ActiveCell.Offset(i, 1).Value = "awesome" '1 column right
    ActiveCell.Offset(i, -1).Value = "cool" '1 column left
  Else
    ActiveCell.Offset(i, 0).EntireRow.Hidden = True 'Hide this row
  End If
 End If
Next i
Filed under: Excel No Comments
23Mar/110

Excel Conditional Hide Column

1
2
3
4
5
6
7
8
9
Application.ScreenUpdating = False 'Dont show hiding of columns

    Columns("W:W").Select
    If Range("W9").Value = "" Then
        Selection.EntireColumn.Hidden = True
    Else
        Selection.EntireColumn.Hidden = False
    End If
Application.ScreenUpdating = True
Filed under: Excel No Comments
23Mar/110

Excel Lock Down

This has a lot of nuggets in it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sub lockDown()
'Lock each sheet and the entire Workbook

'Stops the screen from flashing while it is working
Application.ScreenUpdating = False
 
'Go through each sheet and mark them as protected - This only works if all the cells are marked as Locked and Hidden under the protection tab under format cells
    For Each Sheet In Sheets
        Sheet.Protect ("password")
    Next Sheet
 
'Hide the Sheet1
Sheets("Sheet1").Visible = False
 
'Display Sheet2
Sheets("Sheet2").Select
 
'Lock down the workbook so people can't unhide - there is an optional window protection, it didn't go well
ActiveWorkbook.Protect Password:="password", Structure:=True
 
'Return excel to normal flashing while doing stuff
Application.ScreenUpdating = True
 
'Prompt to Save As
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="xls Files (*.xls), *.xls")
 
'If they don't cancel, save as filename
If fileSaveName <> "False" Then
    ActiveWorkbook.SaveAs fileSaveName
End If
 
End Sub
Filed under: Excel No Comments