Free SunSystems Tutorial: 2018

▼ 2018 (13)
- ▼ March (2)
  - How to make a Financial Statement using Microsoft ...
  - A simple way to upload budget using Ledger Import
- ► February (11)

► 2016 (7)
- ► September (3)
- ► February (4)

► 2014 (2)
- ► June (2)

► 2013 (5)
- ► October (1)
- ► May (1)
- ► April (3)

Total Line

Sub Range_Find_Method() 'Finds the last non-blank cell on a sheet/range. Dim lRow As Long Dim lCol As Long lRow = Cells.Find(What:="*", _ After:=Range("A1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row ' MsgBox "Last Row: " & lRow Range("A1:H" & lRow).Select Selection.Copy Range("P1").Select ActiveSheet.Paste End Sub

Comparative1

Sub BuildCompSh() Dim CTB_A As Worksheet Dim PTB_A As Worksheet Dim CPTB_A As Worksheet Dim C_LastActiveRow As Long Dim P_LastActiveRow As Long Dim CP_LastActiveRow As Long Dim rgFound As Range Dim FoundRow As Integer Dim c_Row As Integer Set LC = Worksheets("Launch") Set CTB_A = Worksheets("CurrentTB_A") Set CTB_B = Worksheets("CurrentTB_B") Set PTB_A = Worksheets("PriorTB_A") Set CPTB_A = Worksheets("ComparativeTB_A") P_LastActiveRow = PTB_A.Cells(PTB_A.Rows.Count, "B").End(xlUp).Row CPTB_A.Range("A4:K" & P_LastActiveRow).Value2 = PTB_A.Range("A4:K" & P_LastActiveRow).Value2 CP_LastActiveRow = CPTB_A.Cells(CPTB_A.Rows.Count, "A").End(xlUp).Row C_LastActiveRow = CTB_A.Cells(CTB_A.Rows.Count, "A").End(xlUp).Row For i = 0 To (C_LastActiveRow - 4) Set rgFound = CPTB_A.Range("A:A").Find(CTB_A.Range("A4").Offset(i, 0), LookAt:=xlWhole) If Not rgFound Is Nothing Then FoundRow = rgFound.Row CPTB_A.Range("L" & FoundRow).Value2 = CTB_A.Range("A4").Offset(i, 10) Else CP_LastActiveRow = CP_LastActiveRow + 1 c_Row = CTB_A.Range("A4").Offset(i, 10).Row CPTB_A.Range("A" & CP_LastActiveRow & ":J" & CP_LastActiveRow).Value2 = _ CTB_A.Range("A" & c_Row & ":J" & c_Row).Value2 CPTB_A.Range("L" & CP_LastActiveRow).Value2 = _ CTB_A.Range("K" & c_Row).Value2 End If Next End Sub

Comparative

Sub BuildCompSh() Dim CTB_A As Worksheet Dim PTB_A As Worksheet Dim CPTB_A As Worksheet Dim C_LastActiveRow As Long Dim P_LastActiveRow As Long Dim CP_LastActiveRow As Long Dim rgFound As Range Dim FoundRow As Integer Dim c_Row As Integer Set LC = Worksheets("Launch") Set CTB_A = Worksheets("CurrentTB_A") Set CTB_B = Worksheets("CurrentTB_B") Set PTB_A = Worksheets("PriorTB_A") Set CPTB_A = Worksheets("ComparativeTB_A") P_LastActiveRow = PTB_A.Cells(PTB_A.Rows.Count, "B").End(xlUp).Row CPTB_A.Range("A4:K" & P_LastActiveRow).Value2 = PTB_A.Range("A4:K" & P_LastActiveRow).Value2 CP_LastActiveRow = CPTB_A.Cells(CPTB_A.Rows.Count, "A").End(xlUp).Row C_LastActiveRow = CTB_A.Cells(CTB_A.Rows.Count, "A").End(xlUp).Row For i = 0 To (C_LastActiveRow - 4) Set rgFound = CPTB_A.Range("A:A").Find(CTB_A.Range("A4").Offset(i, 0), LookAt:=xlWhole) If Not rgFound Is Nothing Then FoundRow = rgFound.Row CPTB_A.Range("L" & FoundRow).Value2 = CTB_A.Range("A4").Offset(i, 10) CPTB_A.Range("N" & FoundRow).Value2 = CTB_A.Range("A4").Offset(i, 11) Else CP_LastActiveRow = CP_LastActiveRow + 1 c_Row = CTB_A.Range("A4").Offset(i, 10).Row CPTB_A.Range("A" & CP_LastActiveRow & ":J" & CP_LastActiveRow).Value2 = _ CTB_A.Range("A" & c_Row & ":J" & c_Row).Value2 CPTB_A.Range("L" & CP_LastActiveRow).Value2 = _ CTB_A.Range("K" & c_Row).Value2 CPTB_A.Range("N" & CP_LastActiveRow).Value2 = _ CTB_A.Range("L" & c_Row).Value2 End If Next End Sub

Connection String MVC

Server=(local)\\;Database=DBNAME;Trusted_Connection=True;MultipleActiveResultSets=true;User ID=sa;Password=PASSWORD;Integrated Security=False

Formula

CYB CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4) & "/001" CYC Result= {=(CY)} CRE CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4) & "/012" PYB CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-1 & "/001" PYC CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-1 & "/" & right(CurrentPeriod,3) PYE CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-1 & "/012" PYB2 CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-2 & "/001" PYC2 CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-2 & "/" & right(CurrentPeriod,3) PYE2 CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-2 & "/012" PYB3 CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-3 & "/001" PYC3 CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-3 & "/" & right(CurrentPeriod,3) PYE3 CurrentPeriod = {=(CY)} Result = left(CurrentPeriod,4)-3 & "/012" === CurrentPeriod = {=(CY)} NewDate = DateAdd("m",-12+1,CurrentPeriod) TheMonth = month(NewDate) Result = Year(NewDate) & "/" & string(3-len(TheMonth),"0") & TheMonth ==== CurrentPeriod = {=(CY)} NewDate = DateAdd("m",-24+1,CurrentPeriod) TheMonth = month(NewDate) Result = Year(NewDate) & "/" & string(3-len(TheMonth),"0") & TheMonth

SunPlusIndo 2016. Simple theme. Theme images by rajareddychadive. Powered by Blogger.