Office_SpacedOut

 Outlook tips/tricks

Is each value in col a found in named range x
=IF(ISNUMBER(MATCH(B3,Districts_combined,0)),"",B3)

ics calendar ...minimal setup

You can put a notepad textfile on a website with ics extention

  with contents to schedule a meeting

BEGIN:VCALENDAR
BEGIN:VEVENT
DTEND;TZID="Central Standard Time":20151231T170000
SUMMARY:Fun_Party_Subject_Line
DTSTART;TZID="Central Standard Time":20151231T170000
DESCRIPTION:fun party \n ics calendar entry for 1700 on 21Dec2015 \n
 for all\, to enjoy
LOCATION:some nice
  place where we can play
SEQUENCE:0
BEGIN:VALARM
TRIGGER:-PT1H
DESCRIPTION:Event reminder
ACTION:DISPLAY
END:VALARM
END:VEVENT
END:VCALENDAR

notice the \n new like char

  and the \ escape char before the comma in the description

outlook will auto add this to your calendar

 

Excel visual basic macro fun

Underline Underscore _

 is _

 the _

 line Continuation _

 character

Subs vs Functions
Functions return values... so within the function, it sets its own val

Subs can be assigned to graphics/clipart/buttons/etc and called when mouse clicked

Var assignment tips

always use DIM
set is only needed when you are dealing with non primitive objects

Note: if you use set x = 5, you must first dim x
          otherwise just say x = 5 (without the set)

dont use "set"  unless referring to an object ref (not a primitive type).

It's a good idea to dim (dimension) ALL variables however:

simple/primitive data types ex: integer, long, boolean, string.
...do not have their own methods and properties.

Dim i as Integer
i = 5

Dim myWord as String
myWord = "Whatever I want"

ex: objects Range, a Worksheet, or a Workbook. These have their own methods and properties.

Dim myRange as Range
Set myRange = Sheet1.Range("A1")

If you try to use the last line without "Set", VB will throw an error.
 Now that you have an object declared you can access its properties and methods.

myString = myRange.Value

'This module opens each file listed on FILES sheet
'    --read cells into Array from 1st sheet
'    --save cell Array to csv worksheet (for later SQLLDR'ing into oracle)
Public sourceRange As Range, destRange As Range
Public fileListingStartingRow, FileListingEndingRow As Integer 'Vars shared between the subs/functions
Public maxRowsToReadFromFile, maxColsToReadFromFile As Integer 'Vars shared between the subs/functions
Public csvRowNum, fileCnt, sheet1graphiccount, sheet21graphiccount As Integer 'Vars shared between the subs/functions
Public FileName, FileAndPath, Sheet1Name, Sheet2Name, OrigPath As String
Public cellArraySheet1() As Variant

'*******************************************
'  Get the last non-blank row of the sheet(1) of the current workbook
'*******************************************

Function getLastRowFn()

Set rng = Worksheets(1).Range("A1:ZZ99")
roww = rng.Find(What:="*", _
 after:=rng.Cells(1), _
 Lookat:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByRows, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Row
  getLastRowFn = roww
End Function


'*******************************************
'  Get the last non-blank col of the sheet(1) of the current workbook
'*******************************************

Function getLastColFn()

Set rng = Worksheets(1).Range("A1:ZZ1")
 coll = rng.Find(What:="*", _
 after:=rng.Cells(1), _
 Lookat:=xlPart, _
 LookIn:=xlFormulas, _
 SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious, _
 MatchCase:=False).Column
 getLastColFn = coll
End Function

'*******************************************
'  This sub is linked to a test button on the scripts sheet
'*******************************************

Sub copyRange()

    MsgBox ("defining source range")
    'Set sourceRange = Sheets("CSV").Range("A1:B1")
    Set sourceRange = Sheets("CSV").Range( _
                Sheets("CSV").Cells(1, 1), _
                Sheets("CSV").Cells(1, getLastColFn))   '"A1:to last col/row"

    MsgBox ("getting source count")
    source_range_row_count = sourceRange.Rows.Count
    MsgBox ("csv row count is " & source_range_row_count)
    source_range_col_count = sourceRange.Rows.Count
    MsgBox ("csv col count is " & source_range_col_count)

    Set destRange = Sheets("CSV").Range( _
                Sheets("CSV").Cells(2, 1), _
               Sheets("CSV").Cells(2, 1))
       
    MsgBox ("copy the source to the dest")
    destRange.Value = sourceRange.Value
End Sub
'*******************************************
'  This sub is linked to a test button on the scripts sheet
'*******************************************

Sub copyRange()

    'MsgBox ("defining source range")
    'Set sourceRange = Sheets("CSV").Range("A1:B1")
    lastSrcCol = getLastColFn
    lastSrcRow = getLastRowFn
 
    Set sourceRange = Sheets("CSV").Range( _
                Sheets("CSV").Cells(1, 1), _
                Sheets("CSV").Cells(lastSrcRow, lastSrcCol))   '"A1:to last col/row"

    'MsgBox ("getting source count")
    'source_range_row_count = sourceRange.Rows.Count
    'MsgBox ("csv row count is " & source_range_row_count)
    'source_range_col_count = sourceRange.Rows.Count
    'MsgBox ("csv col count is " & source_range_col_count)
    destRowNum = 5
    Set destRange = Sheets("CSV").Range( _
                Sheets("CSV").Cells(destRowNum, 1), _
                Sheets("CSV").Cells(lastSrcRow, lastSrcCol))
     
    'MsgBox ("copy the source to the dest")
    destRange.Value = sourceRange.Value
    MsgBox ("copy the source to the dest complete")
End Sub

Many ways to get values

Sub TestIt()

 MsgBox ("A1 Text is:" & Sheets("TEST").Cells(1, 1).Text)

 MsgBox ("A1 value is:" & Sheets("TEST").Cells(1, 1).Value)

 MsgBox ("A1 is:" & Sheets("TEST").Cells(1, 1))

 MsgBox ("A1 range is:" & Sheets("TEST").Range("A1"))

End Sub

    

vLookup

VLOOKUP Parms:
  1) Test_Score: The value to lookup
  2) Scoring lookup table (buckets w/assoc letter grade)
  3) The col num for letter grade in the legend/lookup table
        to pull the value from from the associated score