Paste value from another workbook and multiply by constant

[burl_rfc_h]

I'm having issues with the following code, after the value is pasted
into the second workbook I'd like to multiply the value by a constant,
can someone please help.

Sub loop7()
Dim i As Integer

Workbooks("Part List.xls").Activate

Range("E2").Select

intRowCount = Range("E2").CurrentRegion.Rows.Count - 1

For i = 1 To intRowCount

ActiveCell.Copy

Const diameter = 25.4
Const metric = 1000

Workbooks("Test1.xls").Sheets("Sheet1").Range("B1").PasteSpecial
Paste:=xlValues * diameter

ActiveCell.Offset(0, 2).Copy
Workbooks("Test1.xls").Sheets("Sheet1").Range("B2").PasteSpecial
Paste:=xlValues * metric

Next i

End sub


Thanks

 

[Tom Ogilvy]

It is unclear where you want to copy your values since your destination
never changes in the code you show. It also isn't clear what the sheet name
in Part List.xls is specified. Here is one possibility

Sub loop7()
Dim i As Integer
Const diameter = 25.4
Const metric = 1000
Dim rng as Range, cell as Range

With Workbooks("Part List.xls").Worksheets("Sheet1")
set rng = .Range(.Range("E2"), _
.Range("E2").End(xlup).offset(0,-3))
End With

With Workbooks("Test1.xls").Sheets("Sheet1").Range("B1")

i = 0
For each cell in rng
.offset(i,0).Value = cell * diametet
.offset(i,1).Value = cell.offset(0,2) * metric
i = i + 1
Next
End sub

 

[burl_rfc_h]

Actually I just realized I also have a For Next issue, I removed the
constant from the paste special just to ensure the macro is looping
correctly and it not, what could be wrong with the looping routine.

Sub loop7()
Dim i As Integer


Workbooks("Part List.xls").Activate


Range("E2").Select


intRowCount = Range("E2").CurrentRegion.Rows.Count - 1


For i = 1 To intRowCount


ActiveCell.Copy

Const diameter = 25.4
Const metric = 1000

Workbooks("Test1.xls").Sheets("Sheet1").Range("B1").PasteSpecial
Paste:=xlValues

ActiveCell.Offset(0, 2).Copy
Workbooks("Test1.xls").Sheets("Sheet1").Range("B2").PasteSpecial
Paste:=xlValues

Next i

End sub

Thanks

 

[burl_rfc_h]

Tom,

Thanks for your reply.

Here's what I'm trying to do, in the Part List workbook I want to loop
through column E and G, take the values from each row and place into
cells B2 and C2 of the Test1.xls workbook, but I want to multiply the
value in B2 by 25.4 and cell C2 multiply by 1000. The macro shown in my
1st posting doesn't even loop, once I can get this macro to loop
correctly then I have other things I'm planning on doing with the
pasted values, in a nutshell this is the first step of what I intend to
do.

I'm not sure what the following code is doing in your suggestion, could
you explain.

With Workbooks("Part List.xls").Worksheets("Sheet1")
set rng = .Range(.Range("E2"), _
.Range("E2").End(xlup).offset(0,-3))
End With


Thanks

 

[Tom Ogilvy]

Sub loop7()
Dim i As Long
Const diameter = 25.4
Const metric = 1000
Dim rng as Range

' Determine the extent of the data in column E
With Workbooks("Part List.xls").Worksheets(1)
set rng = .Range(.Range("E2"), _
.Range("E65536").End(xlup))
End With

With Workbooks("Test1.xls").Sheets("Sheet1")
rng.copy
.Range("B2").PasteSpecial xlValues
rng.offset(,2).Copy
.Range("C2").PasteSpecial xlValue

For i = 2 to rng.count+2
.cells(i,"B").Value = .cells(i,"B").Value * diametet
.Cells(i,"C").Value = .Cells(i,"C").Value * metric
Next
End With
End sub

 

[burl_rfc]

Tom,

Again thanks for your reply....

The macro isn't quite doing what I expected, the range selected in
column E per the following code

Determine the extent of the data in column E
With Workbooks("Part List.xls").Worksheets(1)
set rng = .Range(.Range("E2"), _
.Range("E65536").End(xlup))
End With

It is selecting the entire column, this data is then pasted into the
Test.xls workbook, what I really need is for the macro to loop through
each row in Part List.xls. For example, starting in E2 in the Part
List.xls the value is placed into B1 on Test1.xls, then the value in G2
in Part list.xls is placed into cell B2 in Test1.xls, once this is done
a calculated value from Test1.xls cell B7 is placed into the Part
List.xls column K on the same row the original data came from, then the
macro should go to the next row in Part List.xls column E and continue
the same process until all records in column E have been processed. The
values pasted into Test1.xls cells B1 and B2 will be multiplied by the
constants diameter and metric respectively. Sorry fro any confusion.

 

[burl_rfc]

Tom,

Thanks for your response, unfortunately the macro is not working
correctly, I'm thinking I may have confused you along the way.

I really need to loop through the Part list.xls workbook on column E,
the first cell containing data will be E2 this value is then to be
pasted into Test1.xls cell B1, then again on Part List.xls in G2 the
value I need to paste into cell B2 in Test1.xls. Once the pasted values
are in Test1.xls I need to multiply the value in B1 by 25.4 (diameter
variable) and B2 by 1000 (metric variable). After this is completed I
then need to get the value from B7 in Test1.xls and paste it into Part
List.xls cell K2. This would complete the first cycle, now the loop
should continue going through Part List.xls until all values are
processed in column E

Thanks

Burl_rfc

 

[Tom Ogilvy]

Yes, that doesn't seem to match:
Again, modify Worksheets(1) to reflect the sheet in Part List.xls that you
want to loop through.

Sub loop7()
Const diameter = 25.4
Const metric = 1000
Dim rng as Range, cell as Range

' Determine the extent of the data in column E
With Workbooks("Part List.xls").Worksheets(1)
set rng = .Range(.Range("E2"), _
.Range("E65536").End(xlup))
End With

With Workbooks("Test1.xls").Sheets("Sheet1")
for each cell in rng
.Range("B1").Vaue = cell.Value * diameter
.Range("B2").Value = cell.offset(0,2).Value * metric
cell.offset(0,6).Value = .Range("B7")
Next
End With
End sub

 

[burl_rfc_h]

Tom,

The solution worked great, thanks for your help, it's greatly
appreciated.

Burl_rfc_h