Macro for index/match function

[nitn28]

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

 

[nitn28]

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

 

[Guest]

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



"(e-mail address removed)" skrev:

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

 

[thankfulguy]

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

 

[nitn28]

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

"(e-mail address removed)" skrev:

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

 

[Guest]

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

http://pmexcelent.dk/vbaMatch.xls


"(e-mail address removed)" skrev:

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

"(e-mail address removed)" skrev:

On Apr 28, 5:12 pm, (e-mail address removed) wrote:
hi everyone

I m trying to convert this worksheet formula

=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

 

[nitn28]

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

 

[Guest]

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

 

[nitn28]

thanx again

wishing u grt time ahead
b bye

 

[sunil.get123]

Thank you so much for the great help. Please help me in having the vb code for excel formula =INDEX($H$1:$H$499,MATCH(D1,$G$1:$G$499,0),MATCH(E1,$H$1:$H$499,0))

Thanks a lot for supporting me.