Skip to content

Instantly share code, notes, and snippets.

@jollychang
Created May 23, 2011 14:49
Show Gist options
  • Save jollychang/986817 to your computer and use it in GitHub Desktop.
Save jollychang/986817 to your computer and use it in GitHub Desktop.
output_format
Public BlockMatchRow(100, 100) As Integer
Public WhoMax(100, 100) As Integer
Public cell_index(2) As Integer
Sub Run()
sheets_number = 3
tv_number = 5 'counter
start_sheet = 2
SetArray sheets_number, start_sheet, tv_number
'get_range_index_start_cells 2, 1
'get_range_index_start_cells 2, 2
'get_range_index_start_cells 2, 3
'get_range_index_start_cells 2, 4
get_range_index_start_cells 2, 5
get_range_index_start_cells 3, 5
get_range_index_start_cells 4, 5
'MsgBox WhoMax(1, 1)
'MsgBox WhoMax(1, 4)
'Copy_paste_selection copy_sheet_index, copy_start_row, copy_start_col, copy_end_row, copy_end_col, paste_sheet_index, paste_start_row, paste_start_col
'tv = 2
'st = 1
'sheet_start
'Copy_paste_selection st + sheet_start, WhoMax(st, tv + 1) - WhoMax(st, tv), 1, 1 + WhoMax(1, 1), 6 * -6, paste_sheet_index, paste_start_row, paste_start_col
End Sub
Function SetArray(sheets_number, sheet_start_index, tv_number)
counter = Application.WorksheetFunction.CountIf(Sheets(1).[E:E], "=sch-Date")
For c = sheet_start_index To sheet_start_index + sheets_number
Sheets(sheet_start_index).Select
Cells.Select
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim varArray() As Integer
ReDim Preserve varArray(sheet_start_index + sheets_number)
j = 2
BlockMatchRow(c, 1) = 0
For i = 1 To lLastRow
If Cells(i, 2) = "Proposal" Then BlockMatchRow(c, j) = i - 2: j = j + 1: 'MsgBox BlockMatchRow(c, j)
Next i
For a = 2 To counter
WhoMax(c, a) = BlockMatchRow(c, a) - BlockMatchRow(c, a - 1)
'MsgBox WhoMax(c, a)
Next a
varArray(c) = WhoMax(c, tv_number)
Next c
SetArray = Application.WorksheetFunction.Max(varArray())
End Function
Function Copy_paste_selection(copy_sheet_index, copy_start_row, copy_start_col, copy_end_row, copy_end_col, paste_sheet_index, paste_start_row, paste_start_col)
Sheets(copy_sheet_index).Select
Worksheets(sheet_index).Range(Cells(copy_start_row, copy_start_col), Cells(copy_end_row, copy_end_col)).Select
Selection.Copy
Sheets(paste_sheet_index).Select
Worksheets(Sheets.Count).Cells(paste_start_col, paste_start_row).Select
ActiveSheet.Paste
End Function
Function get_range_index_start_cells(sheet_index, tv_number)
s_col = 0
s_row = 0
'If range_number = 1 Then s_col = 0: s_row = 0
If tv_number > 1 Then s_row = BlockMatchRow(sheet_index, tv_number)
cell_index(1) = s_row
cell_index(2) = s_col
'MsgBox cell_index(1)
get_range_index_start_cells = cell_index()
End Function
Function get_range_index_end_cells(sheet_index, range_number)
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment