MS Excel VBA Data Analytics-101 [2 hrs] Instructor Led, In-Person Training New York - VBA code for loops play

MS Excel VBA Data Analytics-101 [2 hrs] Instructor Led, In-Person Training New York

MS Excel VBA Data Analytics-101 [2 hrs] Instructor Led, In-Person Training

Details

# About This Meetup:
Have you ever felt like you are limited to do your calculation in excel cells? Do you want to harness the power of Excel Visual Basic Applications(VBA)? If this sounds like you, this course is for you. This meetup is for Excel beginners and non-programmers and is all about Excel VBA.

# Key Takeaways:
* Referencing
* Shortcuts
* Dragging.
* If command
* Locking
* Sum-Product
* Vlookup
* Hlookup
* Index Match
* Offset
* Find Dependable
* Complex example of mixing all commands
* Matrix multiplication.
* Array Formulas
* Handling big files.

Pre-requisites:
Please bring your own laptop.

About the Speaker:
Shivgan Joshi
Lead Instructor
# 929 256 5046

** Payment Policy: We only accept payment on site and before the class. We accept payment through cash, Venmo & Paypal(+5). **

If you have already attended session 101, take a look at what we offer in session 102.
* Correlation,
* Regression
* Linear modeling, Goal seek,
* Optimization,
* Internal Rate of Return,
* Linest command,
* Graphing,
* Conditional formatting,
* Pivot tables
* Monte Carlo Intro,
* Min, Max, Bin
* Histograms
* Rank,
* Spearman correlation,
* Frequency,
* Error Handling,
* CountIFs,
* ISSomething,
* Cal modes
* Data Tables: Sensitivity, 2 way, and 3-way data table,
* Vlookups with data tables,
* Monte Carlo using Data tables.

'Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
        Range("G" & lastrow).Value = Range("A" & i).Value & "/" & _
                                     Range("B" & j).Value & "/" & _
                                     Range("C" & k).Value & "/" & _
                                     Range("D" & l).Value
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
    Next: Next

    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub



Sub Sample2()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4: For j = 1 To 4
    For k = 1 To 8: For l = 1 To 12
   
    Cells(i, 20) = i
     Cells(j, 21) = j
      Cells(k, 22) = k
      Cells(l, 23) = l
      lastrow = lastrow + 1
      CountComb = CountComb + 1
   
      Cells(lastrow, 24) = lastrow
      Cells(CountComb, 25) = CountComb
   
    Next: Next
    Next: Next

    Application.ScreenUpdating = True
End Sub

Sub Sample3()
    Dim i As Long, j As Long, k As Long, l As Long
    Dim CountComb As Long, lastrow As Long

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6

    For i = 1 To 4
    For j = 1 To 4
    For k = 1 To 8
    For l = 1 To 12
   
    Cells(i, 20) = i
     Cells(j, 21) = j
      Cells(k, 22) = k
      Cells(l, 23) = l
      lastrow = lastrow + 1
      CountComb = CountComb + 1
   
      Cells(lastrow, 24) = lastrow
      Cells(CountComb, 25) = CountComb
   
    Next l
    Next k
    Next j
    Next i

    Application.ScreenUpdating = True
End Sub
Sub playarray()


Dim myThirdColumn As Variant

myThirdColumn = Application.Index(myArray, , 3)



End Sub

' https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/

Sub Test()

    Dim varArray()          As Variant
    Dim varTemp()           As Variant
Dim myRng As Range

'Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)

Set myRng = Worksheets("SheetA").Range("A1:E10")
     varArray = myRng.Value
   varTemp = Application.Index(varArray, , 2)
 '  varTemp = Application.Index(varArray, Array(2, 3), 0)
  '  varTemp = Application.Index(varArray, , Application.Transpose(Array(2)))
 
MsgBox UBound(varTemp) - LBound(varTemp) + 1
    'MsgBox varArray(1, 1)

End Sub


Sub Test2()

    Dim varArray()          As Variant
    Dim varTemp()           As Variant
Dim myRng As Range

'Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)

Set myRng = Worksheets("SheetA").Range("A1:Z10")
     varArray = myRng.Value
   varTemp = Application.Index(varArray, 3)
    varTemp2 = Application.Index(varArray, , 3)
 '  varTemp = Application.Index(varArray, Array(2, 3), 0)
  '  varTemp = Application.Index(varArray, , Application.Transpose(Array(2)))
 
'MsgBox UBound(varTemp) - LBound(varTemp) + 1
'MsgBox varArray(1, 1)
'MsgBox UBound(varTemp2) - LBound(varTemp2) + 1
MsgBox varTemp2(10, 1)
' VBA Array starts at 1



End Sub



''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Test3()

    Dim varArray()          As Variant
    Dim varTemp()           As Variant
Dim myRng As Range

'Application.Index([A1:E10], , 2) = Application.Index(varArray, , 2)

Set myRng = Worksheets("SheetA").Range("A1:Z10")
     varArray = myRng.Value
   varTemp = Application.Index(varArray, Array(1, 2))
 
    'first two row elements
    'varTemp2 = Application.Index(varArray, , 3)
 '  varTemp = Application.Index(varArray, Array(2, 3), 0)
  '  varTemp = Application.Index(varArray, , Application.Transpose(Array(2)))
 
 
 
'  MsgBox Array(1, 2)(0)
 MsgBox varTemp(1)
 ' the first element actually using array command
 ' the above var temp starts with 1 and not with 0
'MsgBox UBound(varTemp) - LBound(varTemp) + 1
'MsgBox varArray(1, 1)
'MsgBox UBound(varTemp2) - LBound(varTemp2) + 1
'MsgBox varTemp2(10, 1)
' VBA Array starts at 1



End Sub