ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Multiplying range: followup for tom (https://www.excelbanter.com/excel-programming/376652-multiplying-range-followup-tom.html)

Caroline
Multiplying range: followup for tom
 
Hi again,
in fact my question was slightly more complicated. I am not sure how to
adapt the "cells" function to my problem.
tHANKS IN ADVANCE

this is my code:


Application.ScreenUpdating = False

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "Country1"
asRangeNames(2) = "Country2"
asRangeNames(3) = "Country3"
asRangeNames(4) = "Country4"
asRangeNames(5) = "Country5"
asRangeNames(6) = "Country6"
asRangeNames(7) = "Country7"
asRangeNames(8) = "Country8"
asRangeNames(9) = "Country9"
asRangeNames(10) = "Country10"

Dim y As Range
Dim asRangeNamesD(1 To 10)
asRangeNamesD(1) = "importCheck1"
asRangeNamesD(2) = "importCheck2"
asRangeNamesD(3) = "importCheck3"
asRangeNamesD(4) = "importCheck4"
asRangeNamesD(5) = "importCheck5"
asRangeNamesD(6) = "importCheck6"
asRangeNamesD(7) = "importCheck7"
asRangeNamesD(8) = "importCheck8"
asRangeNamesD(9) = "importCheck9"
asRangeNamesD(10) = "importCheck10"




Dim W As Range
Dim asRangeNamesW(1 To 10)
asRangeNamesW(1) = "workbook1name"
asRangeNamesW(2) = "workbook2name"
asRangeNamesW(3) = "workbook3name"
asRangeNamesW(4) = "workbook4name"
asRangeNamesW(5) = "workbook5name"
asRangeNamesW(6) = "workbook6name"
asRangeNamesW(7) = "workbook7name"
asRangeNamesW(8) = "workbook8name"
asRangeNamesW(9) = "workbook9name"
asRangeNamesW(10) = "workbook10name"


'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = Worksheets("Sales Data").Range(asRangeNames(i))
Set y = Range(asRangeNamesD(i))
Set W = Range(asRangeNamesW(i))



'THIS IS THE PART I AM NOT TOO SURE ABOUT

x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")

End If

Next i

--
caroline

Tom Ogilvy
Multiplying range: followup for tom
 
x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")


This
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
refers to 202 cells. does x refer to a range of 202 cells? Does Y refer to
a single cell. Assuming x refers to a single cell and y refers to a single
cell, possibly

set rng = Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
for each cell in rng
r = rng(1).row : c = rng(1).col
x.offset(100,2).Offset(cell.row - r, cell.col - c).Value = _
cell * y.Value
Next cell


or if the offset(100,2) was a failed attempt to resize x then just

set rng = Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
for each cell in rng
r = rng(1).row : c = rng(1).col
x.Offset(cell.row - r, cell.col - c).Value = _
cell * y.Value
Next cell

--
Regards,
Tom Ogilvy


"caroline" wrote:

Hi again,
in fact my question was slightly more complicated. I am not sure how to
adapt the "cells" function to my problem.
tHANKS IN ADVANCE

this is my code:


Application.ScreenUpdating = False

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "Country1"
asRangeNames(2) = "Country2"
asRangeNames(3) = "Country3"
asRangeNames(4) = "Country4"
asRangeNames(5) = "Country5"
asRangeNames(6) = "Country6"
asRangeNames(7) = "Country7"
asRangeNames(8) = "Country8"
asRangeNames(9) = "Country9"
asRangeNames(10) = "Country10"

Dim y As Range
Dim asRangeNamesD(1 To 10)
asRangeNamesD(1) = "importCheck1"
asRangeNamesD(2) = "importCheck2"
asRangeNamesD(3) = "importCheck3"
asRangeNamesD(4) = "importCheck4"
asRangeNamesD(5) = "importCheck5"
asRangeNamesD(6) = "importCheck6"
asRangeNamesD(7) = "importCheck7"
asRangeNamesD(8) = "importCheck8"
asRangeNamesD(9) = "importCheck9"
asRangeNamesD(10) = "importCheck10"




Dim W As Range
Dim asRangeNamesW(1 To 10)
asRangeNamesW(1) = "workbook1name"
asRangeNamesW(2) = "workbook2name"
asRangeNamesW(3) = "workbook3name"
asRangeNamesW(4) = "workbook4name"
asRangeNamesW(5) = "workbook5name"
asRangeNamesW(6) = "workbook6name"
asRangeNamesW(7) = "workbook7name"
asRangeNamesW(8) = "workbook8name"
asRangeNamesW(9) = "workbook9name"
asRangeNamesW(10) = "workbook10name"


'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = Worksheets("Sales Data").Range(asRangeNames(i))
Set y = Range(asRangeNamesD(i))
Set W = Range(asRangeNamesW(i))



'THIS IS THE PART I AM NOT TOO SURE ABOUT

x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")

End If

Next i

--
caroline

Caroline
Multiplying range: followup for tom
 
It is the second solution thanks a lot.
but still a little problem,
I get a run time error 438 on that line:
r = rng(1).row : c = rng(1).col

--
caroline


"Tom Ogilvy" wrote:

x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")


This
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
refers to 202 cells. does x refer to a range of 202 cells? Does Y refer to
a single cell. Assuming x refers to a single cell and y refers to a single
cell, possibly

set rng = Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
for each cell in rng
r = rng(1).row : c = rng(1).col
x.offset(100,2).Offset(cell.row - r, cell.col - c).Value = _
cell * y.Value
Next cell


or if the offset(100,2) was a failed attempt to resize x then just

set rng = Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
for each cell in rng
r = rng(1).row : c = rng(1).col
x.Offset(cell.row - r, cell.col - c).Value = _
cell * y.Value
Next cell

--
Regards,
Tom Ogilvy


"caroline" wrote:

Hi again,
in fact my question was slightly more complicated. I am not sure how to
adapt the "cells" function to my problem.
tHANKS IN ADVANCE

this is my code:


Application.ScreenUpdating = False

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "Country1"
asRangeNames(2) = "Country2"
asRangeNames(3) = "Country3"
asRangeNames(4) = "Country4"
asRangeNames(5) = "Country5"
asRangeNames(6) = "Country6"
asRangeNames(7) = "Country7"
asRangeNames(8) = "Country8"
asRangeNames(9) = "Country9"
asRangeNames(10) = "Country10"

Dim y As Range
Dim asRangeNamesD(1 To 10)
asRangeNamesD(1) = "importCheck1"
asRangeNamesD(2) = "importCheck2"
asRangeNamesD(3) = "importCheck3"
asRangeNamesD(4) = "importCheck4"
asRangeNamesD(5) = "importCheck5"
asRangeNamesD(6) = "importCheck6"
asRangeNamesD(7) = "importCheck7"
asRangeNamesD(8) = "importCheck8"
asRangeNamesD(9) = "importCheck9"
asRangeNamesD(10) = "importCheck10"




Dim W As Range
Dim asRangeNamesW(1 To 10)
asRangeNamesW(1) = "workbook1name"
asRangeNamesW(2) = "workbook2name"
asRangeNamesW(3) = "workbook3name"
asRangeNamesW(4) = "workbook4name"
asRangeNamesW(5) = "workbook5name"
asRangeNamesW(6) = "workbook6name"
asRangeNamesW(7) = "workbook7name"
asRangeNamesW(8) = "workbook8name"
asRangeNamesW(9) = "workbook9name"
asRangeNamesW(10) = "workbook10name"


'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = Worksheets("Sales Data").Range(asRangeNames(i))
Set y = Range(asRangeNamesD(i))
Set W = Range(asRangeNamesW(i))



'THIS IS THE PART I AM NOT TOO SURE ABOUT

x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")

End If

Next i

--
caroline

Caroline
Multiplying range: followup for tom
 
found the problem. Column!!
Thanks a lot for the valuable help
--
caroline


"caroline" wrote:

It is the second solution thanks a lot.
but still a little problem,
I get a run time error 438 on that line:
r = rng(1).row : c = rng(1).col

--
caroline


"Tom Ogilvy" wrote:

x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")


This
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
refers to 202 cells. does x refer to a range of 202 cells? Does Y refer to
a single cell. Assuming x refers to a single cell and y refers to a single
cell, possibly

set rng = Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
for each cell in rng
r = rng(1).row : c = rng(1).col
x.offset(100,2).Offset(cell.row - r, cell.col - c).Value = _
cell * y.Value
Next cell


or if the offset(100,2) was a failed attempt to resize x then just

set rng = Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")
for each cell in rng
r = rng(1).row : c = rng(1).col
x.Offset(cell.row - r, cell.col - c).Value = _
cell * y.Value
Next cell

--
Regards,
Tom Ogilvy


"caroline" wrote:

Hi again,
in fact my question was slightly more complicated. I am not sure how to
adapt the "cells" function to my problem.
tHANKS IN ADVANCE

this is my code:


Application.ScreenUpdating = False

Dim i%

Dim x As Range
Dim asRangeNames(1 To 10)
asRangeNames(1) = "Country1"
asRangeNames(2) = "Country2"
asRangeNames(3) = "Country3"
asRangeNames(4) = "Country4"
asRangeNames(5) = "Country5"
asRangeNames(6) = "Country6"
asRangeNames(7) = "Country7"
asRangeNames(8) = "Country8"
asRangeNames(9) = "Country9"
asRangeNames(10) = "Country10"

Dim y As Range
Dim asRangeNamesD(1 To 10)
asRangeNamesD(1) = "importCheck1"
asRangeNamesD(2) = "importCheck2"
asRangeNamesD(3) = "importCheck3"
asRangeNamesD(4) = "importCheck4"
asRangeNamesD(5) = "importCheck5"
asRangeNamesD(6) = "importCheck6"
asRangeNamesD(7) = "importCheck7"
asRangeNamesD(8) = "importCheck8"
asRangeNamesD(9) = "importCheck9"
asRangeNamesD(10) = "importCheck10"




Dim W As Range
Dim asRangeNamesW(1 To 10)
asRangeNamesW(1) = "workbook1name"
asRangeNamesW(2) = "workbook2name"
asRangeNamesW(3) = "workbook3name"
asRangeNamesW(4) = "workbook4name"
asRangeNamesW(5) = "workbook5name"
asRangeNamesW(6) = "workbook6name"
asRangeNamesW(7) = "workbook7name"
asRangeNamesW(8) = "workbook8name"
asRangeNamesW(9) = "workbook9name"
asRangeNamesW(10) = "workbook10name"


'PROCEDURE
For i = 1 To UBound(asRangeNames)

Set x = Worksheets("Sales Data").Range(asRangeNames(i))
Set y = Range(asRangeNamesD(i))
Set W = Range(asRangeNamesW(i))



'THIS IS THE PART I AM NOT TOO SURE ABOUT

x.Offset(100, 2) = y.Value *
Workbooks(W.Value).Worksheets("Forecast").Range("a 10:b110")

End If

Next i

--
caroline


All times are GMT +1. The time now is 05:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com