VBA: Drag down cells by calculated value

[noctash]

I realize this is probably a very basic question, but I'm struggling to find the answer online. Thank you in advance!

I am very new to VBA and trying to write a macro to speed up my workflow, having difficulty finding a code that will drag down a variable amount of rows.

Sheet 1 with lots of information about samples - I add new samples to this one with a form
Sheet 2 has a space to enter results with replicates for each sample.

I want my macro select all the data on Sheet 2, calculate the difference between the last cell in Sheet 1 Column A and Sheet 2 Column E, and drag down by double that amount to skip rows as I have in the images below.

My idea of how the code would work:

Sub Update()

'Call Value of Last Cell Sheet 1 Column A = "x"
???

'Call Value of Last Cell Sheet 2 Column E = "y"
???

'Calculate Number of cells to drag down "n" = (x - y)*2
???

'Select Cells in Sheet 2
Sheets("Sheet 2").Select
Range("E4", Range("E4").End(xlDown).End(xlToRight)).Select)
'Drag Down by "n"
???
 End Sub

 

[HaHoBe]

Hi noctash,

MrE_1223740_161520B_vba drag down cells_221204.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Row	First run				Row
2
3		Data					Result
4
5	3	ID				3	ID
6	4	1	Info11	Info12		4	1	Info11	Info12	Data to be filled	Data to be filled	Data to be filled
7	5	2	Info21	Info22		5				Data to be filled	Data to be filled	Data to be filled
8	6	3	Info31	Info32		6	2	Info21	Info22	Data to be filled	Data to be filled	Data to be filled
9						7				Data to be filled	Data to be filled	Data to be filled
10						8	3	Info31	Info32	Data to be filled	Data to be filled	Data to be filled
11						9				Data to be filled	Data to be filled	Data to be filled
12
13
14
15		Second run
16
17		Data					Result
18
19	3	ID				3	ID
20	4	1	Info11	Info12		4	1	Info11	Info12	Data to be filled	Data to be filled	Data to be filled
21	5	2	Info21	Info22		5				Data to be filled	Data to be filled	Data to be filled
22	6	3	Info31	Info32		6	2	Info21	Info22	Data to be filled	Data to be filled	Data to be filled
23	7	4	Info41	Info42		7				Data to be filled	Data to be filled	Data to be filled
24	8	5	Info51	Info52		8	3	Info31	Info32	Data to be filled	Data to be filled	Data to be filled
25						9				Data to be filled	Data to be filled	Data to be filled
26						10	4	Info41	Info42	Data to be filled	Data to be filled	Data to be filled
27						11				Data to be filled	Data to be filled	Data to be filled
28						12	5	Info51	Info52	Data to be filled	Data to be filled	Data to be filled
29						13				Data to be filled	Data to be filled	Data to be filled
Combined

Code in a standard module, please change the names of the worksheets to suit:

Public Sub MrE_1223740_161520B()
' https://www.mrexcel.com/board/threads/vba-drag-down-cells-by-calculated-value.1223740/
Dim lngCounter      As Long
Dim lngLastSh1      As Long
Dim lngLastSh2      As Long
Dim lngStartSh2     As Long
Dim lngStartSh1     As Long
Dim wsSh1           As Worksheet
Dim wsSh2           As Worksheet

'constant to decide whether to add datra or start at beginning, here set to add
Const cblnStartOver As Boolean = False

'constant to be inserted in target sheet
Const cstrInsert As String = "Data to be filled"

'get references to the sheets
Set wsSh1 = Worksheets("Data")
Set wsSh2 = Worksheets("Result")

'get the last filled row on each sheet respectively
lngLastSh1 = wsSh1.Range("A" & wsSh1.Rows.Count).End(xlUp).Row
lngLastSh2 = wsSh2.Range("E" & wsSh2.Rows.Count).End(xlUp).Row

'check if starting with all IDs
If cblnStartOver = True Then
  lngStartSh2 = 4
  lngStartSh1 = 4
  'clear the range in Target Sheet
  With wsSh2.Range("E4")
    .Resize(.CurrentRegion.Rows.Count - 1, .CurrentRegion.Columns.Count).ClearContents
  End With
Else
  'get the information about the starting rows for each sheet
  With wsSh2.Cells(lngLastSh2, "E")
    lngStartSh2 = IIf(.Value = "ID", 4, .Row + 2)
    If .Value = "ID" Then
      lngStartSh1 = 4
    Else
      lngStartSh1 = .Value + 4
    End If
  End With
End If

With wsSh1
  'loop thorugh the data and add data to target sheet
  For lngCounter = lngStartSh1 To lngLastSh1
     wsSh2.Cells(lngStartSh2, "E").Resize(1, 3).Value = wsSh1.Cells(lngCounter, "A").Resize(1, 3).Value
     wsSh2.Cells(lngStartSh2, "E").Offset(, 3).Resize(2, 3).Value = cstrInsert
     lngStartSh2 = lngStartSh2 + 2
  Next lngCounter
End With

Set wsSh2 = Nothing
Set wsSh1 = Nothing
End Sub

Ciao,
Holger