How to add non adjacent cells?

[jaefo]

Hi, I have a question on how to add non adjacent cells. What I am trying to do is transfer a cell value to a location in the same sheet and then adding (3) cells and place the result in another cell same sheet. This is what I wrote but gives me a syntax error.

workbooks("sales").Sheets(i).Range("L40").Value = workbooks("sales").Sheets(i).Range("K31").Value

workbooks("sales").Sheets(i).Range("L42").Value = workbooks("sales").Sheets(i).Range("U31+AE31+AO31").Value


I also tried to create a variable to hold the sum, but then I get an object error. This is what i tried"

x= (U31+AE31+AO31)

workbooks("score").Sheets(i).Range("L40").Value = workbooks("score").Sheets(i).Range("K31").Value

workbooks("score").Sheets(i).Range("L42").Value = workbooks("score").Sheets(i).Range(x).Value


Thank you for your time,

jaefo

 

[anupagarwal06]

It seems excel does not recognize the way you have input the ranges. Also In my system, this "Sheets(i)" thing is not working thus I have used "Sheets(1). Try the following code, this seems to work.


workbooks("sales").Sheets(1).Range("L40").Value = workbooks("sales").Sheets(1).Range("K31").Value

workbooks("sales").Sheets(1).Range("L42").Value = workbooks("sales").Sheets(1).Range("U31").Value+workbooks("sales").Sheets(1).Range("AE31").Value+workbooks("sales").Sheets(1).Range("AO31").Value


But as I always say, there is always a better way.

 

[jaefo]

Hi Anupagarwal06,


I tried your code,

The (i) is because I have several sheets in the workbooks and I needed the transfer to occur from sheet1 sales to sheet1 report and so on. So when I copied it I substitute the 1 for the i,but I get a subscript out of range error message.

I do not know what it means, I have not change anything else.

jaefo

 

[anupagarwal06]

Dear Jaefo,


From what I understand of the problem, this would work.

Assuming you have figures in different sheets of "sales" workbook and want reports in different sheets in "score" workbook. This code works when both the workbooks are open.


Dim i As Integer

For i = 1 To 3 Step 1

Workbooks("score").Sheets(i).Range("A1").Value = Workbooks("sales").Sheets(i).Range("B1").Value

Workbooks("score").Sheets(i).Range("A2").Value = Workbooks("sales").Sheets(i).Range("B2").Value + Workbooks("sales").Sheets(i).Range("C2").Value + Workbooks("sales").Sheets(i).Range("D2").Value

Next i


I am not very sure but check the exact name of the workbooks including upper/lower case.

 

[jaefo]

Thank you so much Anupagarwal06 I works!

 

[Hui]

Looking at your range you could just

Workbooks("score").Sheets(i).Range("A2").Value = Application.WorksheetFunction.Sum(Workbooks("sales").Sheets(i).Range("B2:D2"))


You could also define a Named Formula say mySales on the Sales sheet as

mySales: =Sales!B2,Sales!C2,SALES!D2


and then


workbooks("sales").Sheets(1).Range("L42").Value = Application.WorksheetFunction.Sum(Range("mySales"))