Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Multiplying range: followup for tom
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 183
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 183
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 183
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
multiplying rows and passing formula throughout the col range Sistereinstein Excel Discussion (Misc queries) 3 December 31st 07 08:31 PM
Multiplying specific data within a range to get column totals MsChief Excel Worksheet Functions 2 March 30th 07 04:44 AM
Multiplying A Range DanielWalters6 Excel Worksheet Functions 7 November 21st 06 12:03 PM
multiplying range caroline Excel Programming 2 November 3rd 06 02:03 PM
Followup to "search range for duplicates" Doug Loewen[_2_] Excel Programming 2 April 16th 04 11:22 AM


All times are GMT +1. The time now is 09:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement