Convert excel formula to vba, need help???

[Jhon Mustofa]

hii all,

please give me, code macro to my formula this below :

=+IFERROR(INDEX(data!$D$2:$F$43;MATCH(1;--(data!$C$2:$C$43=$B2);0);SMALL(IF(data!$D$2:$F$43<>"";IF(data!$C$2:$C$43=$B2;COLUMN(data!$D$1:$F$1)-COLUMN(data!$D$1)+1));COUNTIF(B$2:B2;B2)));"")

for everyone, thanks you very much...

[mahaveersomani]

i think if you will raise your original query what is your need.....then we can help you easly as compare to change formula in vba code......

so what is your need in your sheet....tell us and provide us a sample sheet if possible.....


Regards
CA Mahaveer Somani

[Jhon Mustofa]

i attach my workbook..mahaveersomani..

[AB33]

In all your formula you have ";", instead of comma(,). I do not know how formula works with semi-colon..

[Jhon Mustofa]

Correct it, ";" as instead "," (comma)

[Fotis1991]

In all your formula you have ";", instead of comma(,). I do not know how formula works with semi-colon..

It's depant on the local settings.

We also in Greece use semi colons as separators. Not comma!!

[AB33]

Fortis,
Interesting!
Does it only apply to comma and semi-colon, or you could change any settings?
I am aware many Europeans countries use comma, instead of dot(Full-stop) in numbers, but not come across with formulas.

[AB33]

Sorry!
My local setting must be comma, not semi-colon, so I can not test your formula.
Fortis may be able to help.

[Fotis1991]

Fortis,
Interesting!
Does it only apply to comma and semi-colon, or you could change any settings?
I am aware many Europeans countries use comma, instead of dot(Full-stop) in numbers, but not come across with formulas.

It's also when we use format for dates.. For example the " mmm" format i have to change to "μμμ" that means Months in my local setting.. And also as about this comment of you:

.

.Sorry!
My local setting must be comma, not semi-colon, so I can not test your formula.
Fortis may be able to help.

I f i was thinking like you did then i had to reply almost in no thread.. When OP upload no sample workbook i have to replace ALL comma to his formula to semi colons if i want(and i do many times a day) to test the formula and try to help him.

ALSO: My name is Fotis. Not Fortis

[Jhon Mustofa]

no problem, may be you can do it with your setting "comma", and i try to convert semi-colon...

[AB33]

ALSO: My name is Fotis. Not Fortis
Sorrrrrrrrrrrrrrrrry!
I have always refer by the name of "Fortis", not "Fotis". I really do not how I got it. It was probably easy to remember the English word "Fortis". I bet you I am not the only who misspell you name and will not be the last one.

[Fotis1991]

Some time ago i was asking you if you have fat fingers..(R, T in the keyboard) ! Do you remember?

[AB33]

Yes, my memory still good!

The thread was regarding sheet names.

Looking back, I see what you meant, but I have to admit that I did not understand at that time. You have a taste for sense of humour.

[mahaveersomani]

i have two little confusion in your example

1. what is the sense of no. and how you got it.
2. IN your answer What ever you write for Maria is it right or wrong? I think this is wrong..it should be Item 2 Item 2 Item 3 instead of Item 2 item 3 item 4 as per your question.

Tell me both the issue so that i can make a vba for you....

Regards
CA Mahaveer Somani
9785216211

[Jhon Mustofa]

thanks for your kindness, CA Mahaveer Somani

you are correct...for Maria is it wrong, should be item2,item2 and item3 instead of item..
then, my expected result in "sheet2" start from column c row 2 (c2) drop down, and with sheet "data" as data..

i do believe you can help me with your "touch"..

i attach new workbook..

thanks again...

[Jhon Mustofa]

this, my workbook....

[Jhon Mustofa]

sorry, my file is drop behind

[mahaveersomani]

hi Jhon Mustofa,

Now i understand your query so i will make a vba code for you upto 6PM due to busy at my office..

but still i have a confusion as i asked from you...what is the meaning of No. in your sheet2 how you get that value like
0
2
2
2
0
0
0
2
2
2
0
2
2
2


Regards
CA Mahaveer Somani
9785216211

[mahaveersomani]

Please check the attached file and see the sheet2 it is blank now press the CTRL+SHIFT+T. and see what ever you were need......


I hope it was useful to you so please dont forget to hit * button for user reputation...

If you have any further query then feel free to ask me......

Regards
CA Mahaveer Somani
9785216211

[Jhon Mustofa]

brilliant answer..it's 100% worked perfectly.......

thanks you, CA Mahaveer Somani

regards,
jhon Mustofa

[mahaveersomani]

welcome dear Jhon,

nice to see you happy.
See you nex time dear.
Enjoy with your file.

Regards
CA Mahaveer Somani
9785216211