How to change R1C1 notat. to cell notat. for iterative sumproducts? 1

[friedmar]

Hi,

I have some experience in Excel but I am a beginner in VBA.
So, I hope you will not be angry that I have the following problem.


Suppose you have a matrix which is stored in sheet "UI"

line no.2 nuklid1 0
line no.3 nuklid2 2,00E10
line no.4 nuklid3 3,00E10
line no.5 nuklid4 0
line no.6 nuklid5 5,00E10


The other table (the active sheet is DOSES):

line no. 6 nuclides distance1 distance2 distance3
line no. 7 500 1000 1500
line no. 8 nuklid1 1,00E-11 1,00E-12 1,00E-13
line no. 9 nuklid2 2,00E-11 2,00E-12 2,00E-13
line no.10 nuklid3 3,00E-11 3,00E-12 3,00E-13
line no.11 nuklid4 4,00E-11 4,00E-12 4,00E-13
line no.12 nuklid5 5,00E-11 5,00E-12 5,00E-13

UnitDose 3,8 0,38 0,038


With help of the macro recorder i got the results in the UnitDose line:
Summing the products UI!B2 * B8 + ... UI!B6 * B12 for each distance
gives 3,8 0,38 0,038.


If have the problem to automate and simplify the following VBA macro:

Later on I will have more distances (more distance rows) to consider and perhaps much more nuclide rows.

MY QUESTIONS:

Is it possible to use some loop procedure and cell indexing (cell(i,j)
which simplifies the ActiveCell.FormulaR1C1 line.

How the macro has to be rewritten to

- loop over the various distances (1 to 3)
- cover the SUMPRODUCT formula give below in a cell(i,j) notation instead
using the R1C1 properties. I think I must have the sumproduct formula
in a cell(i,j) notation to be able to loop.



Sub macroff()
'
' macroff Makro
' Makro from 04.09.2003
'

'
ActiveCell.Offset(0, 1).Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(UI!R2C2:UI!R6C2,R[-6]C2:R12C2)"
ActiveCell.Offset(0, 1).Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(UI!R2C2:UI!R6C2,R[-6]C3:R12C3)"
ActiveCell.Offset(0, 1).Range("A1&quot.Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(UI!R2C2:UI!R6C2,R[-6]C4:R12C4)"
ActiveCell.Offset(0, 1).Range("A1&quot.Select
' ActiveCell.FormulaR1C1 = "=SUMPRODUCT(UI!R2C2:UI!R6C2,R[-6]C5:R12C5)"
' ActiveCell.Offset(0, 1).Range("A1&quot.Select
' ActiveCell.FormulaR1C1 = "=SUMPRODUCT(UI!R2C2:UI!R6C2,R[-6]C6:R12C6)"
' ActiveCell.Offset(0, 1).Range("A1&quot.Select
' ActiveCell.FormulaR1C1 = "=SUMPRODUCT(UI!R2C2:UI!R6C2,R[-6]C7:R12C7)"
' ActiveCell.Offset(1, 0).Range("A1&quot.Select
End Sub


Any help is appreciated.

Thanks
Friedmar

 

[SkipVought]

Hi,

Your loop should look something like this...

Sub macroff()
    Dim sAddr1 As String, sAddr2 As String
    For iCol = 2 To 4
        sAddr1 = Cells(2, 2).Address(external:=True) & ":" & _
                Cells(6, 2).Address(external:=True)
        sAddr2 = Cells(1, 2).Address(external:=True) & ":" & _
                Cells(12, iCol).Address(external:=True)
        Range("A1").Formula = "=SUMPRODUCT(" & sAddr1 & "," & sAddr2 & ")"
    Next
End Sub

Hope this helps ;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[friedmar]

Hi Skip,
I had some days of vacation, so there was a delay to thank you. Keep in mind, I am a newbie in VBA. So there are again questions to your solution.

I changed your code a little bit, because your code snippet
had to be changed a little bit. I have two sheets in my workbook UI and DOSES. B2 to B6 of UI should be combined with B8 to B12, C8 to C12, D8 to D12 in the SUMPRODUCT formulaand the result sould be in B14,C14,D14
Running your modified macro gives #VALUE! in B14
------
What is going wrong in the macro ? How to correct and optimize the code ?
Sub macroff3()
Dim iCol As Integer
Dim sAddr1 As String, sAddr2 As String
For iCol = 2 To 4
sAddr1 = Workbooks("fftest0a.xls&quot.Sheets("UI&quot.Cells(2, 2).Address(external:=True) & ":" & _
Workbooks("fftest0a.xls&quot.Sheets("UI&quot.Cells(6, 2).Address(external:=True)
sAddr2 = Cells(8, 2).Address(external:=False) & ":" & _
Cells(12, iCol).Address(external:=False)
Range("B14&quot.Formula = "=SUMPRODUCT(" & sAddr1 & "," & sAddr2 & &quot"
Next
End Sub

Any help is appreciated !
Best regards Friedmar

 

[SkipVought]

I have assumed that the ranges that were unreferenced by sheets, were DOSES sheet. Here's the procedure modified...

Sub macroff3()
    Dim iCol As Integer
        Dim sAddr1 As String, sAddr2 As String
    For iCol = 2 To 4
        sAddr1 = ActiveWorkbook.Sheets("UI").Cells(2, iCol).Address(external:=True) & ":" & _
                ActiveWorkbook.Sheets("UI").Cells(6, iCol).Address(external:=True)
        sAddr2 = ActiveWorkbook.Sheets("DOSES").Cells(8, iCol).Address(external:=False) & ":" & _
                ActiveWorkbook.Sheets("DOSES").Cells(12, iCol).Address(external:=False)
        ActiveWorkbook.Sheets("DOSES").Cells(14, iCol).Formula = "=SUMPRODUCT(" & sAddr1 & "," & sAddr2 & ")"
    Next
End Sub

Is this what you're looking for?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[friedmar]

Hi Skip,

the macro now works perfectly (see last and successful version below), thanks a lot .

Now it is better to understand what is going on in your modified script.

Alway the values from sheet("UI&quot have been B2 up to B6 for ALL cases. Therefore I changed only the iCol values to fixed values in your macro code for the sAddr1 line:

sAddr1 = ActiveWorkbook.Sheets("UI&quot.Cells(2, 2).Address(external:=True) & ":" & _
ActiveWorkbook.Sheets("UI&quot.Cells(6, 6).Address(external:=True)

Here is the working macro:

Sub macroff3()
Dim iCol As Integer
Dim sAddr1 As String, sAddr2 As String
For iCol = 2 To 4
sAddr1 = ActiveWorkbook.Sheets("UI&quot.Cells(2, 2).Address(external:=True) & ":" & _
ActiveWorkbook.Sheets("UI&quot.Cells(6, 2).Address(external:=True)
sAddr2 = ActiveWorkbook.Sheets("DOSES&quot.Cells(8, iCol).Address(external:=False) & ":" & _
ActiveWorkbook.Sheets("DOSES&quot.Cells(12, iCol).Address(external:=False)
ActiveWorkbook.Sheets("DOSES&quot.Cells(14, iCol).Formula = "=SUMPRODUCT(" & sAddr1 & "," & sAddr2 & &quot"
Next
End Sub

 

[SkipVought]

Great!

Glad you got it working to meet your requirements

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com