yannezhang
05-09-2017, 04:20 PM
I need help on VBA code processing multiple blocks of data, reference another workbook, use relative and fixed references.
I want to autofill a 4x4 block multiple times, the autofill formula references to another workbook.
So it is like this:
Workbook A references to Workbook B, and calculates a new set of results
References in Workbook B located from H5 to K8 in a 4x4 block
Workbook A follows following rules for calculation:
1st block
H5 value = D5 + [Workbook B]Sheet1'!H9
I5 value = E5 + [Workbook B]Sheet1'!I9
J5 value = F5 + [Workbook B]Sheet1'!J9
K5 value = G5 + [Workbook B]Sheet1'!K9
... ...
H8 value = D8 + [Workbook B]Sheet1'!H12
I8 value = E8 + [Workbook B]Sheet1'!I12
J8 value = F8 + [Workbook B]Sheet1'!J12
K8 value = G8 + [Workbook B]Sheet1'!K12
2nd block
H9 value = D9 + [Workbook B]Sheet1'!H9
I9 value = E9 + [Workbook B]Sheet1'!I9
J9 value = F9 + [Workbook B]Sheet1'!J9
K9 value = G9 + [Workbook B]Sheet1'!K9
... ...
H12 value = D12 + [Workbook B]Sheet1'!H12
I12 value = E12 + [Workbook B]Sheet1'!I12
J12 value = F12 + [Workbook B]Sheet1'!J12
K12 value = G12 + [Workbook B]Sheet1'!K12
And so on... ...
I tried multiple approaches, non of them cover all that are required. Below is close, but Cells in ActiveCell.Formula causes it losing relative reference.
CorrelationFile = "Correlation.xlsx" 'Workbook B
CorrelationString = "'" & Folder_Path2 & "\[" & CorrelationFile & "]Sheet1'!H9"
j = 0
Do
Range("H" & ((j * 4) + 5)).Select
ActiveCell.Formula = "=" & Cells(((j * 4) + 5), 4) & "+" & CorrelationString 'Cell reference H5
Selection.AutoFill Destination:=Range("H" & ((j * 4) + 5) & ":K" & ((j * 4) + 5)), Type:=xlFillDefault
Range("H" & ((j * 4) + 5) & ":K" & ((j * 4) + 5)).Select
Selection.AutoFill Destination:=Range("H" & ((j * 4) + 5) & ":K" & ((j * 4) + 8)), Type:=xlFillDefault
j = j + 1
Loop While Range("D" & ((j * 4) + 5)) <> ""
I want to autofill a 4x4 block multiple times, the autofill formula references to another workbook.
So it is like this:
Workbook A references to Workbook B, and calculates a new set of results
References in Workbook B located from H5 to K8 in a 4x4 block
Workbook A follows following rules for calculation:
1st block
H5 value = D5 + [Workbook B]Sheet1'!H9
I5 value = E5 + [Workbook B]Sheet1'!I9
J5 value = F5 + [Workbook B]Sheet1'!J9
K5 value = G5 + [Workbook B]Sheet1'!K9
... ...
H8 value = D8 + [Workbook B]Sheet1'!H12
I8 value = E8 + [Workbook B]Sheet1'!I12
J8 value = F8 + [Workbook B]Sheet1'!J12
K8 value = G8 + [Workbook B]Sheet1'!K12
2nd block
H9 value = D9 + [Workbook B]Sheet1'!H9
I9 value = E9 + [Workbook B]Sheet1'!I9
J9 value = F9 + [Workbook B]Sheet1'!J9
K9 value = G9 + [Workbook B]Sheet1'!K9
... ...
H12 value = D12 + [Workbook B]Sheet1'!H12
I12 value = E12 + [Workbook B]Sheet1'!I12
J12 value = F12 + [Workbook B]Sheet1'!J12
K12 value = G12 + [Workbook B]Sheet1'!K12
And so on... ...
I tried multiple approaches, non of them cover all that are required. Below is close, but Cells in ActiveCell.Formula causes it losing relative reference.
CorrelationFile = "Correlation.xlsx" 'Workbook B
CorrelationString = "'" & Folder_Path2 & "\[" & CorrelationFile & "]Sheet1'!H9"
j = 0
Do
Range("H" & ((j * 4) + 5)).Select
ActiveCell.Formula = "=" & Cells(((j * 4) + 5), 4) & "+" & CorrelationString 'Cell reference H5
Selection.AutoFill Destination:=Range("H" & ((j * 4) + 5) & ":K" & ((j * 4) + 5)), Type:=xlFillDefault
Range("H" & ((j * 4) + 5) & ":K" & ((j * 4) + 5)).Select
Selection.AutoFill Destination:=Range("H" & ((j * 4) + 5) & ":K" & ((j * 4) + 8)), Type:=xlFillDefault
j = j + 1
Loop While Range("D" & ((j * 4) + 5)) <> ""