Posts by yjoshi

Hi,

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)
Next

Else
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.

Regards

Yogendra

Thanx Will,

May be next time i will send U2U to one of the moderators :thumbcoo:

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.
:cheers:

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

I tried using your formula, it is updating the formulae as well.

Can you explain your question a bit more???

If you can give a sample, it will be the best.

Hi Jack,

Quote

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.Protect
ActiveSheet.EnableSelection = xlUnlockedCells

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

Regards

Yogendra

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

http://www.decisionmodels.com/calcsecretsi.htm

This site will give almost everything you want and also the examples!!

I saw it just yesterday, and never thought anyone will ask for exactly the same thing :guitar:

Hope this works for you

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
Days_5:
=OFFSET(Sheet1!$A$1,0,0,MATCH(TODAY()-4,Only_Dates,FALSE),6)
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::
=DSUM(Days_5,B$25,Full_Data)
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.

:cheers::cheers::cheers:

I think offset formulae will be the best to use, and better way is to created named ranges with offset formulae!!!

I will try to do something more, but it will be best if you can give data!!!

Is it possible to upload it as attachment here???

if it is tooo big, you may give a link to where it is stored :coolwink:

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.

Function
use formula:
=UPPER(A1)
Using Macro

Sub upper_case()

ActiveSheet.Range("B1").Value = (UCase(ActiveSheet.Range("A1").Value))

End Sub

Note:
Please give your questions a bit more clear, it will help us to help you

:cheers:

You are most welcome Juana!

We expect you to be back with your questions and possibly answers to others in this forum, which you will certainly be able to do, after your exam.

BEST OF LUCK.

Hope to see you back on this forum.

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:

You hit right on the nail.

I had created the buttons from Forms toolbar and not the Control Toolbar.

I have amended my worksheet!!!

Thanx a million

Regards
:guitar:

Great to hear that people are taking heed of http://www.ozgrid.com/forum/viewthread.php?tid=947

which i had railsd.

It went into visious loop of replies which were not related to the subject i railse, so i had left hopes.

Good to see the same again discussed.

Great Going!!!!

Regards

Yogendra