Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Excel 97 ADODB Recordset.Find "Invalid Use" error | Excel Programming | |||
Copy recordset from an Access "make table" query | Excel Programming | |||
Creating small "recordset" or sorting a numeric 1-dim array | Excel Programming |