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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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").Range("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
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Where Bob has
myRow = Application.VLOOKUP(uf_val,Worksheets("Sheet1").Range("A1:A100",0)

should be

myRow = Application.Match(uf_val,Worksheets("Sheet1").Range("A1:A100"),0)


--
Regards,
Tom Ogilvy

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> 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").Range("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
> news:[email protected]...
> > 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" <[email protected]> wrote in
> > message news:[email protected]...
> > >
> > > 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
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

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