PDA

View Full Version : [SOLVED] Reference Another Workbook Using Relative and Fixed Cell References



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)) <> ""

SamT
05-09-2017, 06:29 PM
Sub Autofill_H5Blocks()
Range("D5:G17").Copy Range("H5")
Workbooks("Correlation.xlsx").Sheets("Sheet1").Range("H9:K17").Copy
Range("H5").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End Sub

yannezhang
05-10-2017, 03:41 PM
Thanks, SamT

PasteSpecial is really cool. I ran it, got another issue. The block to be copied in the correlation file contains fomula, not value. So I added to your last line:
Range("H5").PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationAdd

It works. Thank you again for your help!

SamT
05-10-2017, 08:01 PM
:thumb