S
sillyhat
Hello,
Can someone please help. I am trying to understand how VBA can be used
to get data from one sheet to another. I have some code that works but
I think it could be a bit better.
With regard to the code below, the queries I have are as follows:-
Dim XR
Why can't/shouldn't this be defined as a range?
Dim x
Should this be a variant?
XR = Sheets(WS1).Columns(FromCol)
Might be better to define a range since we have lastrow but how?
For Each x In XR
Why doesn't 'For Each x In Sheets(WS1).Columns(FromCol)' work,
since XR was set to be Sheets(WS1).Columns(FromCol) anyway!
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
Why don't these work:
YR.Cells(i, ToCol).Value = x^2
YR.Row(i).Value = x^2
What variations that references YR would work?
Thanks for all constructive advice given.
Hal
Code follows....
'---------8<---------8<---------8<---------8<---------8<---------8<---------
'Sheet1 has a list of numbers in column "A"
'----------------------------------------------------------
Sub GenData()
Call GenSquares("Sheet1", "A", "Sheet2", "D")
End Sub
'----------------------------------------------------------
Private Sub GenSquares(WS1, FromCol, WS2, ToCol)
Dim XR
Dim YR
Dim x
Dim i As Integer
Dim lastrow As Integer
lastrow = Sheets(WS1).UsedRange.Rows.Count
XR = Sheets(WS1).Columns(FromCol)
YR = Sheets(WS2).Columns(ToCol)
i = 1
For Each x In XR
If i > lastrow Then Exit For
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
i = i + 1
Next x
End Sub
'----------------------------------------------------------
'---------8<---------8<---------8<---------8<---------8<---------8<---------
Can someone please help. I am trying to understand how VBA can be used
to get data from one sheet to another. I have some code that works but
I think it could be a bit better.
With regard to the code below, the queries I have are as follows:-
Dim XR
Why can't/shouldn't this be defined as a range?
Dim x
Should this be a variant?
XR = Sheets(WS1).Columns(FromCol)
Might be better to define a range since we have lastrow but how?
For Each x In XR
Why doesn't 'For Each x In Sheets(WS1).Columns(FromCol)' work,
since XR was set to be Sheets(WS1).Columns(FromCol) anyway!
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
Why don't these work:
YR.Cells(i, ToCol).Value = x^2
YR.Row(i).Value = x^2
What variations that references YR would work?
Thanks for all constructive advice given.
Hal
Code follows....
'---------8<---------8<---------8<---------8<---------8<---------8<---------
'Sheet1 has a list of numbers in column "A"
'----------------------------------------------------------
Sub GenData()
Call GenSquares("Sheet1", "A", "Sheet2", "D")
End Sub
'----------------------------------------------------------
Private Sub GenSquares(WS1, FromCol, WS2, ToCol)
Dim XR
Dim YR
Dim x
Dim i As Integer
Dim lastrow As Integer
lastrow = Sheets(WS1).UsedRange.Rows.Count
XR = Sheets(WS1).Columns(FromCol)
YR = Sheets(WS2).Columns(ToCol)
i = 1
For Each x In XR
If i > lastrow Then Exit For
Sheets(WS2).Cells(i, ToCol).Value = x ^ 2
i = i + 1
Next x
End Sub
'----------------------------------------------------------
'---------8<---------8<---------8<---------8<---------8<---------8<---------