Using variable inside a range

[Brijesh]

Hi Everybody

I have defined name of range of cells B19 to B23 on sheet1 of excel workbook as SARbtRng and put some numeric values in these cells (e.g. 10000, 15000, 25000, 33000, 50000)
Again I have defined name of 5 cells on sheet 2 as under
Cell Name Defined
K9 SA1
K10 SA2
K11 SA3
K12 SA4
K13 SA5
All these names are defined in excel and till this point no vba command is used.

What I want to do is that to write a code which will pick values one by one from range SARbtRng and put these in corresponding cells on sheet2 i.e. It takes value from cell B19 (e.g 10000) and put it in the cell named SA1 (i.e. cell K9) on sheet 2 and then it take value from cell B20 (e.g. 15000) and put it in the cell named SA1 (i.e. cell K10) on sheet 2 and so on...

I am using the following code

Sub B()

For i = 1 To [SARbtRng].Rows.Count
SumAssuredi = "SumAssured" & i
SumAssured = [SARbtRng].Cells(i, 1).Value
Range("SumAssuredi") = SumAssured

Next i

End Sub

This gives the error "Method 'Range' of object '_global' failed.
Please suggest why this error is coming and what code should be used

 

[NARAYANK991]

Hi Brijesh ,

Try this :

Sub B()
    Dim i As Integer
    For i = 1 To [SARbtRng].Rows.Count
        Range("SA" & i & "_").Value = [SARbtRng].Cells(i, 1).Value
    Next i
End Sub

I am using Excel 2007 , and SA1 is being taken as a cell address , which is why it cannot be used to name a range ; instead Excel converts it to SA1_ , SA2_ , SA3_ ,....

Narayan

 

[Brijesh]

Thanks Narayan

Infact I was not exactly using the name SA1, instead i was using name SumA1.
What I changed in coding is that I replaced Range ("SumAssuredi") with Range ("SumAssured" & i). The code run successfully.
Although I have corrected my macro before reading your reply but still your reply was very useful bcoz earlier I was known that I can not use any name which resembles any column name (e.g SA) but I was not aware that even I can not use any name which resembles any cell address. Thanks a lot...

I have a new problem in excel. I have some numbers in first 5 rows of two columns A and B

A B
10 45
12 65
22 29
13 34
17 26

I want sum of product of numbers in first 5 rows of third column C in a below manner:
C
10*45
10*65+12*45
10*29+12*65+22*45
10*34+12*29+22*65+13*45
10*26+12*34+22*29+13*65+17*45

Means for a particular row (say row 3) of column C, I have to take number from columns A and B upto that row (i.e. numbers in column A and B upto row 3) and then multiply them in reverse order (means row 1 value of column A to be multiplied by row 3 value of column B, row 2 value of column A by row 2 value of Column B, row 3 value of column A by row 1 value of column B) and then to sum these product.

Is there any function in excel to perform these calculations? I don't want to use vba in this case.

Brijesh

 

[NARAYANK991]

Hi Brijesh ,

There is no single function in Excel to do what you want , though it may be possible using the MMULT function ; even here it will not be straightforward , since you want an reversal of the second range before the elements are multiplied.

With VBA it will be straightforward.

Narayan

 

[Haseeb A]

Hello Bijesh,

Assuming data starting from A2:B2, if so try this in C2, then copy down.

=SUMPRODUCT(A$2:A2,N(OFFSET(B2,ROW(B$2)-ROW(B$2:B2),)))

Note: All $ signs are important.