$$Excel-Macros$$ Three "HOW TO" questions.

2009-05-22 Thread howard.lawren...@gmail.com 

Hi everyone,
Thank you for reading my questions. I look forward to your responses.

I am reading in data from a text file. For simplicity's sake, let's
say
I'm using the following code:

Sub ProgramName()
Open "filename.txt" For Input As #1
for i=1 to 10
Line Input #1, tempstring
Range("a1").offset(i-1,0).Value = tempstring
Next i
Close #1
End Sub

Question #1: Let's say that after reading in 10 lines, I decide that I
want to read them in again. In other words, I'd like the next "Line
Input #1, tempstring" command to start over at the beginning. Is there
a way to do it without closing and reopening file #1?

Question #2: (related) Is there a way to randomly access a line from a
text file? That is, let's say that I know I want the read the
millionth line from filename.txt above. Can I do it without reading in
every line before it?

Question #3: (unrelated) When I was using Excel 2000 with an XP
operating system, I had no trouble breaking out of VBA code while it
was running. Ctl-Break seemed to do the trick every time. Now, with
Excel 2007 with Vista, the Ctl-Break doesn't work as well. Sure, it
will send me into break mode if it's a very modest program (a simple
but lengthy loop), but if the code gets complicated at all, forget it.
It simply won't break. Does anybody know a what I can do?

Thank you again,
Howard

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Error Checking option.

2009-05-22 Thread Aindril De 
Dear Abdul,

Create the following UDF in a module:

Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function

Now in your conditional formatting, use the formula is option and refer to
this function. and put the formatting you want.

Only drawback in this is it will consider one cell at a time with this
formula.


Regards,
Andy

On Fri, May 22, 2009 at 9:38 PM, Abdul Hakim wrote:

>
> Dear All,
>
> I want to check the cell value via conditional formatting that whether
> the value is manual entered or formula.
>
> can any body assist me to solving this problem.
>
> Regards,
> Abdul Hakim...
>
> >
>

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Coloring sheet tab conditionnally

2009-05-22 Thread Aindril De 
Hi Can you send a sample sheet please

Regards,
Andy

On Fri, May 22, 2009 at 4:02 PM, Mehdi_21  wrote:

>
> hey everyone,
>
> how can i write a macro that colors sheet tab into "red" if:
>
> 1) sheet contains "zero" string
> 2) sheet contains "nine" string
> 3) sheet contains "nine" AND "zero" strings
> 4) sheet contains "nine" OR "zero" strings
>
> otherwise sheet tab color turns into "Green"
>
> thanks in advance for your help
> regards
>
> >
>

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: i cannot write with E , for example 11E17

2009-05-22 Thread Harmeet Singh 
please send the file
On Fri, May 22, 2009 at 1:21 AM, god is gr8 i love u
wrote:

> HI
> i want to make chart on data in excel its whole month data
> i want to show to difference in timing so which chart will b suitable and
> how cn i show the whole month data in chat
> also in this i hv 4 columns
> plz help me
> plz mail me on another id
> life_is_to_ch...@yahoo.co.in
>
> Thanx
>   On Tue, May 19, 2009 at 4:29 PM, 2008ra...@gmail.com <
> 2008ra...@gmail.com> wrote:
>
>>
>> problem is that i take my code's report from my database , i don't
>> write these codes with myself.
>> if someone want to file about that  , i can send a excel file.
>> thanks for your helps.
>>
>>
>>
>
> >
>


-- 
Thanks & Regards,

Harmeet Singh

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Error Checking option.

2009-05-22 Thread Abdul Hakim 

Dear All,

I want to check the cell value via conditional formatting that whether
the value is manual entered or formula.

can any body assist me to solving this problem.

Regards,
Abdul Hakim...

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ macro to automatically merge cells in the worksheet/workbook

2009-05-22 Thread Praveen Khunte 
Hi
Here is the macro which removes non-breaking spaces in all the cells of 
the worksheet.

Public Sub RemoveNonBreakingSpaces()

' Remove all non-breaking spaces from constants in the active worksheet.

   Dim Cell As Range
   Dim Value As String
   Dim Length As Long
  
   If ActiveSheet.Type <> xlWorksheet Then Exit Sub
  
   For Each Cell In ActiveSheet.UsedRange
  If Len(Cell) > 0 And Not Cell.HasFormula Then
 Value = Cell
 Length = Len(Value)
 Value = Replace(Value, Chr(160), vbNullString)
 If IsNumeric(Value) Then Value = Trim(Value)
 If Length <> Len(Value) Then Cell = Value
  End If
   Next Cell
  
End Sub

Best Regards
Praveen Khunte


Dave Bonallack wrote:
> Hi,
> If you don't need any merged cells in your sheet, try putting these 
> lines at the beginning of your present code:
>  
>  
> Regards - Dave.
>
>  
> 
> Date: Thu, 21 May 2009 23:11:30 +0530
> From: praveen.khu...@gmail.com
> To: excel-macros@googlegroups.com
> Subject: $$Excel-Macros$$ Need a macro to automatically merge cells in 
> the worksheet/workbook
>
> Hi all
> How's everyone doing?
> I am novice to macro, I need a help from you guys. I hope it should be 
> simple.*
> Present Scenario:* I have a macro which remove non-breaking space from 
> all the cells at once in the worksheet. (If anyone need, I can share 
> the code).
> *Usefulness of the Macro*: When you copy data from the internet and 
> paste in an excel worksheet, it contains lot of spaces before and 
> after, and when you want to perform some calculation it doesn't give 
> desired result. This macro removes spaces before and after so that 
> your calculation (life) becomes easy.*
> Problem:* This macro stops working beyond the point where the it finds 
> a merged cell. To overcome this, I need to select all the cells of the 
> worksheet and then Go to Format > Cells and  click uncheck merge cells 
> (as shown in image). This is tedious repetitive job since I have about 
> 30 worksheets.*
> Requirement: *A macro which automatically merges all the cells of the 
> worksheet (if possible-entire workbook).
>
>
>
> Warm Regards
> Praveen Khunte
>
>
> 
>
>
> 
> Let ninemsn property help Looking to move somewhere new this winter? 
> 
> >

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Hi Urgent Excel help required.

2009-05-22 Thread TAlgo 

Try this if this helps

Sub CopyPaste()
Dim ws As Worksheet
For Each ws In Worksheets

If ws.Name <> "JAN" Then
ws.Select
ws.Application.Run "Macro1"
End If
Next
End Sub

Sub Macro1()
Range("A2:B13").Select ' select all the data range
Selection.Copy
Sheets("Summary Sheet").Select
ActiveSheet.Paste
End Sub

shannu shannu wrote:
> Hi All
>  
>     I have many sheets and I want to copy in one main excel sheet but 
> I want to copy ne after the other exce sheet's data, it shd be like a ladder 
> or a staircase.
>  
> Sheets like A1, B1, C1 etc and one man sheet called "JAN".
> I want to copy all the visible rows which are present in A1, B1,C1 and paste 
> it in Jan excel one after the other.
>  
> Regards,
> Shahina

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Hi Urgent Excel help required.

2009-05-22 Thread ddadmin2009 
check the article below,  this should give you a start for what your are
looking to do
http://funwithexcel.blogspot.com/2009/05/macro-to-copy-worksheets-from-master.html

In this I have shown how to copy the whole excel sheet, you got to tweak a
bit, get the active cell and paste all sheets one by one in same worksheet.

On Fri, May 22, 2009 at 12:46 AM, shannu shannu wrote:

>Hi All
>
> I have many sheets and I want to copy in one main excel sheet
> but I want to copy ne after the other exce sheet's data, it shd be like a
> ladder or a staircase.
>
> Sheets like A1, B1, C1 etc and one man sheet called "JAN".
> I want to copy all the visible rows which are present in A1, B1,C1 and
> paste it in Jan excel one after the other.
>
> Regards,
> Shahina
>
>
>
> >
>


-- 
http://funwithexcel.blogspot.com/

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Searching a Value in Excel Sheet

2009-05-22 Thread Aindril De 
Hi Anil,

Please go thru the link below. This should answer your query..
http://www.cpearson.com/excel/lookups.htm


Regards,
Andy

On Fri, May 22, 2009 at 9:18 PM, anil kumar  wrote:

> Hi All,
>
> i have a query suppose we have a excel file which have some data like as
> below
>
>  A  B
> 1  xyz10
> 2  abc20
> 3  xyw30
> 2  abc 40
> 5  xyz  50
>
> if i am using Vlookup and hlookup function of excel it will give you the
> value as per your search.
>
>
> *NOTE :* Suppose there is no vlookup and hlookup fuction in Excel then
> what function i will use to solve my problem (Searching a data in excel
> file).
>
>
> --
> Thanks & Regards
> Anil Kumar
>9213901798
>
> >
>

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Chart Formatting in Excel 2007

2009-05-22 Thread Dave 

Any thoughts on this?  I'm able to record the procedure in Excel 2000
but when I try using that code in 2007, I get an error.


On May 21, 1:12 am, Satish Mandava  wrote:
> On Thu, May 21, 2009 at 2:41 AM, Dave  wrote:
>
> > Hi all, new to the group and fairly new to VBA programming.  Here is
> > my question:
>
> > I'm writing a Macro to automate chart formatting tasks in Excel 2007.
> > I noticed that the macro recorder does not record all of my steps and
> > I can't figure out which commands to use from the object browser so
> > hopefully somebody here can help me.
>
> > My chart is a 3D contour plot. I'm trying to specify which colors are
> > associated with certain numerical ranges in the plot and the legend.
> > Manually, I accomplish this by selecting the individual legend entry,
> > right clicking and selecting "format band" and then selecting the
> > solid fill option with my preferred color.
>
> > So far I have a hunch that the right code might go something like
> > this:
>
> > ActiveChart.Legend.LegendEntries(1).Fill.ForeColor.RGB = RGB(128, 0,
> > 0)
>
> > However, I get an error when I try to run this.  Thoughts?
> > Suggestions?  Ridicule?
>
> > Thanks,
> > Dave
>
> --
> Satish Mandava
> Financial Planning Consultanthttp://investoreducation.blogspot.com- Hide 
> quoted text -
>
> - Show quoted text -

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Searching a Value in Excel Sheet

2009-05-22 Thread anil kumar 
Hi All,

i have a query suppose we have a excel file which have some data like as
below

 A  B
1  xyz10
2  abc20
3  xyw30
2  abc 40
5  xyz  50

if i am using Vlookup and hlookup function of excel it will give you the
value as per your search.


*NOTE :* Suppose there is no vlookup and hlookup fuction in Excel then what
function i will use to solve my problem (Searching a data in excel file).


-- 
Thanks & Regards
Anil Kumar
   9213901798

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: want a macro code

2009-05-22 Thread msakibkhan 

Hi Deepak rawat !

I saw the solution you have provided is working perfectly and also
implementing immediately that's a very good job doing by a VBA
function "OFFSET" I will highly appreciate.


On May 22, 1:20 pm, kishan@gmail.com wrote:
> Hi,
>  I did this with vba, but I am unable to attache excle file. so I
> paste this code to here...
> --
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo Err1:
> Static l As Integer
> Static i As Integer
> If Target.Value = "" Then
> GoTo my
> End If
>
> If l > 0 Then
> GoTo jay
> End If
>
> l = Application.WorksheetFunction.Match(Target.Value, Sheet2.Range
> ("A1:A10"), 0)
> jay:
> i = i + 1
> Target.Offset(0, 1).Value = Sheet2.Cells(l, i + 1)
> my:
> l = 0
> i = 0
> Err1:
>
> End Sub
> --- 
> -
> only you have to paste this code on vba editor
> Alt+F11
> double click on sheet1 and paste it there it will run.
>
> Regards,
> Jai Kishan
>
> On May 20, 10:10 am, Deepak Rawat  wrote:
>
> > Hi all!i have attached a file, there is 5 fields in the file
> > my requirement is, as i put city name in city field, then all the field
> > should fill accordingly
> > like as i put Mumbai, the names n Ph nos come accordingly ie. AMD_NAME=>ANIK
> > BHASIN AND ADM_PH => 9921356982 and the same for GM name as well.
>
> > regards,
> > Deepak rawat
>
> >  Book1.xls
> > 44KViewDownload

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: Coloring sheet tab conditionnally

2009-05-22 Thread Paul Schreiner 
Since the color numbers are different for the different versions of Excel
(Excel 2007 uses some wierd shades)
You'll have to record a macro to find your color numbers.
this macro will get you started.
you need to work on the logic for what you want to do, since what you describe 
doesn't really make sense...

"how can i write a macro that colors sheet tab into "red" if:

1) sheet contains "zero" string
2) sheet contains "nine" string  "if 1) didn't turn it red, it will now
3) sheet contains "nine" AND "zero" strings 'would've been red if 1) or 2) 
anyway
4) sheet contains "nine" OR "zero" strings  'would've been red if 1) or 2) 
anyway

'-

Sub colorTab()
    Dim sh
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each sh In Worksheets
    Sheets(sh.Name).Select
    Range("A1").Select
Sheets(sh.Name).Tab.Color = 32768 'Color
'
    Err.Clear
    Cells.Find(What:="zero", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
    False, SearchFormat:=False).Activate
    If Err.Number <> 0 Then
    Sheets(sh.Name).Tab.Color = 13311 'Red
    End If
    Cells.Find(What:="nine", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, 
MatchCase:= _
    False, SearchFormat:=False).Activate
    If Err.Number <> 0 Then
    Sheets(sh.Name).Tab.Color = 13311 'Red
    End If

    Next sh
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub


Paul



From: Mehdi_21 
To: MS EXCEL AND VBA MACROS 
Sent: Friday, May 22, 2009 5:02:45 AM
Subject: $$Excel-Macros$$ Coloring sheet tab conditionnally


hey everyone,

how can i write a macro that colors sheet tab into "red" if:

1) sheet contains "zero" string
2) sheet contains "nine" string
3) sheet contains "nine" AND "zero" strings
4) sheet contains "nine" OR "zero" strings

otherwise sheet tab color turns into "Green"

thanks in advance for your help
regards


--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Hi Urgent Excel help required.

2009-05-22 Thread shannu shannu 






Hi All
 
    I have many sheets and I want to copy in one main excel sheet but I 
want to copy ne after the other exce sheet's data, it shd be like a ladder or a 
staircase.
 
Sheets like A1, B1, C1 etc and one man sheet called "JAN".
I want to copy all the visible rows which are present in A1, B1,C1 and paste it 
in Jan excel one after the other.
 
Regards,
Shahina



  
--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Coloring sheet tab conditionnally

2009-05-22 Thread Mehdi_21 

hey everyone,

how can i write a macro that colors sheet tab into "red" if:

1) sheet contains "zero" string
2) sheet contains "nine" string
3) sheet contains "nine" AND "zero" strings
4) sheet contains "nine" OR "zero" strings

otherwise sheet tab color turns into "Green"

thanks in advance for your help
regards

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---

$$Excel-Macros$$ Re: want a macro code

2009-05-22 Thread kishan . jai 

Hi,
 I did this with vba, but I am unable to attache excle file. so I
paste this code to here...
--
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err1:
Static l As Integer
Static i As Integer
If Target.Value = "" Then
GoTo my
End If

If l > 0 Then
GoTo jay
End If

l = Application.WorksheetFunction.Match(Target.Value, Sheet2.Range
("A1:A10"), 0)
jay:
i = i + 1
Target.Offset(0, 1).Value = Sheet2.Cells(l, i + 1)
my:
l = 0
i = 0
Err1:

End Sub

only you have to paste this code on vba editor
Alt+F11
double click on sheet1 and paste it there it will run.

Regards,
Jai Kishan




On May 20, 10:10 am, Deepak Rawat  wrote:
> Hi all!i have attached a file, there is 5 fields in the file
> my requirement is, as i put city name in city field, then all the field
> should fill accordingly
> like as i put Mumbai, the names n Ph nos come accordingly ie. AMD_NAME=>ANIK
> BHASIN AND ADM_PH => 9921356982 and the same for GM name as well.
>
> regards,
> Deepak rawat
>
>  Book1.xls
> 44KViewDownload

--~--~-~--~~~---~--~~
-
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at 
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
 

To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain  @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-
-~--~~~~--~~--~--~---