Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
i have a userform i fill doing a vlookup from a table. if the user changes
the record, how do i determine the row number the data was in to copy the data back to the table? -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
Gary, Copy the records to variant array before the user-change and the re-copy same range into a second variant array. Next take th differences between the two: changes will be reflected by zer differences. It is inconsequential whether data is numeric alphanumeric or whatever. Illustration: Dim v Dim u Before user effects changes: 'create 1st variant array v=Range("a1:a100").value After changes: 'create 2nd variant array u=Range("a1:a100").value 'copy diffs to normal array Dim dArray() For i = 1 to Application.CountA([a:a]) Redim preserve dArray(1 to i) dArray(i)= v(i,1)-u(i,1) Next 'determine row nos where changes were made by setting dArray(x)= 0 For x = 1 to Application.CountA([a:a]) If dArray(x)= 0, then k = k & "Row " & x & ":" end if Next Msgbox k 'lists all rows wuth changes End sub HTH Davi -- david ----------------------------------------------------------------------- davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064 View this thread: http://www.excelforum.com/showthread.php?threadid=48449 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
kind of complicated for me to understand. i ended up just adding a rownumber
field and put it into a hidden textbox on the form. i have no idea what's going on here now, because i did just about the same thing on my first form programming, but every time i try to change and update a record, only the first field changes. but i think i have it now Gary "davidm" wrote in message ... Gary, Copy the records to variant array before the user-change and then re-copy same range into a second variant array. Next take the differences between the two: changes will be reflected by zero differences. It is inconsequential whether data is numeric, alphanumeric or whatever. Illustration: Dim v Dim u Before user effects changes: 'create 1st variant array v=Range("a1:a100").value After changes: 'create 2nd variant array u=Range("a1:a100").value 'copy diffs to normal array Dim dArray() For i = 1 to Application.CountA([a:a]) Redim preserve dArray(1 to i) dArray(i)= v(i,1)-u(i,1) Next 'determine row nos where changes were made by setting dArray(x)= 0 For x = 1 to Application.CountA([a:a]) If dArray(x)= 0, then k = k & "Row " & x & ":" end if Next Msgbox k 'lists all rows wuth changes End sub HTH David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=484491 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
Gary, why don't you just use MATCH against the first column of the lookup
data to get its index myRow = Application.VLOOKUP(uf_val,Worksheets("Sheet1").Ra nge("A1:A100",0) If the table does not start in row 1 do not forget to add the number of rows preceding it as MATCH returns the index into the data not the row number. -- HTH RP (remove nothere from the email address if mailing direct) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... kind of complicated for me to understand. i ended up just adding a rownumber field and put it into a hidden textbox on the form. i have no idea what's going on here now, because i did just about the same thing on my first form programming, but every time i try to change and update a record, only the first field changes. but i think i have it now Gary "davidm" wrote in message ... Gary, Copy the records to variant array before the user-change and then re-copy same range into a second variant array. Next take the differences between the two: changes will be reflected by zero differences. It is inconsequential whether data is numeric, alphanumeric or whatever. Illustration: Dim v Dim u Before user effects changes: 'create 1st variant array v=Range("a1:a100").value After changes: 'create 2nd variant array u=Range("a1:a100").value 'copy diffs to normal array Dim dArray() For i = 1 to Application.CountA([a:a]) Redim preserve dArray(1 to i) dArray(i)= v(i,1)-u(i,1) Next 'determine row nos where changes were made by setting dArray(x)= 0 For x = 1 to Application.CountA([a:a]) If dArray(x)= 0, then k = k & "Row " & x & ":" end if Next Msgbox k 'lists all rows wuth changes End sub HTH David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=484491 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
David, I get a 'Type Mismatch' on the line:
dArray(i)= v(i,1)-u(i,1) I don't see what it is. Mike F "davidm" wrote in message ... Gary, Copy the records to variant array before the user-change and then re-copy same range into a second variant array. Next take the differences between the two: changes will be reflected by zero differences. It is inconsequential whether data is numeric, alphanumeric or whatever. Illustration: Dim v Dim u Before user effects changes: 'create 1st variant array v=Range("a1:a100").value After changes: 'create 2nd variant array u=Range("a1:a100").value 'copy diffs to normal array Dim dArray() For i = 1 to Application.CountA([a:a]) Redim preserve dArray(1 to i) dArray(i)= v(i,1)-u(i,1) Next 'determine row nos where changes were made by setting dArray(x)= 0 For x = 1 to Application.CountA([a:a]) If dArray(x)= 0, then k = k & "Row " & x & ":" end if Next Msgbox k 'lists all rows wuth changes End sub HTH David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=484491 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
Where Bob has
myRow = Application.VLOOKUP(uf_val,Worksheets("Sheet1").Ra nge("A1:A100",0) should be myRow = Application.Match(uf_val,Worksheets("Sheet1").Rang e("A1:A100"),0) -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... Gary, why don't you just use MATCH against the first column of the lookup data to get its index myRow = Application.VLOOKUP(uf_val,Worksheets("Sheet1").Ra nge("A1:A100",0) If the table does not start in row 1 do not forget to add the number of rows preceding it as MATCH returns the index into the data not the row number. -- HTH RP (remove nothere from the email address if mailing direct) "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... kind of complicated for me to understand. i ended up just adding a rownumber field and put it into a hidden textbox on the form. i have no idea what's going on here now, because i did just about the same thing on my first form programming, but every time i try to change and update a record, only the first field changes. but i think i have it now Gary "davidm" wrote in message ... Gary, Copy the records to variant array before the user-change and then re-copy same range into a second variant array. Next take the differences between the two: changes will be reflected by zero differences. It is inconsequential whether data is numeric, alphanumeric or whatever. Illustration: Dim v Dim u Before user effects changes: 'create 1st variant array v=Range("a1:a100").value After changes: 'create 2nd variant array u=Range("a1:a100").value 'copy diffs to normal array Dim dArray() For i = 1 to Application.CountA([a:a]) Redim preserve dArray(1 to i) dArray(i)= v(i,1)-u(i,1) Next 'determine row nos where changes were made by setting dArray(x)= 0 For x = 1 to Application.CountA([a:a]) If dArray(x)= 0, then k = k & "Row " & x & ":" end if Next Msgbox k 'lists all rows wuth changes End sub HTH David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=484491 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
If you have non-numeric values in your range, you could get a type mismatch
error ex in the immediate window: ? "A" - "B" produces type mismatch. -- Regards, Tom Ogilvy "Mike Fogleman" wrote in message ... David, I get a 'Type Mismatch' on the line: dArray(i)= v(i,1)-u(i,1) I don't see what it is. Mike F "davidm" wrote in message ... Gary, Copy the records to variant array before the user-change and then re-copy same range into a second variant array. Next take the differences between the two: changes will be reflected by zero differences. It is inconsequential whether data is numeric, alphanumeric or whatever. Illustration: Dim v Dim u Before user effects changes: 'create 1st variant array v=Range("a1:a100").value After changes: 'create 2nd variant array u=Range("a1:a100").value 'copy diffs to normal array Dim dArray() For i = 1 to Application.CountA([a:a]) Redim preserve dArray(1 to i) dArray(i)= v(i,1)-u(i,1) Next 'determine row nos where changes were made by setting dArray(x)= 0 For x = 1 to Application.CountA([a:a]) If dArray(x)= 0, then k = k & "Row " & x & ":" end if Next Msgbox k 'lists all rows wuth changes End sub HTH David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=484491 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
need help determining a row number
Mike, Tom was right. To cover both numeric and alphanumeric values, the following modification is needed, making code all-purpose. Sub FindRowChanges() Dim v Dim u 'populate range with 123abc Range("a1:a100") = "123abc" Range("a1:a100").Value = Range("a1:a100").Value 'create 1st variant array v = Range("a1:a100").Value 'effect changes to a5 and a10 Range("a5") = 500 Range("a10") = 800 'After changes: 'create 2nd variant array u = Range("a1:a100").Value 'check differences For i = 1 To Application.CountA([a:a]) If Not v(i, 1) Like u(i, 1) Then k = k & "Row " & i & ":" End If Next MsgBox k 'lists all rows wuth changes End Sub -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=484491 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining the highest number in a row | Excel Discussion (Misc queries) | |||
Determining new category number | Excel Programming | |||
Determining Row Number | Excel Programming | |||
Determining Active Row Number | Excel Programming | |||
Determining Active Row Number | Excel Programming |