Posts by yjoshi


    This is a different approach.

    A bit more user-friendly(with messages), also takes care of the requirement that this needs to be tere only for a few cells!

    Worked fine for me, hope it works for you!
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim x
    Dim i As Integer
    If Intersect(Target, Range("a1:a10")) Is Nothing Then Exit Sub

    x = InputBox("Enter a valid 8 digit number!!", "Telephone Entry")
    If (IsNumeric(x)) And Len(CStr(x)) = 8 Then
    i = 0
    For i = 0 To 8
    Target.Offset(0, i) = Mid(CStr(x), i + 1, 1)

    MsgBox ("Please enter a valid 8 digit number")
    End If

    End Sub

    Is there any place (site!!) where i can get an online help for VBA so as not to be dependent on the inbuilt help!!

    (The reality struck me very hard today when my EXCEL-VBA help files got corrupted)

    Also if anyone can tell me any place from where i can download it, it will be great as well.



    Hi tinka1

    Can you please change the original subject to something like :
    Creating formulae Dynamic ranges

    (Click on Edit in your main post)

    This will help others to use this when required and even you may find some better ways of doing this than suggested mine.

    I hope you won't mind this.

    P.S. : It will be great if you can put : SOLVED in the main text when you are fully satisfied.

    Hi Jack,


    Re : The next question I have is how do I make the cursor just go to unlocked cells?

    This code in the workbook_open event will do the trick...

    ActiveSheet.EnableSelection = xlUnlockedCells

    P.S. I was not sure whether you are asking for this, but read the question and gave the answer:tongue:



    Its pretty easy,

    You have to just open Excel
    - File -> Open
    - File Type : Select : Lotus 1-2-3 Files..
    - Select the file you want to convers
    - Save as - Type : MS Excel

    Hope this helps

    Here is the formula.

    Paste this formula in B2 (say A2 has the number you want to search, in the book1(Sheet3 (2)), the FALSE will find an exact match!!!

    =MATCH(A2,'[Book1]Sheet3 (2)'!A1:A10,FALSE)

    You will need to change this a bit, but this will give you a head-start:cheers:

    I have created a sample data for you,
    see if this helps.

    Created 3 Dynamic Ranges
    What it does???
    -This formula stats from the first cell
    -matches today's date - 4 (to get 5 days including today!!)
    -Selects a range starting from first cell, rows = the figure of match we derived above and cols = 6

    Same thing applies for Days_10 and Days_15

    Now in the formulae::
    Sum of column say "A" for last 5 days::
    What it does???
    This formuma takes sum of Data_5 we generated above for column "A" (B$25 = "A" which is in summary) with criteria of full_data.

    I have done a quick QA.

    Let me know if you want anything more.
    Also see the Sheet attached.


    Hi troppojohn,

    Also,see if you are using any function / procedure stored in your PERSONAL.XLS
    If yes, just copy these into your friend's PERSONAL.XLS and it will work.

    Even after the Addins and PERSONAL.XLS inquiry, you cannot find it, please post a sample of the macro/sheet here, so that we can look into it to find a problem area.

    My pleasure Bill,

    Still, Shady's question still remains pending!!!

    Automatic shifting of selection to right on entering single number in a cell ????

    I am leaving for the day, hopefully the experts here will be able to solve this.

    In the meanwhile, i will read a few books i have at home, and try to update, if i find something.

    Best Luck

    The solution you are looking for is quite interesting.

    I believe, you want to just keep typing numbers and on each entry, the single number should be entered in one cell and selection to be moved to next cell!!!

    The reason I believe this is tough, is that,
    this will require checking each keystroke, also validating whether the user has entered all number keys and then proceed. This can be a bit slow as well!!

    I am not sure about your data requirement, but see if this helps.
    1. Assign only one one cell (column) for number entry
    2. enter Validation as : text length = 8
    3. In the next 8 columns, split the number using mid function.

    Can this work?????

    I have attached a sheet for this as well.:flower: