Use an entire row as "recordset"
 

Please can someone assist me with this as I usually program in access and I'm
struggling with the VB in excel.

From a button_click procedure, I need to set the last row inserted in
"Sheet1" as a recordset in order to use the different values in various cells
in "Sheet2".
e.g Dim rs as recordset in Access.

I would like to say something to the effect of:
Find the last record inserted (the last row on the sheet):
eg: Set rRng = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row), but
for the entire row.
Then I need to be able to say something like:
Set Sheet2 C10 = rs!sheet1 B3

ANY assistance would be greatly appreciated.
Thanks in advance.

Use an entire row as "recordset"
 

I always distrust End(xlUp) because there may be an empty cell in that
column. I use the following to select the last row of data (assuming the
list starts at the top left of your sheet but you could change A1 to
something else):

Range("A1").CurrentRegion.Select
Selection.Offset(Selection.Rows.Count - 1).Resize(1).Select

Rather than a recordset - which, as you say is a bit "Accessy" - why not
store this range of values into an array:

Dim myArray As Variant
Range("A1").CurrentRegion.Select
myArray = Selection.Offset(Selection.Rows.Count - 1).Resize(1)

The nice thing about using a variant array is that you can populate it with
the single step shown rather than having to write a loop. The array is
two-dimensional so the first cell's value is stored in myArray(1,1), second
in myArray(1,2), etc.

"Callan" wrote:

Please can someone assist me with this as I usually program in access and I'm
struggling with the VB in excel.

From a button_click procedure, I need to set the last row inserted in
"Sheet1" as a recordset in order to use the different values in various cells
in "Sheet2".
e.g Dim rs as recordset in Access.

I would like to say something to the effect of:
Find the last record inserted (the last row on the sheet):
eg: Set rRng = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row), but
for the entire row.
Then I need to be able to say something like:
Set Sheet2 C10 = rs!sheet1 B3

ANY assistance would be greatly appreciated.
Thanks in advance.

Use an entire row as "recordset"
 

if you set up two range objects
dim rng1 as range
and set one to the first cell in which you are interested eg set
rng1=worksheets("sheet1").range("A3") and one to the cell on the second
sheet; you can use offset to address the particular cells in which you
are interested eg

rng2.offset(0,3)=rng1.offset(-2,4)

does this help

Callan Wrote:

I would like to say something to the effect of:
Find the last record inserted (the last row on the sheet):
eg: Set rRng = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row),
but
for the entire row.
Then I need to be able to say something like:
Set Sheet2 C10 = rs!sheet1 B3

ANY assistance would be greatly appreciated.
Thanks in advance.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=529521

Use an entire row as "recordset"
 

Hi Tony, thanks, your way seems to work.
I also managed to stumble across another way if anybody reads this and finds
it useful:

Static mRow As Long
mRow = Target.Row

This targets an entire Row when the user clicks a cell.
You can then say things like:
Row(mRow).Range("the cell you want").Value
e.g
SJ.Range("C5").Value = Row(mRow).Range("H1").value
It's just not as "clean" as your way.
Thanks again.
--
Callan


"tony h" wrote:


if you set up two range objects
dim rng1 as range
and set one to the first cell in which you are interested eg set
rng1=worksheets("sheet1").range("A3") and one to the cell on the second
sheet; you can use offset to address the particular cells in which you
are interested eg

rng2.offset(0,3)=rng1.offset(-2,4)

does this help

Callan Wrote:

I would like to say something to the effect of:
Find the last record inserted (the last row on the sheet):
eg: Set rRng = Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row),
but
for the entire row.
Then I need to be able to say something like:
Set Sheet2 C10 = rs!sheet1 B3

ANY assistance would be greatly appreciated.
Thanks in advance.


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=529521