hi everyone

I m trying to convert this worksheet formula

=INDEX($H$1:$H$499,MATCH(D1,$G$1:$G$499,0))

=INDEX(Range to Search,MATCH(Cell to Match, Range in which to search
for match, FALSE)

to macro [in sub, not function ]

no.of rows are unknown .......plz help me convert above formula to vba
code

I hop i get support from u experts as i got earlier
many thanks in advance for u time n effort

On Apr 28, 5:12 pm, wrote:
hi everyone

I m trying to convert this worksheet formula

=INDEX($H$1:$H$499,MATCH(D1,$G$1:$G$499,0))

=INDEX(Range to Search,MATCH(Cell to Match, Range in which to search
for match, FALSE)

to macro [in sub, not function ]

no.of rows are unknown .......plz help me convert above formula to vba
code

I hop i get support from u experts as i got earlier
many thanks in advance for u time n effort

i would lik to add one more thing here that my match value "d1" in
above formula

but in macro i want to match all values in column "d" [i.e.
d1,d2....d(n)] in column g (where no.of rows are unknwon )
and no. of rows in column H [ range in which value to b srchd ] are
also unknown

waitng 4 ur replys
many thanx

values writes to column E - change Sh1 to ur sheet

Sub vbaMatch()

Dim d, g, h, t
Sheets("Sh1").Activate
d = Cells(65535, 4).End(xlUp).Row
g = Cells(65535, 7).End(xlUp).Row
h = Cells(65535, 8).End(xlUp).Row
On Error Resume Next

For t = 1 To d
Range("E" & t) = Range("G" & Range("H1:H" & h).Find(Range("D" & t),
LookIn:=xlValues).Row)
Next

End Sub



" skrev:

On Apr 28, 5:12 pm, wrote:
hi everyone

I m trying to convert this worksheet formula

=INDEX($H$1:$H$499,MATCH(D1,$G$1:$G$499,0))

=INDEX(Range to Search,MATCH(Cell to Match, Range in which to search
for match, FALSE)

to macro [in sub, not function ]

no.of rows are unknown .......plz help me convert above formula to vba
code

I hop i get support from u experts as i got earlier
many thanks in advance for u time n effort

i would lik to add one more thing here that my match value "d1" in
above formula

but in macro i want to match all values in column "d" [i.e.
d1,d2....d(n)] in column g (where no.of rows are unknwon )
and no. of rows in column H [ range in which value to b srchd ] are
also unknown

waitng 4 ur replys
many thanx

On Apr 28, 6:40 pm, excelent
wrote:
values writes to column E - change Sh1 to ur sheet

Sub vbaMatch()

Dim d, g, h, t
Sheets("Sh1").Activate
d = Cells(65535, 4).End(xlUp).Row
g = Cells(65535, 7).End(xlUp).Row
h = Cells(65535, 8).End(xlUp).Row
On Error Resume Next

For t = 1 To d
Range("E" & t) = Range("G" & Range("H1:H" & h).Find(Range("D" & t),
LookIn:=xlValues).Row)
Next

End Sub

" skrev:

On Apr 28, 5:12 pm, wrote:
hi everyone

I m trying to convert this worksheet formula

=INDEX($H$1:$H$499,MATCH(D1,$G$1:$G$499,0))

=INDEX(Range to Search,MATCH(Cell to Match, Range in which to search
for match, FALSE)

to macro [in sub, not function ]

no.of rows are unknown .......plz help me convert above formula to vba
code

I hop i get support from u experts as i got earlier
many thanks in advance for u time n effort

i would lik to add one more thing here that my match value "d1" in
above formula

but in macro i want to match all values in column "d" [i.e.
d1,d2....d(n)] in column g (where no.of rows are unknwon )
and no. of rows in column H [ range in which value to b srchd ] are
also unknown

waitng 4 ur replys
many thanx

hello mr excelent

thanks 4 ur time n reply

i tried but its not working, hav changed the sh1 to sheet1 but stil
program is running but no values in "e" column

any suggestions plz

try look at my sheet - maby this can help us ce what goes wrong

http://pmexcelent.dk/vbaMatch.xls


" skrev:

On Apr 28, 6:40 pm, excelent
wrote:
values writes to column E - change Sh1 to ur sheet

Sub vbaMatch()

Dim d, g, h, t
Sheets("Sh1").Activate
d = Cells(65535, 4).End(xlUp).Row
g = Cells(65535, 7).End(xlUp).Row
h = Cells(65535, 8).End(xlUp).Row
On Error Resume Next

For t = 1 To d
Range("E" & t) = Range("G" & Range("H1:H" & h).Find(Range("D" & t),
LookIn:=xlValues).Row)
Next

End Sub

" skrev:

On Apr 28, 5:12 pm, wrote:
hi everyone

I m trying to convert this worksheet formula

=INDEX($H$1:$H$499,MATCH(D1,$G$1:$G$499,0))

=INDEX(Range to Search,MATCH(Cell to Match, Range in which to search
for match, FALSE)

to macro [in sub, not function ]

no.of rows are unknown .......plz help me convert above formula to vba
code

I hop i get support from u experts as i got earlier
many thanks in advance for u time n effort

i would lik to add one more thing here that my match value "d1" in
above formula

but in macro i want to match all values in column "d" [i.e.
d1,d2....d(n)] in column g (where no.of rows are unknwon )
and no. of rows in column H [ range in which value to b srchd ] are
also unknown

waitng 4 ur replys
many thanx

hello mr excelent

thanks 4 ur time n reply

i tried but its not working, hav changed the sh1 to sheet1 but stil
program is running but no values in "e" column

any suggestions plz

hi mr. excelent
ur code worked excelnt
many many thanks 4 ur time n effort realy appreciable.........

but i made some small changes

original code
For t = 1 To d
Range("E" & t) = Range("G" & Range("H1:H" & h).Find(Range("D" & t),
LookIn:=xlValues).Row)
Next

modified one
For t = 1 To d
Range("E" & t) = Range("H" & Range("g1:g" & h).Find(Range("D" & t),
LookIn:=xlValues).Row)
Next

by mistake u placed "g" ,wher we supoose to put "h" n vice
versa..........

wil u plz spend some more time to xplain this code i mean
from .......on error resume next or u cud suggest sum site or
tutorial wher from i can learn this

thnks in advance

well i can try :-)
For t = 1 to d is the loop, where d is the last row with values in colD
Range("E" & t) is where found value is writed
Range("g1:g" & h).Find(Range("D" & t), LookIn:=xlValues).Row is retuning
row number where found value is
Range("H" & is forsing formula to take value in column H instead of
column G

sry my french - Denmark u no :-)

For t = 1 To d
Range("E" & t) = Range("H" & Range("g1:g" & h).Find(Range("D" & t),
LookIn:=xlValues).Row)
Next

by mistake u placed "g" ,wher we supoose to put "h" n vice
versa..........

wil u plz spend some more time to xplain this code i mean
from .......on error resume next or u cud suggest sum site or
tutorial wher from i can learn this

thnks in advance

thanx again

wishing u grt time ahead
b bye