Would Like To Learn VBA

[texastomass]

Hi,

Thought this would be the best place to ask. I would like to start learning VBA script, i have a very basic knowledge but want to know more. Where would be the best startingpoint to gain this understanding and once i have this what professional qualifications would you advise i look into ?

Thanks In Advance

 

[MyExcel]

thanx geek
and thanx adam
it is really great explain
i will do my best to improve my self
and i will ask always about what i can't understand
*******************************
i have another Question a bout Formula
what is the best to understand Formula
and how i can make complex Formula like this

=TRIM(LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))
****************************
=TRIM(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")))))

i am sorry for all this Question
but i need your help

 

[xenou]

A very helpful trick is using the F9 key to highlight a part of a formula, which shows you what it evaluates to. For example, if you have values 1,2,3 in A1,B1 and C1, and a formula =A1+(B1+C1), then select and highlight (B1+C1) in the formula bar, and hit F9, you will see 5, which is 2 + 3. (NOTE, hit ESCAPE to get out of this display, otherwise the conversion becomes a permanent one).

Doing the same with your "complex formula", you work inside out:

Concatenate A2 with the value "0123456789"

[COLOR="Blue"]A2&"0123456789"[/COLOR]

Find the first location of each of the values from 0 to 9 in the concatenated value above.

[COLOR="blue"]SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")[/COLOR]

Find the lowest/minimum values from the set of numbers returned above.

[COLOR="blue"]MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))[/COLOR]

Subtract 1

[COLOR="blue"]MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1[/COLOR]

Take the left characters in A2 from the first to the one at the number location above.

[COLOR="blue"]LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)[/COLOR]

Remove any leading and trailing spaces

[COLOR="blue"]TRIM(LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1))[/COLOR]

It appears this formula would return text with any numeric values stripped off off from the right side. As you can see, it is built from a number of more or less simple formulas, to achieve a fairly sophisticated goal. Another way to "decompose" these formulas is to break each step into a separate cell. In this case, you could put the search formula in Cell B2, the Min formula in Cell C2 (referencing the result in B2), the Left formula in Cell D2 (referencing the result in C2), and the Trim formula in Cell E2 (referencing the result in D2). E2 would also be the final calculation and the answer.

Hope this helps. In my opinion, using search with set of values to create a resulting set of values is not common and the formula is quite clever - someone who knows Excel very well probably created this.

 

[MyExcel]

Thanx Alexander for your advice it is very helpful
and as u said this is clever formula and a member here his name Yard
he make this formula
i hope if i can some day create formula like this formula
any way thanks again for all this help from
SydneyGeek
ajetrumpet
Alexander Barnes
you are really nice team