Simply you are genius, I appreciate your kindness and your knowledge. Can you tell me one thing more that is the structure or learning style of macros because I am a developer and I don't know how to learn macros. what is the exact way of learning macros?
Again thanking you and kind regards
VBA is pretty close to basis
Dim i As Long, j As Long
For i = 1 To 10
j = Int(Rnd * 100 + 1)
if j < 70 then
MsgBox "Count is " & i & " random number is " & j
So this demonstrates using a loop and using a condition statement.
It does nothing with excel - but just demonstrates basic structure using loops and conditional constructs which are basic to most programming languages.
If I wanted to loop through the range A1:A10 and sequentially display the contents of that cell in a message box I could do
Dim rng as Range, cell as Range
set rng = ActiveWorksheet.Range("A1:A10")
for each cell in rng
cell.value = Int(rnd() * 600 + 1)
This shows creating a rng reference (a range object) to refer to a specific range of cells on the activeworksheet.
Then looping through those cells and placing a random integer in the cell.
this is a reference to John Walkenbach site. He is one of the Key authors on Excel books. He write his programming book for each version of excel and his Excel Bible and many of his other books. You would do well to buy one of his programming books say from Amazon. If you don't want to spend a lot of money, look at one that is for several excel versions ago. 95 percent of the information will be applicable even in the latest excel version. If you want to work with the ribbon, then you should get a book no later than Excel 2007 when the ribbon was introduced.
The URL is: http://www.spreadsheetpage.com/
but buying a book is not the reason I picked his site. He has excellent VBA and just Excel tips on his site which can show you great code examples for doing a lot of routine and non-routine stuff.
On the above page, look for Excel hints. Look at the entries below "What Else Is Here?"
There are tips and downloads and the letter from David Hager have some really advanced tips.
So for me, the exact way of learning macros is to look at other peoples macros when you know what the macro is supposed to do. I personally learned Excel VBA by using Reed Jacobson's introductory book. It is pretty much a tutorial - so you could look up his book. But I polished my skills helping people solve problems on the old Microsoft Excel newsgroups (no longer exist) and then I could read other people's solutions as well. Doing is the best way to learn.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here