[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]....[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like to shorten the following vba codes. Any good suggestion?
Thanks.
ActiveCell.Offset(42, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-72]C,'01_Logbook'!R9C17:R1009C17,R75C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-73]C,'01_Logbook'!R9C17:R1009C17,R76C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-74]C,'01_Logbook'!R9C17:R1009C17,R77C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-75]C,'01_Logbook'!R9C17:R1009C17,R78C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-76]C,'01_Logbook'!R9C17:R1009C17,R79C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-77]C,'01_Logbook'!R9C17:R1009C17,R80C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-78]C,'01_Logbook'!R9C17:R1009C17,R81C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-79]C,'01_Logbook'!R9C17:R1009C17,R82C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-80]C,'01_Logbook'!R9C17:R1009C17,R83C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-81]C,'01_Logbook'!R9C17:R1009C17,R84C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-82]C,'01_Logbook'!R9C17:R1009C17,R85C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-83]C,'01_Logbook'!R9C17:R1009C17,R86C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-84]C,'01_Logbook'!R9C17:R1009C17,R87C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-85]C,'01_Logbook'!R9C17:R1009C17,R88C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-86]C,'01_Logbook'!R9C17:R1009C17,R89C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]....[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would like to shorten the following vba codes. Any good suggestion?
Thanks.
ActiveCell.Offset(42, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-72]C,'01_Logbook'!R9C17:R1009C17,R75C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-73]C,'01_Logbook'!R9C17:R1009C17,R76C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-74]C,'01_Logbook'!R9C17:R1009C17,R77C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-75]C,'01_Logbook'!R9C17:R1009C17,R78C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-76]C,'01_Logbook'!R9C17:R1009C17,R79C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-77]C,'01_Logbook'!R9C17:R1009C17,R80C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-78]C,'01_Logbook'!R9C17:R1009C17,R81C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-79]C,'01_Logbook'!R9C17:R1009C17,R82C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-80]C,'01_Logbook'!R9C17:R1009C17,R83C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-81]C,'01_Logbook'!R9C17:R1009C17,R84C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-82]C,'01_Logbook'!R9C17:R1009C17,R85C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-83]C,'01_Logbook'!R9C17:R1009C17,R86C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-84]C,'01_Logbook'!R9C17:R1009C17,R87C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-85]C,'01_Logbook'!R9C17:R1009C17,R88C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS('01_Logbook'!R9C2:R1009C2,R[-86]C,'01_Logbook'!R9C17:R1009C17,R89C1)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:AE1"), Type:= _
xlFillDefault