RE: $$Excel-Macros$$ prefix 0 problem

[Asa Rossoff]

Hello Shankar,

You can use Format() in VBA.

txttotser.Text = Format(txttotyrs.Value, "00") & Format(txttotmths.Value,
"00") & Format(txttotdays.Value, "00")

 

To use Application.Worksheetfunction.Text, it would go like:

txttotser.Text = Application.WorksheetFunction.Text(txttotyrs.Value, "00") &
Application.WorksheetFunction.Text(txttotmths.Value, "00") &
Application.WorksheetFunction.Text(txttotdays.Value, "00")

 

Also your calculator form left the year textbox blank when using the test
dates you suggested.  There should be a number in that box for the above
formula to generate zeroes for the year.  If you want to pad a string with
zeroes no matter if it's empty, a number, or something else; you could, like
this:

txttotser.Text = Right("00" & txttotyrs.Value, 2)

etc.

 

One more thing - I didn't study how you are calculating your date
difference, but VBA has a function called DateDiff for that purpose.  Check
VBA Help for more info.

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Shankar Bheema
Sent: Sunday, January 29, 2012 10:02 PM
To: excel-macros@googlegroups.com; Sam Mathai Chacko
Subject: $$Excel-Macros$$ prefix 0 problem

 

Dear Experts

In the above attached file, there exists a worksheet containing total
service cell.  It takes the value from the userform data and saves on it.
when a single digit value comes in years or months or days textbox, it
should show prefixing 0.  

application.worksheetfunction.text(arg1,arg2),"interval as string"

is not working and throwing error.  Pls clarify.

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Hours calculate highlighted colum

[yasir_shahjee]

thanks EVERY BUDDY.

On Sun, Jan 29, 2012 at 10:32 PM, NOORAIN ANSARI
wrote:

> See attached sheet.
>
> On Sat, Jan 28, 2012 at 12:42 PM, yasir_shahjee 
> wrote:
>
>> Respected Masters,
>>
>> Kindly check attached sheet. i need Time calculate in Hours. hopefully
>> its really easier for Masters.
>>
>>
>> thanks
>>
>> --
>> Yasir Waheed
>> Factory Manager
>> Sinopak Extrusion Technologies (Pvt.) Ltd.
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Yasir Waheed
Factory Manager
Sinopak Extrusion Technologies (Pvt.) Ltd.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

[Asa Rossoff]

Hi Darwin,

Your "ElseIf Not." lines are redundant, you can simply say "Else" for those
lines, since that is what the ElseIf line already does.

 

In the line

.Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r), "X")
- 1))

If the cell at .Range("E" & r) does not contain an "X", then Instr() will
return 0, and then you subtract 1 from that, and ask for -1 characters using
Left().  That is most likely the problem.

 

I also highly recommend putting OPTION EXPLICIT at the top of every module
and declaring all variables that you use with Dim, Static, etc. as
appropriate.  Otherwise it can make debugging much more difficult if you
have a small typo in a variable name, as well as create other surprising
problems caused by using variant type variables (the default) for
everything.

 

You switch between using Cells() and Range() for your references.  In a case
like this where both seem to be being used interchangably, I recommend
sticking to one for ease of code comprehension, debugging, and code speed.
I would use Cells in this case as there is less work for Excel to do to
interpret it and it is probably a little faster.

 

There is almost never any need to reference the VBA library name in your
code.  You can, for example, just use Left(string, length) -- no need for
VBA.Left(.).

 

All the best!

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Darwin Chan
Sent: Monday, January 30, 2012 12:11 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

 

Dear all,

 

I have composed some code for this, however, there is error happened, can
anyone suggest to solve? Highlight in yellow has problem.

 

Private Sub CommandButton2_Click()

'Inserting columns and fill with strings, I basically get the idea from our
group

With Sheets("CHS EDI")

.Cells(1, 2).Value = "E/F"

.Cells(1, 7).Value = "Unit Prefix"

.Cells(1, 8).Value = "Unit No"

.Cells(1, 9).Value = "Estimate Date"

.Cells(1, 10).Value = "Component Code"

.Cells(1, 11).Value = "Location Code"

.Cells(1, 12).Value = "Damage Code"

.Cells(1, 13).Value = "Repair Code"

.Cells(1, 14).Value = "Length"

.Cells(1, 15).Value = "Width"

.Cells(1, 16).Value = "Num"

 

For r = 2 To .Cells(Rows.Count, "C").End(xlUp).Row

.Cells(r, 7).Value = VBA.Left(.Range("A" & r), 4)

.Cells(r, 8).Value = VBA.Mid(.Range("A" & r), 5, 7)

.Cells(r, 9).Value = VBA.Format(.Range("C" & r), "dd/mm/")

.Cells(r, 10).Value = VBA.Left(.Range("D" & r), 3)

.Cells(r, 11).Value = VBA.Mid(.Range("D" & r), 5, 4)

.Cells(r, 12).Value = VBA.Mid(.Range("D" & r), 10, 2)

.Cells(r, 13).Value = VBA.Mid(.Range("D" & r), 13, 2)

 

If IsEmpty(.Cells(r, 5)) Then

.Cells(r, 14).Value = ""

ElseIf Not IsEmpty(.Cells(r, 5).Value) Then

.Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r), "X")
- 1))

End If

 

If IsEmpty(.Cells(r, 5).Value) Then

.Cells(r, 15).Value = ""

ElseIf Not IsEmpty(.Cells(r, 5).Value) Then

.Cells(r, 15).Value = VBA.Mid(.Range("E" & r), (InStr(.Range("E" & r), "X")
+ 1), 3)

End If

 

.Cells(r, 16).Value = Cells(r, 6)

Next r

.UsedRange.Columns.AutoFit

End With

 

End Sub

 

2012/1/30 Darwin Chan 

Dear group,

 

I would like to get the value from 1 cell and split the value to 2 cells.
Any VBA formula can help achieve this?

 

Code starts from Private Sub CommandButton2_Click()

 

Criteria: 

Depends on the position of "X".

If no value in original cell, just leave it blank.

The value before "X" is length, the value after "X" is width.


 

Attached can find sample for reference.

 

-- 
Darwin Chan

darwin.chankaw...@gmail.com

kw42c...@yahoo.com.hk

 





 

-- 
Darwin Chan

darwin.chankaw...@gmail.com

kw42c...@yahoo.com.hk

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding b

Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

[Darwin Chan]

Hi Asa,

First thank you very much for suggesting why there is an error. As a newbie
in VBA programming, I really dont know much about OPTION EXPLICIT, only to
the extent it requires us to define variables in every procedure.

When i put OPTION EXPLICIT in original code, I find there is error showing
internal procedure.

After revising the "ElseIf Not..." I use the following code.however,
the problem is only solved by "On error resume Next" only, any other way
out?

Private Sub CommandButton2_Click()
'Inserting columns and fill with strings, I basically get the idea from our
group

With Sheets("CHS EDI")
.Cells(1, 2).Value = "E/F"
.Cells(1, 7).Value = "Unit Prefix"
.Cells(1, 8).Value = "Unit No"
.Cells(1, 9).Value = "Estimate Date"
.Cells(1, 10).Value = "Component Code"
.Cells(1, 11).Value = "Location Code"
.Cells(1, 12).Value = "Damage Code"
.Cells(1, 13).Value = "Repair Code"
.Cells(1, 14).Value = "Length"
.Cells(1, 15).Value = "Width"
.Cells(1, 16).Value = "Num"

For r = 2 To .Cells(Rows.Count, "C").End(xlUp).Row
.Cells(r, 7).Value = VBA.Left(.Range("A" & r), 4)
.Cells(r, 8).Value = VBA.Mid(.Range("A" & r), 5, 7)
.Cells(r, 9).Value = VBA.Format(.Range("C" & r), "dd/mm/")
.Cells(r, 10).Value = VBA.Left(.Range("D" & r), 3)
.Cells(r, 11).Value = VBA.Mid(.Range("D" & r), 5, 4)
.Cells(r, 12).Value = VBA.Mid(.Range("D" & r), 10, 2)
.Cells(r, 13).Value = VBA.Mid(.Range("D" & r), 13, 2)

On Error Resume Next
If IsEmpty(.Cells(r, 5)) Then
.Cells(r, 14).Value = ""
Else
.Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r),
"X") - 1))
End If

If IsEmpty(.Cells(r, 5).Value) Then
.Cells(r, 15).Value = ""
Else
.Cells(r, 15).Value = VBA.Mid(.Range("E" & r), (InStr(.Range("E" & r), "X")
+ 1), 3)
End If

.Cells(r, 16).Value = Cells(r, 6)

Next r

.UsedRange.Columns.AutoFit
End With

End Sub

2012/1/30 Asa Rossoff 

> Hi Darwin,
>
> Your "ElseIf Not…" lines are redundant, you can simply say "Else" for
> those lines, since that is what the ElseIf line already does.
>
> ** **
>
> In the line
>
> .Cells(r, 14).Value = VBA.Left(.Range("E" & r), (*InStr(.Range("E" & r),
> "X") - 1*))
>
> If the cell at .Range("E" & r) does not contain an "X", then Instr() will
> return 0, and then you subtract 1 from that, and ask for -1 characters
> using Left().  That is most likely the problem.
>
> ** **
>
> I also highly recommend putting OPTION EXPLICIT at the top of every module
> and declaring all variables that you use with Dim, Static, etc. as
> appropriate.  Otherwise it can make debugging much more difficult if you
> have a small typo in a variable name, as well as create other surprising
> problems caused by using variant type variables (the default) for
> everything.
>
> ** **
>
> You switch between using Cells() and Range() for your references.  In a
> case like this where both seem to be being used interchangably, I recommend
> sticking to one for ease of code comprehension, debugging, and code speed.
> I would use Cells in this case as there is less work for Excel to do to
> interpret it and it is probably a little faster.
>
> ** **
>
> There is almost never any need to reference the VBA library name in your
> code.  You can, for example, just use Left(string, length) -- no need for
> VBA.Left(…).
>
> ** **
>
> All the best!
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Darwin Chan
> *Sent:* Monday, January 30, 2012 12:11 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2
> cells
>
> ** **
>
> Dear all,
>
> ** **
>
> I have composed some code for this, however, there is error happened, can
> anyone suggest to solve? Highlight in yellow has problem.
>
> ** **
>
> Private Sub CommandButton2_Click()
>
> 'Inserting columns and fill with strings, I basically get the idea from
> our group
>
> With Sheets("CHS EDI")
>
> .Cells(1, 2).Value = "E/F"
>
> .Cells(1, 7).Value = "Unit Prefix"
>
> .Cells(1, 8).Value = "Unit No"
>
> .Cells(1, 9).Value = "Estimate Date"
>
> .Cells(1, 10).Value = "Component Code"
>
> .Cells(1, 11).Value = "Location Code"
>
> .Cells(1, 12).Value = "Damage Code"
>
> .Cells(1, 13).Value = "Repair Code"
>
> .Cells(1, 14).Value = "Length"
>
> .Cells(1, 15).Value = "Width"
>
> .Cells(1, 16).Value = "Num"
>
> ** **
>
> For r = 2 To .Cells(Rows.Count, "C").End(xlUp).Row
>
> .Cells(r, 7).Value = VBA.Left(.Range("A" & r), 4)
>
> .Cells(r, 8).Value = VBA.Mid(.Range("A" & r), 5, 7)
>
> .Cells(r, 9).Value = VBA.Format(.Range("C" & r), "dd/mm/")
>
> .Cells(r, 10).Value = VBA.Left(.Range("D" & r), 3)
>
> .Cells(r, 11).Value = VBA.Mid(.Range("D" & r), 5, 4)
>
> .Cells(r, 12).Value = VBA.Mid(.Range("D" & r), 10, 2)
>
> .Cells(r, 13).Value = VBA.Mid(.Range("D" & r), 13, 2)
>
> ** **
>
> *If IsEmpty

Re: $$Excel-Macros$$ IS.xls

[PrIyAnKa]

Still your problem is not being understand

I m not getting exactly what u want

On Mon, Jan 30, 2012 at 1:31 PM, Mohammed Muneer  wrote:

> **
> plz, check I write clearly now. I was little busy doing something other
> than this.
>
> I need the same in formula.
>
>
>
> Regards,
> Muneer,
> CC...
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ prefix 0 problem

[Shankar Bheema]

Thank you asa.

On Mon, Jan 30, 2012 at 1:40 PM, Asa Rossoff  wrote:

> Hello Shankar,
>
> You can use Format() in VBA.
>
> txttotser.Text = Format(txttotyrs.Value, "00") & Format(txttotmths.Value,
> "00") & Format(txttotdays.Value, "00")
>
> ** **
>
> To use Application.Worksheetfunction.Text, it would go like:
>
> txttotser.Text = Application.WorksheetFunction.Text(txttotyrs.Value, "00")
> & Application.WorksheetFunction.Text(txttotmths.Value, "00") &
> Application.WorksheetFunction.Text(txttotdays.Value, "00")
>
> ** **
>
> Also your calculator form left the year textbox blank when using the test
> dates you suggested.  There should be a number in that box for the above
> formula to generate zeroes for the year.  If you want to pad a string with
> zeroes no matter if it's empty, a number, or something else; you could,
> like this:
>
> txttotser.Text = Right("00" & txttotyrs.Value, 2)
>
> etc.
>
> ** **
>
> One more thing - I didn't study how you are calculating your date
> difference, but VBA has a function called DateDiff for that purpose.  Check
> VBA Help for more info.
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Shankar Bheema
> *Sent:* Sunday, January 29, 2012 10:02 PM
> *To:* excel-macros@googlegroups.com; Sam Mathai Chacko
> *Subject:* $$Excel-Macros$$ prefix 0 problem
>
> ** **
>
> Dear Experts
>
> In the above attached file, there exists a worksheet containing total
> service cell.  It takes the value from the userform data and saves on it.
> when a single digit value comes in years or months or days textbox, it
> should show prefixing 0.
>
> application.worksheetfunction.text(arg1,arg2),"interval as string"
>
> is not working and throwing error.  Pls clarify.
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

[Paul Schreiner]

First of all, I'll explain Option Explicit.
In ANY programming language, you have the ability to define variables to 
represent values.
In this case, you're using "r" to increment the Row number.

Now, in all programming, the variables have to be "Declared".
that is, you have to tell the language Compiler what type of variable it is.
text string, integer number, floating point number (Boolean (true/false))

In VBA, the compiler "allows" the variable to be "Declared" the first time it 
is 
used.
Sometimes, that's not a good thing.
Let's say that the values in a list of cells is being copied to a variable.
the values are supposed to be numbers which are then used in calculations.
The first value is read, but since it's the column heading, it is text, so the 
variable is declared as a String.
then, when the next value is read, it is stored in a String variable, which 
doesn't work well in calculations.

Also.. let's say that the variable you used is for the row number is: "rNumber".
But in one instance, you accidently type it as: "rNubmer"
The first time the code uses "rNubmer" it will automatically Declare is, and 
the 
code continues with unexpected results.

So, the best course of action is to ALWAYS declare the variable, just so you 
can 
control the TYPE of variable.
Using Option Explicit tells the compiler that ALL variables must be declared.  
So if it encounters a variable that has not been declared, it assumes that it 
is 
an error (which, if you simply failed to declare it is really more of an 
"oversight").

---

Next.
The problem is with:
If IsEmpty(.Cells(r, 5)) Then
.Cells(r, 14).Value = ""
Else
.Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r), "X") - 
1))
End If

In your sample file, the values in column "E" are NOT blank (empty)
they have a space in them.
So, the script "assumes" that if it is not blank, then it MUST have a "X" in 
the 
string.
The error occurs when the Instr() function runs, finds no "X", returns 0.
then, the Left() command results in:
Left (" ",-1) which causes an error.

I'd suggest using:

    If (InStr(.Cells(R, "E").Value, "X") > 0) Then
    .Cells(R, "N").Value = Left(.Cells(R, "E").Value, (InStr(.Cells(R, 
"E").Value, "X") - 1))
    Else
    .Cells(R, "N").Value = ""
    End If

 
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-





From: Darwin Chan 
To: excel-macros@googlegroups.com
Sent: Mon, January 30, 2012 4:25:57 AM
Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

Hi Asa, 

First thank you very much for suggesting why there is an error. As a newbie in 
VBA programming, I really dont know much about OPTION EXPLICIT, only to the 
extent it requires us to define variables in every procedure.

When i put OPTION EXPLICIT in original code, I find there is error showing 
internal procedure.

After revising the "ElseIf Not..." I use the following code.however, the 
problem is only solved by "On error resume Next" only, any other way out?

Private Sub CommandButton2_Click()
'Inserting columns and fill with strings, I basically get the idea from our 
group

With Sheets("CHS EDI")
.Cells(1, 2).Value = "E/F"
.Cells(1, 7).Value = "Unit Prefix"
.Cells(1, 8).Value = "Unit No"
.Cells(1, 9).Value = "Estimate Date"
.Cells(1, 10).Value = "Component Code"
.Cells(1, 11).Value = "Location Code"
.Cells(1, 12).Value = "Damage Code"
.Cells(1, 13).Value = "Repair Code"
.Cells(1, 14).Value = "Length"
.Cells(1, 15).Value = "Width"
.Cells(1, 16).Value = "Num"

For r = 2 To .Cells(Rows.Count, "C").End(xlUp).Row
.Cells(r, 7).Value = VBA.Left(.Range("A" & r), 4)
.Cells(r, 8).Value = VBA.Mid(.Range("A" & r), 5, 7)
.Cells(r, 9).Value = VBA.Format(.Range("C" & r), "dd/mm/")
.Cells(r, 10).Value = VBA.Left(.Range("D" & r), 3)
.Cells(r, 11).Value = VBA.Mid(.Range("D" & r), 5, 4)
.Cells(r, 12).Value = VBA.Mid(.Range("D" & r), 10, 2)
.Cells(r, 13).Value = VBA.Mid(.Range("D" & r), 13, 2)

On Error Resume Next
If IsEmpty(.Cells(r, 5)) Then
.Cells(r, 14).Value = ""
Else
.Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r), "X") - 
1))
End If

If IsEmpty(.Cells(r, 5).Value) Then
.Cells(r, 15).Value = ""
Else
.Cells(r, 15).Value = VBA.Mid(.Range("E" & r), (InStr(.Range("E" & r), "X") + 
1), 3)
End If

.Cells(r, 16).Value = Cells(r, 6)

Next r

.UsedRange.Columns.AutoFit
End With

End Sub

2012/1/30 Asa Rossoff 

Hi Darwin,
>Your "ElseIf Not…" lines are redundant, you can simply say "Else" for those 
>lines, since that is what the ElseIf line already does.
> 
>In the line
>.Cells(r, 14).Value = VBA.Left(.Range("E" & 

$$Excel-Macros$$ Data to dynamic array---implicit string to interger/long conversion?

[bpascal123]

Hi,

Is there a difference between these 2 methods of assignation  :

Range method :

Dim MyArray() as long, rCnt As Long, cCnt As Integer

With Sheet1.Range("A1:C5")
ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
MyArray = Sheet1.Range("A1:C5")
End With

Loop method :

with Sheet1.Range("A1:C5")
   redim MyArray(1 to .Rows.Count, 1 To .Columns.Count)
   for i = 1 to .Rows.Count
  for j = 1 to .Columns.Count
 MyArray(i-1,j-1) = .Cells(i,j) ''' I'm not even sure if
it's .Cells(i,j) is the right syntax...the question is not really
about the syntax
  next j
   next i

Are the 2 methods equivalent?

If the range has numbers stored as text along number stored as number,
is everything going to be cast or converted as Long in MyArray. To me
it would be an implicit casting since it relies only on the fact that
MyArray is declared as Long...

Wouldn't it be wiser to use Clong or Cint in the allocation line?

Pascal

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ IS.xls

[dguillett1]

   =B3*($F$6/100)  

and copy down

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Mohammed Muneer 
Sent: Monday, January 30, 2012 2:01 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ IS.xls

plz, check I write clearly now. I was little busy doing something other than 
this.

I need the same in formula.



Regards,
Muneer,
CC...
-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

[dguillett1]

oops. change o1 to p1

Application.DisplayAlerts = True
Columns("f").Copy Range("p1")

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Darwin Chan 
Sent: Sunday, January 29, 2012 11:03 PM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

Dear group, 

I would like to get the value from 1 cell and split the value to 2 cells. Any 
VBA formula can help achieve this?

Code starts from Private Sub CommandButton2_Click()


Criteria: 
Depends on the position of "X".
If no value in original cell, just leave it blank.
The value before "X" is length, the value after "X" is width.


Attached can find sample for reference.

-- 
Darwin Chan 
darwin.chankaw...@gmail.com
kw42c...@yahoo.com.hk

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

[Darwin Chan]

Hi Paul, Thanks a lot for your explanation first on how to use option
explicit. Let me try the code when I m back to office!!

2012/1/30 Paul Schreiner 

> First of all, I'll explain Option Explicit.
> In ANY programming language, you have the ability to define variables to
> represent values.
> In this case, you're using "r" to increment the Row number.
>
> Now, in all programming, the variables have to be "Declared".
> that is, you have to tell the language Compiler what type of variable it
> is.
> text string, integer number, floating point number (Boolean (true/false))
>
> In VBA, the compiler "allows" the variable to be "Declared" the first time
> it is used.
> Sometimes, that's not a good thing.
> Let's say that the values in a list of cells is being copied to a variable.
> the values are supposed to be numbers which are then used in calculations.
> The first value is read, but since it's the column heading, it is text, so
> the variable is declared as a String.
> then, when the next value is read, it is stored in a String variable,
> which doesn't work well in calculations.
>
> Also.. let's say that the variable you used is for the row number
> is: "rNumber".
> But in one instance, you accidently type it as: "rNubmer"
> The first time the code uses "rNubmer" it will automatically Declare is,
> and the code continues with unexpected results.
>
> So, the best course of action is to ALWAYS declare the variable, just so
> you can control the TYPE of variable.
> Using Option Explicit tells the compiler that ALL variables must be
> declared.  So if it encounters a variable that has not been declared, it
> assumes that it is an error (which, if you simply failed to declare it is
> really more of an "oversight").
>
>
> ---
> Next.
> The problem is with:
>  If IsEmpty(.Cells(r, 5)) Then
> .Cells(r, 14).Value = ""
> Else
> .Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r),
> "X") - 1))
> End If
>
> In your sample file, the values in column "E" are NOT blank (empty)
> they have a space in them.
> So, the script "assumes" that if it is not blank, then it MUST have a "X"
> in the string.
> The error occurs when the Instr() function runs, finds no "X", returns 0.
> then, the Left() command results in:
> Left (" ",-1) which causes an error.
>
> I'd suggest using:
>
> If (InStr(.Cells(R, "E").Value, "X") > 0) Then
> .Cells(R, "N").Value = Left(.Cells(R, "E").Value,
> (InStr(.Cells(R, "E").Value, "X") - 1))
> Else
> .Cells(R, "N").Value = ""
> End If
>
>
>
> *Paul*
>
> -
> *“Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> *-
>
>
>  --
> *From:* Darwin Chan 
> *To:* excel-macros@googlegroups.com
> *Sent:* Mon, January 30, 2012 4:25:57 AM
>
> *Subject:* Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2
> cells
>
> Hi Asa,
>
> First thank you very much for suggesting why there is an error. As
> a newbie in VBA programming, I really dont know much about OPTION EXPLICIT,
> only to the extent it requires us to define variables in every procedure.
>
> When i put OPTION EXPLICIT in original code, I find there is error showing
> internal procedure.
>
> After revising the "ElseIf Not..." I use the following code.however,
> the problem is only solved by "On error resume Next" only, any other way
> out?
> 
>  Private Sub CommandButton2_Click()
> 'Inserting columns and fill with strings, I basically get the idea from
> our group
>
> With Sheets("CHS EDI")
> .Cells(1, 2).Value = "E/F"
> .Cells(1, 7).Value = "Unit Prefix"
> .Cells(1, 8).Value = "Unit No"
> .Cells(1, 9).Value = "Estimate Date"
> .Cells(1, 10).Value = "Component Code"
> .Cells(1, 11).Value = "Location Code"
> .Cells(1, 12).Value = "Damage Code"
> .Cells(1, 13).Value = "Repair Code"
> .Cells(1, 14).Value = "Length"
> .Cells(1, 15).Value = "Width"
> .Cells(1, 16).Value = "Num"
>
> For r = 2 To .Cells(Rows.Count, "C").End(xlUp).Row
> .Cells(r, 7).Value = VBA.Left(.Range("A" & r), 4)
> .Cells(r, 8).Value = VBA.Mid(.Range("A" & r), 5, 7)
> .Cells(r, 9).Value = VBA.Format(.Range("C" & r), "dd/mm/")
> .Cells(r, 10).Value = VBA.Left(.Range("D" & r), 3)
> .Cells(r, 11).Value = VBA.Mid(.Range("D" & r), 5, 4)
> .Cells(r, 12).Value = VBA.Mid(.Range("D" & r), 10, 2)
> .Cells(r, 13).Value = VBA.Mid(.Range("D" & r), 13, 2)
>
> On Error Resume Next
> If IsEmpty(.Cells(r, 5)) Then
> .Cells(r, 14).Value = ""
> Else
> .Cells(r, 14).Value = VBA.Left(.Range("E" & r), (InStr(.Range("E" & r),
> "X") - 1))
> End If
>
> If IsEmpty(.Cells(r, 5).Value) Then
> .Cells(r, 15).Value = ""
> Else
> .Cells(r, 15).Value = VBA

Re: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like it

[dguillett1]

It appears that this will, indeed, be very useful. Thanks from Texas


Don Guillett
SalesAid Software
dguille...@gmail.com

From: NOORAIN ANSARI 
Sent: Monday, January 30, 2012 9:39 AM
To: excel-macros@googlegroups.com 
Subject: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like 
it

Dear Group,

Find attached the Excel add-ins, which simply fantastic in our routine work.
Just open the attachment in excel file and you can see there is an additional 
menu appears to your excel tab “My Menu” 
develop by one of our Group member Mr. Ashish Koul 

You can see almost all the functions we use are available in the menu

http://www.excelvbamacros.com/2012/01/my-menu-functions-and-help.html 


-- 





-- 

Thanks & regards,
Noorain Ansari
http://excelmacroworld.blogspot.com/
http://noorain-ansari.blogspot.com/

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Import data

[Rajan_Verma]

Try this:

Sub Compile()

On Error GoTo Err_Clear:

Application.ScreenUpdating = False

Application.DisplayAlerts = False

Dim Fso As New Scripting.FileSystemObject

Dim Path As String

 

Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to
Pick Downloaded Bills"

Application.FileDialog(msoFileDialogFolderPicker).Show

Path = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) &
"\"

If Path = "" Then Exit Sub

 

Application.FileDialog(msoFileDialogFolderPicker).Title = "Select Folder to
Save Compiled File"

CompilePath = Application.FileDialog(msoFileDialogFolderPicker).Show

compiledPath =
Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"

If compiledPath = "" Then Exit Sub

   

   

Dim Counter

Dim File As File

Dim FOlder As FOlder

Dim wb As Workbook

Dim ws As Worksheet

Dim AcWb As Workbook

Set AcWb = ActiveWorkbook

 ActiveWorkbook.Sheets.Add

 ActiveSheet.Name = "Index"

 

Set FOlder = Fso.GetFolder(Path)

 

For Each File In FOlder.Files

 Counter = Counter + 1

 Set wb = Workbooks.Open(Path & File.Name)

   

If Application.Ready = True Then



 wb.Sheets("Index").Activate

 ActiveSheet.UsedRange.Copy

  AcWb.Sheets("Index").Activate

  Range("A100").End(xlUp).Select

  ActiveSheet.Paste

 Application.CutCopyMode = False

 wb.Close

End If

 

Next

If Counter > 0 Then

AcWb.SaveAs compiledPath & "Compiled", xlExcel12

AcWb.Close

End If

Err_Clear:

Err.Clear

Resume Next

 

Application.DisplayAlerts = True

Application.ScreenUpdating = True

If Counter < 1 Then

MsgBox "No File Found For Compile", vbInformation

Else

MsgBox Counter & " File Has been Compiled, Please Find your File at"
& vbCrLf & compiledPath, vbInformation

End If

   

End Sub

 

http://excelpoweruser.blogspot.com/search?q=workbook

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Jan/Wed/2012 11:12
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Import data

 

Dear Selva,

Can you share a sample with Excel Macro Group.

On Wed, Jan 25, 2012 at 10:53 AM, LearnExcel  wrote:

Hi team, i wanted to import data set from two files and consolidate
the data into a master file.

I need something in VBA, currently i run a marco that goes through
each sheet and copies them into the marco but for some reason it takes
some time im sure theres a better way..

let me know if you need more info

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com




-- 

Thanks & regards,

Noorain Ansari

  http://excelmacroworld.blogspot.com/

  http://noorain-ansari.blogspot.com/

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Nee

RE: $$Excel-Macros$$ Import data

[Rajan_Verma]

=INDIRECT("'" & A2&"'!E" &MAX(IF(LEN('dia 28 en8 bgt'!A:J)>0,ROW('dia 28 en8
bgt'!A:J with CSE

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Jan/Wed/2012 11:12
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Import data

 

Dear Selva,

Can you share a sample with Excel Macro Group.

On Wed, Jan 25, 2012 at 10:53 AM, LearnExcel  wrote:

Hi team, i wanted to import data set from two files and consolidate
the data into a master file.

I need something in VBA, currently i run a marco that goes through
each sheet and copies them into the marco but for some reason it takes
some time im sure theres a better way..

let me know if you need more info

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com




-- 

Thanks & regards,

Noorain Ansari

  http://excelmacroworld.blogspot.com/

  http://noorain-ansari.blogspot.com/

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Import data

[dguillett1]

Indirect only works with external sheets that are OPEN. So, if you want to  use 
formulas for this I suggest using edit replace to change the formula as needed.

Don Guillett
SalesAid Software
dguille...@gmail.com

From: Rajan_Verma 
Sent: Monday, January 30, 2012 10:31 AM
To: excel-macros@googlegroups.com 
Subject: RE: $$Excel-Macros$$ Import data

=INDIRECT("'" & A2&"'!E" &MAX(IF(LEN('dia 28 en8 bgt'!A:J)>0,ROW('dia 28 en8 
bgt'!A:J with CSE

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On 
Behalf Of NOORAIN ANSARI
Sent: Jan/Wed/2012 11:12
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Import data

 

Dear Selva,

Can you share a sample with Excel Macro Group.

On Wed, Jan 25, 2012 at 10:53 AM, LearnExcel  wrote:

Hi team, i wanted to import data set from two files and consolidate
the data into a master file.

I need something in VBA, currently i run a marco that goes through
each sheet and copies them into the marco but for some reason it takes
some time im sure theres a better way..

let me know if you need more info

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited.

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com




-- 

Thanks & regards,

Noorain Ansari

http://excelmacroworld.blogspot.com/

http://noorain-ansari.blogspot.com/

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.
 
--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Data to dynamic array---implicit string to interger/long conversion?

[Sam Mathai Chacko]

First of all, your first method has very less likelihood of working. In
other words, you cannot pass a range of values once you have defined the
dimensions of the array.

If you had defined the variable as a variant, it would have worked.

So basically,

Dim MyArray as Variant

With Sheet1.Range("A1:C5")
   MyArray = Sheet1.Range("A1:C5").Value2
End With

would have worked.

Secondly,

You cannot use a i -1 within your array if you already initialize it with 1
to .Rows.Count, or 1 to .Columns.COunt

By default the array base is 0, but since you started with 0, your loop
argument should also start with 1, and not 0

So

With Sheet1.Range("A1:C5")
  ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
  For i = 1 To .Rows.Count
 For j = 1 To .Columns.Count
MyArray(i, j) = .Cells(i, j).Value
 Next j
  Next i
  End With

End Sub

Finally, your question about Clng. Using the conversion wouldn't really
make a difference if the values were either some numeric value, or blank.
Also, there's no point converting it to a long value if the value is a text
entry. In that case it would anyway throw an error if you use Clng.

Regards,
Sam Mathai Chacko

On Mon, Jan 30, 2012 at 7:00 PM, bpascal123  wrote:

> Hi,
>
> Is there a difference between these 2 methods of assignation  :
>
> Range method :
>
> Dim MyArray() as long, rCnt As Long, cCnt As Integer
>
> With Sheet1.Range("A1:C5")
>ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
>MyArray = Sheet1.Range("A1:C5")
> End With
>
> Loop method :
>
> with Sheet1.Range("A1:C5")
>   redim MyArray(1 to .Rows.Count, 1 To .Columns.Count)
>   for i = 1 to .Rows.Count
>  for j = 1 to .Columns.Count
> MyArray(i-1,j-1) = .Cells(i,j) ''' I'm not even sure if
> it's .Cells(i,j) is the right syntax...the question is not really
> about the syntax
>  next j
>   next i
>
> Are the 2 methods equivalent?
>
> If the range has numbers stored as text along number stored as number,
> is everything going to be cast or converted as Long in MyArray. To me
> it would be an implicit casting since it relies only on the fact that
> MyArray is declared as Long...
>
> Wouldn't it be wiser to use Clong or Cint in the allocation line?
>
> Pascal
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Checkbox disappears when Macro Run

[Thomp]

I have this weird problem that whenever I run a macro I have the
clears some data.. the checkbox control at the top
disappears..Stranger still is if I drag the last row of data or copy
and paste in a new row the checkbox reappears.. I have already tried
changing the format control  to  the option of "dont move or size with
cells" but that has no effect.

The checkbox is from the control toolbox and is assigned a macro..just
in case that makes a differenct..

thanks in advance for any help on this

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Checkbox disappears when Macro Run

[Sam Mathai Chacko]

Try adding a line at the end of the code

Application.ScreenUpdating=True

Regards,
Sam Mathai Chacko

On Mon, Jan 30, 2012 at 8:59 PM, Thomp  wrote:

> I have this weird problem that whenever I run a macro I have the
> clears some data.. the checkbox control at the top
> disappears..Stranger still is if I drag the last row of data or copy
> and paste in a new row the checkbox reappears.. I have already tried
> changing the format control  to  the option of "dont move or size with
> cells" but that has no effect.
>
> The checkbox is from the control toolbox and is assigned a macro..just
> in case that makes a differenct..
>
> thanks in advance for any help on this
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Code for deleting files in a folder.

[Rajan_Verma]

For deleting all files from a folder..

 

Sub deleteFiles()

Dim MyFile As File

Dim MyFolder As Folder

Dim fso As Scripting.FileSystemObject

Set fso = New Scripting.FileSystemObject



Set MyFolder = fso.GetFolder(YourFolderPath)

For Each MyFile In MyFolder.Files

MyFile.Delete

Next

End Sub

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mr excel
Sent: Jan/Thu/2012 06:03
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Code for deleting files in a folder.

 

hi all,

  i would like to know the code for deleting all the files in a folder on a
desired day or say a particular date using vba or any script for that
matter.Can i achieve the same using DOS commands or any software is required
to do.
Kindly help.
Regards
excelkeechak.

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ learn from basic

[Rajan_Verma]

You can visit these sites for advance and Basic VBA..

 

www.ozgrid.com

www.cpearson.com

www.akoul.blogspot.com

www.excelpoweruser.blogspot.com

www.ozgrid.com

akoul.posterous.com

www.mrexcel.com

www.contextures.com

www.rondebruin.nl

peltiertech.com

chandoo.org 

www.mvps.org/links.html

www.vbaexpress.com

www.functionx.com

www.freevbcode.com

www.excelforum.com

https://www.facebook.com/ExcelLover

https://www.facebook.com/pages/Excel-and-VBA-Codes-Macros/15180389897

https://www.facebook.com/microsoftexcel

https://www.facebook.com/l.php?u=http%3A%2F%2Fwww.excelitems.com%2F&h=014d2&;
ref=ts

http://akoul.wordpress.com/2011/05/27/excel-blogs-and-websites-learn-excel-o
nline/

http://www.excelvbamacros.com/

http://ashish-msaccess.blogspot.com/

http://www.facebook.com/groups/163491717053198/

http://www.facebook.com/groups/214214011997936/

http://www.facebook.com/pages/Access-VBA-Macros-Codes/212032078844161

http://www.facebook.com/pages/Excel-VBA-Codes-Macros/15180389897

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of satiqjaffar satiqjaffar
Sent: Jan/Thu/2012 11:29
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ learn from basic

 

Dear Experts,

 

I would like to learn the excel VBA from basic. Please let me know source
from site. Thanks in advance

 

Regards

Satiq

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

[Rajan_Verma]

Use this

=OFFSET(INDEX(Data!A:A,MATCH(Dashboard!E3,Data!A:A,0),1),2,MATCH(Dashboard!F
2,Data!$A$1:$L$1,0)-1)

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of John A. Smith
Sent: Jan/Thu/2012 08:49
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need Formula for Lookup Based on a Matrix of Data

 

Thank you for your valuable help.

 

I need a formula that will lookup a value based on a column heading, a date
column/row and a row qualifier (the word "Total"). Please see the attached
spreadsheet.

 

Thank you.

 

John

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Data Matrix Macro

[Rajan_Verma]

Try this:

 

Sub GetOutput()

Dim rngFrom As Range

Dim rngTo As Range

Dim rngCell As Range

Dim wksOutput As Worksheet

Dim rngCell2 As Range

Dim lngRow As Long



Set rngFrom = Range("rngFrom").Resize(Range("rngFrom").End(xlDown).Row -
(Range("rngFrom").Row - 1), 1)

Set rngTo = Range("rngTo").Resize(1,
Range("rngto").End(xlToRight).Column - (Range("rngTo").Column - 1))

Set wksOutput = ThisWorkbook.Worksheets.Add

lngRow = 2

For Each rngCell In rngFrom

For Each rngCell2 In rngTo

wksOutput.Range("A" & lngRow).Value = rngCell.Value

wksOutput.Range("B" & lngRow).Value = rngCell2.Value

wksOutput.Range("C" & lngRow).Value =
rngCell2.Offset(rngCell.Row - 12).Value

lngRow = lngRow + 1

Next



Next

End Sub

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Pehrson, Dave
Sent: Jan/Fri/2012 01:39
To: excel-macros@googlegroups.com
Cc: 'ashish koul'
Subject: $$Excel-Macros$$ Data Matrix Macro

 

 

I have developed a matrix to capture change over time from one production
model to another, and now I need to upload into a software package with a
format as 


from

To

Time


WT-628

WT-628

0


WT-628

WT-629

15


WT-628

WT-630

45

(see attached partial example of requested output.

 

The process I used manually is outlined as shown below, I'm not a programmer
and you may know a more efficient process to achieve the desired results.  I
assume a series of loops would be required.   (Note: as I do this process in
different areas, the number of data sets will be different and I assume
would become a variable. )

 

1.  Define number of data sets (Constant) by counting the number of
parts on the list in column  H 

2.  Copy part numbers in column H (Data Matrix) to D4 in (Output)

3.  Copy the first number in the data set and paste to C4 in (output)

a. Copy that part to end of data set in column D

4.  Copy the Times in Row I13 to I plus 21 (constant) row 13 and paste
to (output) E4 (transposed)

5.  Repeat this process 21 (constant times) advancing the part number
for column C by one, and the data in data row by one row

 

I hope this is clear, please let me know if you have any questions.  Your
support and expertise is greatly appreciated.  Again I'm not a programmer so
if you have a better way than I outlined in my manual steps above,  please
go with your ideas,  It's the results that's important and I'm always trying
to learn more effective routines.

 

Thanks 

 

David Pehrson

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com
<>

RE: $$Excel-Macros$$ Re: Need help in building pivot using vba

[Rajan_Verma]

Try this:

Sub CreatePivot()

Dim wksPivotSheet As Worksheet

Dim wksWest As Worksheet

Set wksPivotSheet = ThisWorkbook.Worksheets.Add

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

Set oWS = ActiveSheet

Set oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase,
Worksheets("West").UsedRange)

Set oPT = oPC.CreatePivotTable(wksPivotSheet.Range("A1"), "Pivot From
Cache", True)

oPT.AddFields oPT.PivotFields("Account").Name,
oPT.PivotFields("CostCtr").Name

oPT.AddDataField oPT.PivotFields("Amount in local cur."), "Amount",
xlSum

wksPivotSheet.Name = "PivotSummary"

End Sub

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of rekha siri
Sent: Jan/Fri/2012 10:54
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: Need help in building pivot using vba

 

Need pivot macro assistance..

On Wed, Jan 18, 2012 at 9:59 PM, rekha siri 
wrote:

hi experts,

 

Please provide the code to build pivot using vba

 

this macro is provided to me in past by rajan really  thank full for the
concern. 

 

adding to that i need to prepare pivot for  west bu  from attached macro raw
data sheet.

 

pivot format is also attached it should be as per West december2011 file's
pivot manner.

 

attached are macro and west december workbooks which contain pivot ..

 

 

 

kindly help in this concern thanks.

 

Rekha

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Checkbox disappears when Macro Run

[Thomp]

Here is the code Sam..when I added your code line it eliminates all
the lines not just those completed

Sub FilterDeleteColumnF()
Dim Lastrow As Long

Range("N:N").AutoFilter Field:=1, Criteria1:="þ"
Lastrow = Range("N" & Rows.Count).End(xlUp).Row

If Lastrow > 3 Then Range("A3:A" &
Lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp

ActiveSheet.AutoFilterMode = False


On Jan 30, 11:10 am, Sam Mathai Chacko  wrote:
> Try adding a line at the end of the code
>
> Application.ScreenUpdating=True
>
> Regards,
> Sam Mathai Chacko
>
>
>
>
>
> On Mon, Jan 30, 2012 at 8:59 PM, Thomp  wrote:
> > I have this weird problem that whenever I run a macro I have the
> > clears some data.. the checkbox control at the top
> > disappears..Stranger still is if I drag the last row of data or copy
> > and paste in a new row the checkbox reappears.. I have already tried
> > changing the format control  to  the option of "dont move or size with
> > cells" but that has no effect.
>
> > The checkbox is from the control toolbox and is assigned a macro..just
> > in case that makes a differenct..
>
> > thanks in advance for any help on this
>
> > --
> > FORUM RULES (986+ members already BANNED for violation)
>
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > will not get quick attention or may not be answered.
>
> > 2) Don't post a question in the thread of another member.
>
> > 3) Don't post questions regarding breaking or bypassing any security
> > measure.
>
> > 4) Acknowledge the responses you receive, good or bad.
>
> > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > signatures are prohibited.
>
> > NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> > owners and members are not responsible for any loss.
>
> > ---­---
> > To post to this group, send email to excel-macros@googlegroups.com
>
> --
> Sam Mathai Chacko- Hide quoted text -
>
> - Show quoted text -

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Appending multiple file results into one table

[RockyFontane]

Question Experts:

I receive a nightly data dump from one of my vendors which is stored
in a shared drive.  For example, Jan01_2012.csv, Jan02_2012.csv,
Jan03_2012.csv, etc.

Is there a way I can take each file and append the data into one
file?  I have a file for each day from 9/22/2011 thru Yesterday.  The
file format hasn't changedsame amount of columns and the columns
names have not changed.

Let me know.  I'm thinking it could be done in Access, but not sure
about Excel.

Thanks!

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Data to dynamic array---implicit string to interger/long conversion?

[bpascal123]

thanks
...the base 1 array is a mistake, i usually work with base 0, so it
would then be :

>>>redim MyArray(.Rows.count-1, .Colymns.Count-1)
then...

>>>for i = 1 to .rows.count
for j = 1 to .columns.count
>MyArray(i-1, j-1) = ...

I think it's better like that

On Jan 30, 4:59 pm, Sam Mathai Chacko  wrote:
> First of all, your first method has very less likelihood of working. In
> other words, you cannot pass a range of values once you have defined the
> dimensions of the array.
>
> If you had defined the variable as a variant, it would have worked.
>
> So basically,
>
> Dim MyArray as Variant
>
> With Sheet1.Range("A1:C5")
>    MyArray = Sheet1.Range("A1:C5").Value2
> End With
>
> would have worked.
>
> Secondly,
>
> You cannot use a i -1 within your array if you already initialize it with 1
> to .Rows.Count, or 1 to .Columns.COunt
>
> By default the array base is 0, but since you started with 0, your loop
> argument should also start with 1, and not 0
>
> So
>
> With Sheet1.Range("A1:C5")
>   ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
>   For i = 1 To .Rows.Count
>      For j = 1 To .Columns.Count
>         MyArray(i, j) = .Cells(i, j).Value
>      Next j
>   Next i
>   End With
>
> End Sub
>
> Finally, your question about Clng. Using the conversion wouldn't really
> make a difference if the values were either some numeric value, or blank.
> Also, there's no point converting it to a long value if the value is a text
> entry. In that case it would anyway throw an error if you use Clng.
>
> Regards,
> Sam Mathai Chacko
>
>
>
>
>
>
>
>
>
> On Mon, Jan 30, 2012 at 7:00 PM, bpascal123  wrote:
> > Hi,
>
> > Is there a difference between these 2 methods of assignation  :
>
> > Range method :
>
> > Dim MyArray() as long, rCnt As Long, cCnt As Integer
>
> > With Sheet1.Range("A1:C5")
> >    ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
> >    MyArray = Sheet1.Range("A1:C5")
> > End With
>
> > Loop method :
>
> > with Sheet1.Range("A1:C5")
> >   redim MyArray(1 to .Rows.Count, 1 To .Columns.Count)
> >   for i = 1 to .Rows.Count
> >      for j = 1 to .Columns.Count
> >         MyArray(i-1,j-1) = .Cells(i,j) ''' I'm not even sure if
> > it's .Cells(i,j) is the right syntax...the question is not really
> > about the syntax
> >      next j
> >   next i
>
> > Are the 2 methods equivalent?
>
> > If the range has numbers stored as text along number stored as number,
> > is everything going to be cast or converted as Long in MyArray. To me
> > it would be an implicit casting since it relies only on the fact that
> > MyArray is declared as Long...
>
> > Wouldn't it be wiser to use Clong or Cint in the allocation line?
>
> > Pascal
>
> > --
> > FORUM RULES (986+ members already BANNED for violation)
>
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > will not get quick attention or may not be answered.
>
> > 2) Don't post a question in the thread of another member.
>
> > 3) Don't post questions regarding breaking or bypassing any security
> > measure.
>
> > 4) Acknowledge the responses you receive, good or bad.
>
> > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > signatures are prohibited.
>
> > NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> > owners and members are not responsible for any loss.
>
> > --- 
> > ---
> > To post to this group, send email to excel-macros@googlegroups.com
>
> --
> Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Hours calculate highlighted colum

[Rajan_Verma]

=(ROUNDDOWN(G7,0)+H7-ROUNDDOWN(E7,0)-F7)*24

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Asa Rossoff
Sent: Jan/Sun/2012 07:29
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Hours calculate highlighted colum

 

Just to be clear, in the general case, you can add times to date/times.  In
the example spreadsheet the "End Date" column sometimes already included the
time, even though it was hidden by the cell format -- thus the inconsistent
results.  Sam's solution is effective at removing redundant times from the
calculation in this case.

Asa

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Sam Mathai Chacko
Sent: Saturday, January 28, 2012 5:04 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Hours calculate highlighted colum

 

You cannot add time to a date that already has time. The solution has to
take out the date value and time value separately. Otherwise, it will give
inconsistent results. 

Here's how the calculation should be. Also, formatting it to [h]:mm:ss will
give an exact idea of the duration.

=ROUNDDOWN(G6,0)+H6-ROUNDDOWN(E6,0)-F6

Regards,
Sam Mathai Chacko

On Sat, Jan 28, 2012 at 6:16 PM, Yahya Muhammad  wrote:

Dear Yasir

 

Please find the solution in the attached file.

 

On Sat, Jan 28, 2012 at 10:12 AM, yasir_shahjee 
wrote:

Respected Masters,

 

Kindly check attached sheet. i need Time calculate in Hours. hopefully its
really easier for Masters.

 

 

thanks 


 

-- 
Yasir Waheed
Factory Manager
Sinopak Extrusion Technologies (Pvt.) Ltd.

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com




-- 
Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

---

Re: $$Excel-Macros$$ Checkbox disappears when Macro Run

[dguillett1]

UN tested but try

Sub FilterDeleteColumnN()
Application.ScreenUpdating=false
with Range("N1:N"&Range("N" & Rows.Count).End(xlUp).Row)
  .AutoFilter Field:=1, Criteria1:="ş"
  .offset(1).rows.delete
  .AutoFilter
end with
Application.ScreenUpdating=True
end sub


On Jan 30, 11:10 am, Sam Mathai Chacko  wrote:

Try adding a line at the end of the code

Application.ScreenUpdating=True



Don Guillett
SalesAid Software
dguille...@gmail.com
-Original Message- 
From: Thomp

Sent: Monday, January 30, 2012 12:39 PM
To: MS EXCEL AND VBA MACROS
Subject: Re: $$Excel-Macros$$ Checkbox disappears when Macro Run

Here is the code Sam..when I added your code line it eliminates all
the lines not just those completed

Sub FilterDeleteColumnF()
Dim Lastrow As Long

Range("N:N").AutoFilter Field:=1, Criteria1:="ş"
Lastrow = Range("N" & Rows.Count).End(xlUp).Row

If Lastrow > 3 Then Range("A3:A" &
Lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete xlShiftUp

ActiveSheet.AutoFilterMode = False


On Jan 30, 11:10 am, Sam Mathai Chacko  wrote:

Try adding a line at the end of the code

Application.ScreenUpdating=True

Regards,
Sam Mathai Chacko





On Mon, Jan 30, 2012 at 8:59 PM, Thomp  wrote:
> I have this weird problem that whenever I run a macro I have the
> clears some data.. the checkbox control at the top
> disappears..Stranger still is if I drag the last row of data or copy
> and paste in a new row the checkbox reappears.. I have already tried
> changing the format control  to  the option of "dont move or size with
> cells" but that has no effect.

> The checkbox is from the control toolbox and is assigned a macro..just
> in case that makes a differenct..

> thanks in advance for any help on this

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE  : Don't ever post personal or confidential data in a workbook. 
> Forum

> owners and members are not responsible for any loss.

> 
---­---
> To post to this group, send email to excel-macros@googlegroups.com

--
Sam Mathai Chacko- Hide quoted text -

- Show quoted text -


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please 
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will 
not get quick attention or may not be answered.


2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security 
measure.


4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited.


NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com 


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 


NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Adding an Item to a Collection within a Dictionary

[Rajan_Verma]

Dim objDic as object

Set objdic= CreateObject("Scripting.Dictionary")

ObjDic.add Key,values

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of IF
Sent: Jan/Sat/2012 02:10
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Adding an Item to a Collection within a Dictionary

 

I am trying to add an item to a specific collection within a dictionary.  I
am not sure how to do this.  Any ideas?  Thank you. 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Need help in Excel-VBA code to write function in coding

[Rajan_Verma]

You can use this for text also

 

Sub isExist()

On Error Resume Next

If WorksheetFunction.Match(Range("rngValues").Text,
Worksheets("Sheet2").Range("d4:d10"), 0) Then

  If Err.Number = 1004 Then

Err.Clear

On Error GoTo 0: On Error GoTo -1

MsgBox "Values not found"

  Else

MsgBox Range("rngValues").Value

  End If

End If

End Sub

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Pascal Baro
Sent: Jan/Sun/2012 09:44
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Need help in Excel-VBA code to write function in
coding

 

Hi Ashish,

 

Please find another version. With this, I've worked out the fact your data
source is only numbers but either it is entered as text either it is
copy/paste and excel reads it as text. Some of the things I have tried woud
take an input such as 76 and look for everything in the data source that
matches 76 ex.: 7690 would match... So here I consider you only want to
compare number. Tough my code is quite basic if someone looks at it.

 

Applying Rajan_Verma

 

Sub isExist()

On Error Resume Next

If WorksheetFunction.Match(Worksheets("Sheet1").Range("b4").Value,
Worksheets("Sheet2").Range("d4:d10"), 0) Then

  If Err.Number = 1004 Then

Err.Clear

On Error GoTo 0: On Error GoTo -1

MsgBox "Values not found"

  Else

MsgBox Range("rngValues").Value

  End If

End If

End Sub

 

The code above returns false if you compare 8902 to "8902", the first one is
a number, the second one is a text... and I can't make it work if I format
8902 as a number, nothing happens. Why?

 

While I'm learning, I'm helping so sorry for asking a question that might
not be of your concern Ashish...

 

HTH,

 

pascal baro

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Job

[Rajan_Verma]

Hi Group,

 

Please call on 8860604189 Mr.Bhupendra Raghav

raghav.bhupen...@gmail.com

 

Regards

Rajan verma

 

From: Raghav [mailto:bhupendra.rag...@smaca.com] 
Sent: Jan/Tue/2012 12:50
To: rajanverma1...@gmail.com
Subject: FW: Employee Referral Scheme.."Buddy Get Buddy"

 

 

Please find below the requirement:-

 

Details of the Requirement : Total positions (4). 1 -Assistant Manager, 1-
Sr Executive Accounts Payable, 1- Executive MIS, 1- Admin Executive.

 

JOB DESCRIPTION

 

Title: Assistant Manager 

No. Of Openings: 1

Experience- 6+yrs

Package: 4Lpa

Qualification: M.com/MBA/ICWA/CA-inter.

Location: Gurgaon

Preferred industry: Service Industry

KEY SKILLS:-

. End to end accounting experience.

. Good knowledge and experience with AP, AR and GL accounting.

. Experience with Taxation.

. Hands of experience with MIS Reporting.

 

Title: Senior Executive Accounts Payable.

No. Of Openings: 1

Experience- 2+yrs

Package: 2.4Lpa

Qualification: B.Com/M.com/MBA.

Location: Gurgaon

Preferred industry: Service Industry

KEY SKILLS:-

. Experience in Accounts Payable.

. Working exposure in ERP(Oracle) preferable.

. Hands of experience with MIS Reporting.

 

Title: Executive MIS

No. Of Openings: 1

Experience- 1-3yrs

Package: 1.8Lpa

Qualification: Any Graduate.

Location: Gurgaon

Preferred industry: Any Industry

KEY SKILLS:-

. Hands of experience with MIS Reporting.

. Good knowledge in MS Excel(pivot table, Vlookup, sumif).

. Knowledge of MS Access.

 

Title: Executive Admin

No. Of Openings: 1

Experience- 2+yrs

Package: 1.8Lpa

Qualification: Any Graduate.

Location: Okhla(Delhi) 

Preferred industry: Any Industry

KEY SKILLS:-

. House keeping 

. Facility Management 

. Canteen, pantry and stationary.

. Maintain Leave record of the employee and Preparation of Salary

. Making travel and hotel arrangement 

. Good knowledge in MS Excel.

 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Data to dynamic array---implicit string to interger/long conversion?

[Sam Mathai Chacko]

I'd rather go with

>>>for i = 0 to .rows.count-1
for j = 0 to .columns.count-1
>MyArray(i, j) = ...

Regards,

Sam Mathai Chacko

On Tue, Jan 31, 2012 at 12:23 AM, bpascal123  wrote:

> thanks
> ...the base 1 array is a mistake, i usually work with base 0, so it
> would then be :
>
> >>>redim MyArray(.Rows.count-1, .Colymns.Count-1)
> then...
>
> >>>for i = 1 to .rows.count
> for j = 1 to .columns.count
> >MyArray(i-1, j-1) = ...
>
> I think it's better like that
>
> On Jan 30, 4:59 pm, Sam Mathai Chacko  wrote:
> > First of all, your first method has very less likelihood of working. In
> > other words, you cannot pass a range of values once you have defined the
> > dimensions of the array.
> >
> > If you had defined the variable as a variant, it would have worked.
> >
> > So basically,
> >
> > Dim MyArray as Variant
> >
> > With Sheet1.Range("A1:C5")
> >MyArray = Sheet1.Range("A1:C5").Value2
> > End With
> >
> > would have worked.
> >
> > Secondly,
> >
> > You cannot use a i -1 within your array if you already initialize it
> with 1
> > to .Rows.Count, or 1 to .Columns.COunt
> >
> > By default the array base is 0, but since you started with 0, your loop
> > argument should also start with 1, and not 0
> >
> > So
> >
> > With Sheet1.Range("A1:C5")
> >   ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
> >   For i = 1 To .Rows.Count
> >  For j = 1 To .Columns.Count
> > MyArray(i, j) = .Cells(i, j).Value
> >  Next j
> >   Next i
> >   End With
> >
> > End Sub
> >
> > Finally, your question about Clng. Using the conversion wouldn't really
> > make a difference if the values were either some numeric value, or blank.
> > Also, there's no point converting it to a long value if the value is a
> text
> > entry. In that case it would anyway throw an error if you use Clng.
> >
> > Regards,
> > Sam Mathai Chacko
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > On Mon, Jan 30, 2012 at 7:00 PM, bpascal123 
> wrote:
> > > Hi,
> >
> > > Is there a difference between these 2 methods of assignation  :
> >
> > > Range method :
> >
> > > Dim MyArray() as long, rCnt As Long, cCnt As Integer
> >
> > > With Sheet1.Range("A1:C5")
> > >ReDim MyArray(1 To .Rows.Count, 1 To .Columns.Count)
> > >MyArray = Sheet1.Range("A1:C5")
> > > End With
> >
> > > Loop method :
> >
> > > with Sheet1.Range("A1:C5")
> > >   redim MyArray(1 to .Rows.Count, 1 To .Columns.Count)
> > >   for i = 1 to .Rows.Count
> > >  for j = 1 to .Columns.Count
> > > MyArray(i-1,j-1) = .Cells(i,j) ''' I'm not even sure if
> > > it's .Cells(i,j) is the right syntax...the question is not really
> > > about the syntax
> > >  next j
> > >   next i
> >
> > > Are the 2 methods equivalent?
> >
> > > If the range has numbers stored as text along number stored as number,
> > > is everything going to be cast or converted as Long in MyArray. To me
> > > it would be an implicit casting since it relies only on the fact that
> > > MyArray is declared as Long...
> >
> > > Wouldn't it be wiser to use Clong or Cint in the allocation line?
> >
> > > Pascal
> >
> > > --
> > > FORUM RULES (986+ members already BANNED for violation)
> >
> > > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > > will not get quick attention or may not be answered.
> >
> > > 2) Don't post a question in the thread of another member.
> >
> > > 3) Don't post questions regarding breaking or bypassing any security
> > > measure.
> >
> > > 4) Acknowledge the responses you receive, good or bad.
> >
> > > 5)  Cross-promotion of, or links to, forums competitive to this forum
> in
> > > signatures are prohibited.
> >
> > > NOTE  : Don't ever post personal or confidential data in a workbook.
> Forum
> > > owners and members are not responsible for any loss.
> >
> > >
> ---
> ---
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > --
> > Sam Mathai Chacko
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



--

RE: $$Excel-Macros$$ about set focus event

[Rajan_Verma]

I think he is talking abt Focus event .. 

If working with VBA FORm you can use Activate event as setFocus and
terminate as lostFocus event.

 

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NOORAIN ANSARI
Sent: Jan/Sun/2012 11:11
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ about set focus event

 

Dear Shankar,

 

Please see attached sheet..

 

Private Sub TextBox1_Change()
   If Len(TextBox1) = 2 Then
  TextBox2.SetFocus
   End If
End Sub


Private Sub TextBox2_Change()
   If Len(TextBox2) = 2 Then
  TextBox3.SetFocus
   End If
End Sub

On Sat, Jan 28, 2012 at 10:36 AM, Shankar Bheema 
wrote:

Dear Experts

In VB there is a facility of SET FOCUS event whereas in Excel VBA it is not
provided.  Is there any alternative for it in excel vba ?

regards
shankar sb

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com




-- 

Thanks & regards,

Noorain Ansari

  http://excelmacroworld.blogspot.com/

  http://noorain-ansari.blogspot.com/

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Appending multiple file results into one table

[Sam Mathai Chacko]

Depending on the volume of data, you can choose between Excel or Access.
However, the point is, you'd need a macro loop to run through each of those
files and append it to access table, or to Excel sheet.

Regards,
Sam Mathai Chacko

On Tue, Jan 31, 2012 at 12:20 AM, RockyFontane  wrote:

> Question Experts:
>
> I receive a nightly data dump from one of my vendors which is stored
> in a shared drive.  For example, Jan01_2012.csv, Jan02_2012.csv,
> Jan03_2012.csv, etc.
>
> Is there a way I can take each file and append the data into one
> file?  I have a file for each day from 9/22/2011 thru Yesterday.  The
> file format hasn't changedsame amount of columns and the columns
> names have not changed.
>
> Let me know.  I'm thinking it could be done in Access, but not sure
> about Excel.
>
> Thanks!
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Adding an Item to a Collection within a Dictionary

[Sam Mathai Chacko]

OR, you probably are not intending a Dictionary object, but a collection
object.

Sam

On Tue, Jan 31, 2012 at 12:39 AM, Rajan_Verma wrote:

> Dim objDic as object
>
> Set objdic= CreateObject(“Scripting.Dictionary”)
>
> ObjDic.add Key,values
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *IF
> *Sent:* Jan/Sat/2012 02:10
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Adding an Item to a Collection within a
> Dictionary
>
> ** **
>
> I am trying to add an item to a specific collection within a dictionary.
>  I am not sure how to do this.  Any ideas?  Thank you. 
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Job

[Rajan_Verma]

Position Level: Advance Analyst (1-3 yrs) / Sr. Analyst (3-5 yrs) 

 

Job Location: Gurgaon

 

Shifts: Day shifts

Must have: Ms Access + VBA (Know how to code macros) with good communication
skills.

 

 

Job Description: 

 

Perform detailed reviews and update of Access financial models under
supervision and prepares a report of findings in a standardized format.

 

Design and build new applications using VBA, MS Access & Excel and uses
model auditing tools and other software packages.

 

Writing codes and developing new applications using VBA, Access, c# and SQL.

 

Testing and documentation of MS Access / Excel / VBA Applications.

 

Assist team in Code review and bug fixes.

 

Automation of financial reports using different tools.

 

 

Salary:   Not Disclosed by
Recruiter

 

Industry   :   KPO, Research,
Analytics

 

Functional Area:   Application Programming,
Maintenance

 

Role Category:   Programming & Design

 

Role   :   Software
Developer

 

Keyskills   :   :   MS Access,MS -
Access,MS - Access,access, vba coding or vba codes or vba code or visual
basic or vba developer or visual basic application or vb or vba macros

 

Desired Candidate Profile

Education:( UG - Any Graduate - Any
Specialization, Graduation Not Required, B.Sc, B.Tech/B.E., BCA) AND (PG -
Any PG Course - Any Specialization, Post Graduation Not Required, M.Sc,
M.Tech, MBA/PGDM, MCA)

 

Please refer to the Job description above

 

Company Profile

Spectral Consultants -Search and Recruitments Firm 

http://www.spectral.in

Spectral Consultants is a professionally managed, talent search, talent
acquisition and strategic Human Resource consultancy firm in Delhi (N.C.R.)
since 2001. We provide multi-tiered portfolio of services that adds
measurable value to its middle and top management recruitment. Our
successful journey since 2001 has realized us as expert manpower consultants
for managing the diversified projects for different verticals.

 

 

Contact Details

Executive Name:

Sonal, Ankita

Email Address:

 
ankit...@spectral.in,spectral@spectral.in

Telephone:

8802304972,9716006800  

 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ JOB

[Rajan_Verma]

Job Description

 

Hello,

 

Genpact is looking for MS Access Professionals - 

 

BE/B.Tech/MCA or equivalent

 

.3 to 5 years of experience in MS Access (Forms, Database and Reports), VBA,
Visual basic and SQL Server, and Stored Procedures

 

.Thorough understanding of building user interfaces using Visual Basic, MS
Access and SQL server

 

.Good analytical skills, MIS reporting, query writing as well as programming
skill.

 

.Good knowledge of MS Excel, specifically pivot tables with external data
sources. 

 

.SQL Server 2005: query design, optimization, performance / tuning

 

.Strong database development experience with SQL Server in creating database
objects like Stored Procedures/ Views/ Queries/ Triggers etc

 

.Good problem solving skills; the problems are infrequent and usually in the
system environment 

 

.Some business analysis skills to understand and specify enhancements. 

 

.Available at off-hours for problem resolution (one application is business
critical globally)

 

Interested candidates can reach to -"divya.kukr...@genpact.com" with resume,
CTC, ECTC, Exp. & Notice Period.

 

 

Salary:   Not Disclosed by
Recruiter

Industry   :   IT Software, Software
Services

 

Functional Area:

IT-Other

Role Category:   Programming & Design

Role   :   Team
Lead/Technical Lead

Keyskills   :   ms access

 

 

Desired Candidate Profile

 

Education  :   (UG - Any Graduate - Any
Specialization, Graduation Not Required) AND (PG - Any PG Course - Any
Specialization, Post Graduation Not Required)

 

 

Company Profile

 

GENPACT 

http://www.genpact.com

Genpact, formerly GE Capital International Services(GECIS), a GE affiliate
and India's largest BPO and Knowledge Services organization, has over 20,000
employees spread across India, Mexico, Hungary, Poland, Romania, Philippines
and China. In India, Genpact has over 16,000 employees working in offices in
Delhi, Gurgaon, Hyderabad, Bangalore, Kolkata and Jaipur. Genpact provides a
world class working environment and opportunities for career enhancement
through focused training and rotations in different functions. Genpact also
provides benefits such as medical, accident and life insurance, transport
and round-the-clock medical facilities.Genpact was also recently named the
3rd best company to work for in India in the Business Today 10 Best
Companies to work for in India survey. Genpact also topped the list of
companies in the Dataquest 2005 BPO Company rankings.You can read more about
Genpact at http://www.genpact.com.

 

Contact Details

 

Executive Name  :   Divya Kukreja

Contact Company   :   GENPACT

Telephone:   9818840473  

 

 

 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Excel - recording macro - help

[Rajan_Verma]

No,
You can not record macro  like this.. As noorain Explain in the example you
can Execute any Exe File with shell Command..

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Kasireddy Amarender
Sent: Jan/Mon/2012 11:49
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Excel - recording macro - help

Hi Noorain,

Thanks for your email.

Calc was just an example, I just wanted to know if I can see the code ...
which is recorded outside the excel scope. is it possible?

Thanks,
Amar


On 30/01/2012, Kasireddy Amarender  wrote:
> Dear all,
>
> My question may be strange and silly but I thought I'll spell it 
>
> Can I record the macro outside the excel ?
>
> For example, I'll start the macro recording and then minimize all my 
> applications and open calculator and then use some maths like 2+2, can 
> I see the code in my macro all these calculations?
>
> i.e. opening calc and doing operations?
>
> Best regards,
> Amar
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com
>

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Appending multiple file results into one table

[RockyFontane]

Thanks Sam - the data is small (approx 100 rows per day).  Can anyone
provide me with a macro that would accomplish that task?  I'll change
the file names of course.  Let me know.  Thanks!

On Jan 30, 2:30 pm, Sam Mathai Chacko  wrote:
> Depending on the volume of data, you can choose between Excel or Access.
> However, the point is, you'd need a macro loop to run through each of those
> files and append it to access table, or to Excel sheet.
>
> Regards,
> Sam Mathai Chacko
>
>
>
>
>
>
>
>
>
> On Tue, Jan 31, 2012 at 12:20 AM, RockyFontane  wrote:
> > Question Experts:
>
> > I receive a nightly data dump from one of my vendors which is stored
> > in a shared drive.  For example, Jan01_2012.csv, Jan02_2012.csv,
> > Jan03_2012.csv, etc.
>
> > Is there a way I can take each file and append the data into one
> > file?  I have a file for each day from 9/22/2011 thru Yesterday.  The
> > file format hasn't changedsame amount of columns and the columns
> > names have not changed.
>
> > Let me know.  I'm thinking it could be done in Access, but not sure
> > about Excel.
>
> > Thanks!
>
> > --
> > FORUM RULES (986+ members already BANNED for violation)
>
> > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > will not get quick attention or may not be answered.
>
> > 2) Don't post a question in the thread of another member.
>
> > 3) Don't post questions regarding breaking or bypassing any security
> > measure.
>
> > 4) Acknowledge the responses you receive, good or bad.
>
> > 5)  Cross-promotion of, or links to, forums competitive to this forum in
> > signatures are prohibited.
>
> > NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> > owners and members are not responsible for any loss.
>
> > --
> > To post to this group, send email to excel-macros@googlegroups.com
>
> --
> Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ prefix 0 problem

[Rajan_Verma]

WorksheetFunction.Text(1,"") = 0001

 

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Shankar Bheema
Sent: Jan/Mon/2012 11:32
To: excel-macros@googlegroups.com; Sam Mathai Chacko
Subject: $$Excel-Macros$$ prefix 0 problem

 

Dear Experts

In the above attached file, there exists a worksheet containing total
service cell.  It takes the value from the userform data and saves on it.
when a single digit value comes in years or months or days textbox, it
should show prefixing 0.  

application.worksheetfunction.text(arg1,arg2),"interval as string"

is not working and throwing error.  Pls clarify.

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

[Rajan_Verma]

You can use Split method and Assign split values to range

Range("A1:C1")=split("A:B:C",":")

 

Rajan.

 

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Jan/Mon/2012 08:14
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

 

oops. change o1 to p1

 

Application.DisplayAlerts = True

Columns("f").Copy Range("p1")

 

Don Guillett
SalesAid Software
dguille...@gmail.com

 

From: Darwin Chan   

Sent: Sunday, January 29, 2012 11:03 PM

To: excel-macros@googlegroups.com 

Subject: $$Excel-Macros$$ Splitting the values from 1 cell to 2 cells

 

Dear group, 

 

I would like to get the value from 1 cell and split the value to 2 cells.
Any VBA formula can help achieve this?

 

Code starts from Private Sub CommandButton2_Click()

 

Criteria: 

Depends on the position of "X".

If no value in original cell, just leave it blank.

The value before "X" is length, the value after "X" is width.


 

Attached can find sample for reference.

 

-- 
Darwin Chan 

darwin.chankaw...@gmail.com

kw42c...@yahoo.com.hk

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Checkbox disappears when Macro Run

[Rajan_Verma]

Can u attached sample file.. I want to look at that

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Thomp
Sent: Jan/Mon/2012 08:59
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Checkbox disappears when Macro Run

I have this weird problem that whenever I run a macro I have the clears some
data.. the checkbox control at the top disappears..Stranger still is if I
drag the last row of data or copy and paste in a new row the checkbox
reappears.. I have already tried changing the format control  to  the option
of "dont move or size with cells" but that has no effect.

The checkbox is from the control toolbox and is assigned a macro..just in
case that makes a differenct..

thanks in advance for any help on this

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Appending multiple file results into one table

[Sam Mathai Chacko]

Would be better if you post a sample of your master file, and one daily file

Sam Mathai Chacko

On Tue, Jan 31, 2012 at 1:16 AM, RockyFontane  wrote:

> Thanks Sam - the data is small (approx 100 rows per day).  Can anyone
> provide me with a macro that would accomplish that task?  I'll change
> the file names of course.  Let me know.  Thanks!
>
> On Jan 30, 2:30 pm, Sam Mathai Chacko  wrote:
> > Depending on the volume of data, you can choose between Excel or Access.
> > However, the point is, you'd need a macro loop to run through each of
> those
> > files and append it to access table, or to Excel sheet.
> >
> > Regards,
> > Sam Mathai Chacko
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > On Tue, Jan 31, 2012 at 12:20 AM, RockyFontane 
> wrote:
> > > Question Experts:
> >
> > > I receive a nightly data dump from one of my vendors which is stored
> > > in a shared drive.  For example, Jan01_2012.csv, Jan02_2012.csv,
> > > Jan03_2012.csv, etc.
> >
> > > Is there a way I can take each file and append the data into one
> > > file?  I have a file for each day from 9/22/2011 thru Yesterday.  The
> > > file format hasn't changedsame amount of columns and the columns
> > > names have not changed.
> >
> > > Let me know.  I'm thinking it could be done in Access, but not sure
> > > about Excel.
> >
> > > Thanks!
> >
> > > --
> > > FORUM RULES (986+ members already BANNED for violation)
> >
> > > 1) Use concise, accurate thread titles. Poor thread titles, like Please
> > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> > > will not get quick attention or may not be answered.
> >
> > > 2) Don't post a question in the thread of another member.
> >
> > > 3) Don't post questions regarding breaking or bypassing any security
> > > measure.
> >
> > > 4) Acknowledge the responses you receive, good or bad.
> >
> > > 5)  Cross-promotion of, or links to, forums competitive to this forum
> in
> > > signatures are prohibited.
> >
> > > NOTE  : Don't ever post personal or confidential data in a workbook.
> Forum
> > > owners and members are not responsible for any loss.
> >
> > >
> --
> > > To post to this group, send email to excel-macros@googlegroups.com
> >
> > --
> > Sam Mathai Chacko
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Sam Mathai Chacko

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Excel - recording macro - help

[Asa Rossoff]

Kasireddy,
In my opinion the best way to automate windows in general is with the
AutoHotkey script language.  It includes a macro recorder, and you can
modify the resulting scripts as you like.  You can also compile them into
EXE files.  If desired, you can call your script from VBA as in the CALC
example.
http://autohotkey.net - AutoHotkey - Original open-source build
http://l.autohotkey.net - AutoHotkey_L - Fork of AutoHotkey with added
features (64-bit, Object, COM, Unicode support and more)

Asa

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Rajan_Verma
Sent: Monday, January 30, 2012 11:44 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Excel - recording macro - help

No,
You can not record macro  like this.. As noorain Explain in the example you
can Execute any Exe File with shell Command..

Rajan.

-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Kasireddy Amarender
Sent: Jan/Mon/2012 11:49
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Excel - recording macro - help

Hi Noorain,

Thanks for your email.

Calc was just an example, I just wanted to know if I can see the code ...
which is recorded outside the excel scope. is it possible?

Thanks,
Amar


On 30/01/2012, Kasireddy Amarender  wrote:
> Dear all,
>
> My question may be strange and silly but I thought I'll spell it 
>
> Can I record the macro outside the excel ?
>
> For example, I'll start the macro recording and then minimize all my 
> applications and open calculator and then use some maths like 2+2, can 
> I see the code in my macro all these calculations?
>
> i.e. opening calc and doing operations?
>
> Best regards,
> Amar
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like 
> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and 
> Need Advice will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security 
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum 
> in signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. 
> Forum owners and members are not responsible for any loss.
>
> --
>  To post to this group, send email to 
> excel-macros@googlegroups.com
>

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.


--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or

$$Excel-Macros$$ Analytics

[Rohan]

Hi All,
Dear friends, I am looking for a pitch/presentation which represents
the value to data in the organisation and how data can transform the
decision making abilty of an organisation and drive growth. Moreover,
what are the best analytics ability that an organisation can have.

If you have a related slide, please attach the slides. Thank you !!


Regards,
Rohan.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Re: Analytics

[Rohan]

Additionally, this is required for pure marketing and sales, so that I
can attract the team and the leadership on the importance of data and
analytics and also how it can help us improve on efficienc and
forecasting. Any help in this regard will be deeply appreciated.


Regards,
Rohan.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Amazing... Excel Menu Bar... Try it... you will like it

[Rohan]

Nice Work. Thank you !!

Rohan.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ about set focus event

[NOORAIN ANSARI]

Thnks

On Mon, Jan 30, 2012 at 12:43 AM, Asa Rossoff  wrote:

>  Thanks for posting a useful, practical application, Noorain.
>
> Asa
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI
> *Sent:* Sunday, January 29, 2012 9:41 AM
> *To:* excel-macros@googlegroups.com
>
> *Subject:* Re: $$Excel-Macros$$ about set focus event
>
> ** **
>
> Dear Shankar,
>
>  
>
> Please see attached sheet..
>
>  
>
> Private Sub TextBox1_Change()
>If Len(TextBox1) = 2 Then
>   TextBox2.SetFocus
>End If
> End Sub
>
>
> Private Sub TextBox2_Change()
>If Len(TextBox2) = 2 Then
>   TextBox3.SetFocus
>End If
> End Sub
>
> On Sat, Jan 28, 2012 at 10:36 AM, Shankar Bheema 
> wrote:
>
> Dear Experts
>
> In VB there is a facility of SET FOCUS event whereas in Excel VBA it is
> not provided.  Is there any alternative for it in excel vba ?
>
> regards
> shankar sb
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
>
>
>
> -- 
>
> Thanks & regards,
>
> Noorain Ansari
>
> *http://excelmacroworld.blogspot.com/*
> 
>
> *http://noorain-ansari.blogspot.com/*
> 
>
> ** **
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ ExcelTest

[NOORAIN ANSARI]

Dear Group,

Please find attached Excel Test to analyze you excel Skills.

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Excel Test N.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ Excel - recording macro - help

[Kasireddy Amarender]

Thanks Asa,

I'll check this and keep you posted.

Best regards,
Amar

On 31/01/2012, Asa Rossoff  wrote:
> Kasireddy,
> In my opinion the best way to automate windows in general is with the
> AutoHotkey script language.  It includes a macro recorder, and you can
> modify the resulting scripts as you like.  You can also compile them into
> EXE files.  If desired, you can call your script from VBA as in the CALC
> example.
> http://autohotkey.net - AutoHotkey - Original open-source build
> http://l.autohotkey.net - AutoHotkey_L - Fork of AutoHotkey with added
> features (64-bit, Object, COM, Unicode support and more)
>
> Asa
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Rajan_Verma
> Sent: Monday, January 30, 2012 11:44 AM
> To: excel-macros@googlegroups.com
> Subject: RE: $$Excel-Macros$$ Excel - recording macro - help
>
> No,
> You can not record macro  like this.. As noorain Explain in the example you
> can Execute any Exe File with shell Command..
>
> Rajan.
>
> -Original Message-
> From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
> On Behalf Of Kasireddy Amarender
> Sent: Jan/Mon/2012 11:49
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Excel - recording macro - help
>
> Hi Noorain,
>
> Thanks for your email.
>
> Calc was just an example, I just wanted to know if I can see the code ...
> which is recorded outside the excel scope. is it possible?
>
> Thanks,
> Amar
>
>
> On 30/01/2012, Kasireddy Amarender  wrote:
>> Dear all,
>>
>> My question may be strange and silly but I thought I'll spell it 
>>
>> Can I record the macro outside the excel ?
>>
>> For example, I'll start the macro recording and then minimize all my
>> applications and open calculator and then use some maths like 2+2, can
>> I see the code in my macro all these calculations?
>>
>> i.e. opening calc and doing operations?
>>
>> Best regards,
>> Amar
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like
>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
>> Need Advice will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum
>> in signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>> --
>>  To post to this group, send email to
>> excel-macros@googlegroups.com
>>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> 
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
> 
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in t

RE: $$Excel-Macros$$ IS.xls

[Mohammed Muneer]

That's what I want!

Thanx.

 

Regards,

Muneer,

CC...

 



From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI
Sent: Monday, January 30, 2012 7:35 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ IS.xls

 

Dear Muneer,

 

Pls see attached sheet.

On Mon, Jan 30, 2012 at 7:16 PM, dguillett1 
wrote:

 =B3*($F$6/100) 

and copy down

 

Don Guillett
SalesAid Software
dguille...@gmail.com

 

From: Mohammed Muneer   

Sent: Monday, January 30, 2012 2:01 AM

To: excel-macros@googlegroups.com 

Subject: $$Excel-Macros$$ IS.xls

 

plz, check I write clearly now. I was little busy doing something other
than this.

 

I need the same in formula.

 

 

 

Regards,

Muneer,

CC...

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com




-- 

Thanks & regards,

Noorain Ansari

http://excelmacroworld.blogspot.com/
 

http://noorain-ansari.blogspot.com/
 

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Formula for minimum no..

[chhajersandeep]

Dear Excel guru,

I have a data like below

Admin.  5
Household.7
Production.2
Stores.  3

Admin.  2
Household.3
Production.1
Stores.  5

Admin.  6
Household.1
Production.3
Stores.  2


Now I want here the minimum no of respective dept by formula..like

Admin.  2 (min of 5,2,6)
Household.1
Production.1
Stores.  2 

Please help asap.

With Thanks

Sandeep Chhajer.
Sent on my BlackBerry® from Vodafone

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Formula for minimum no..

[NOORAIN ANSARI]

Dear Sandeep,

Please try it,,

=SMALL(IF($A$1:$A$14=A17,$B$1:$B$14,""),1) with ctrl+Shift+enter(CSE)
See attached sheet.

Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Tue, Jan 31, 2012 at 10:06 AM,  wrote:

> Dear Excel guru,
>
> I have a data like below
>
> Admin.  5
> Household.7
> Production.2
> Stores.  3
>
> Admin.  2
> Household.3
> Production.1
> Stores.  5
>
> Admin.  6
> Household.1
> Production.3
> Stores.  2
>
>
> Now I want here the minimum no of respective dept by formula..like
>
> Admin.  2 (min of 5,2,6)
> Household.1
> Production.1
> Stores.  2
>
> Please help asap.
>
> With Thanks
>
> Sandeep Chhajer.
> Sent on my BlackBerry® from Vodafone
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



--

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Solution(sandeep).xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Analytics

[Vikash Chandra]

Hi Rohan,
If you need the slides you have to relay on google only. Because i
never come across these type of basic slides. But with right data you
can predict lots of things like simple will be churn,your sales/
Profit driver,forcasting, correlation,default rate,survival rate of
your customer/product/Process etc.. There are more lots of there so as
per my understanding you can get the slides of the approach,means how
to go on some business problem given the type of data.

Thanks & Regards,
Vikash Chandra
Bangalore
9902622922

On Tue, Jan 31, 2012 at 2:33 AM, Rohan  wrote:
> Hi All,
> Dear friends, I am looking for a pitch/presentation which represents
> the value to data in the organisation and how data can transform the
> decision making abilty of an organisation and drive growth. Moreover,
> what are the best analytics ability that an organisation can have.
>
> If you have a related slide, please attach the slides. Thank you !!
>
>
> Regards,
> Rohan.
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
> Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not 
> get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5)  Cross-promotion of, or links to, forums competitive to this forum in 
> signatures are prohibited.
>
> NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
> owners and members are not responsible for any loss.
>
> --
> To post to this group, send email to excel-macros@googlegroups.com



--

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Formula for minimum no..

[NOORAIN ANSARI]

Dear Sandeep,

You can also use...

=MIN(IF($A$1:$A$14=A17,$B$1:$B$14,"")) with ctrl+shift+enter

Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 


On Tue, Jan 31, 2012 at 10:47 AM, NOORAIN ANSARI
wrote:

> Dear Sandeep,
>
> Please try it,,
>
> =SMALL(IF($A$1:$A$14=A17,$B$1:$B$14,""),1) with ctrl+Shift+enter(CSE)
> See attached sheet.
>
>  On Tue, Jan 31, 2012 at 10:06 AM,  wrote:
>
>> Dear Excel guru,
>>
>> I have a data like below
>>
>> Admin.  5
>> Household.7
>> Production.2
>> Stores.  3
>>
>> Admin.  2
>> Household.3
>> Production.1
>> Stores.  5
>>
>> Admin.  6
>> Household.1
>> Production.3
>> Stores.  2
>>
>>
>> Now I want here the minimum no of respective dept by formula..like
>>
>> Admin.  2 (min of 5,2,6)
>> Household.1
>> Production.1
>> Stores.  2
>>
>> Please help asap.
>>
>> With Thanks
>>
>> Sandeep Chhajer.
>> Sent on my BlackBerry® from Vodafone
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --




-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Payslip macro need

[chandra sekaran]

Dear all,

Here with attached  salary data  and  payslip format  i have data sheet
that is containing emp salary details  & i have sheet   Payslip   .  this
is containing   Payslip formati need  macro for this only once  i will
run macro  all the employee  should generate this format and convert to PDF
and sent to respecite employee

any one help to make macro   for this

Advance thanks to all

Regards
chandru

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Payslip.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

$$Excel-Macros$$ Help Required

[PRAVESH KUMAR]

 Hi Group,

I want to extract my data horizently according to my header from vertical
row data.
See attached sheet for more clearity.

Thanks in advance


With Regards,
Pravesh

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Minimum & Maximum value of a Cell

[PRAVESH KUMAR]

Hi Group,

I need to extract min & max value of a cell.
Sample sheet is attached herewith.
Thanks in advance.

With Regards,
Pravesh

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


query(1).xlsm
Description: Binary data

$$Excel-Macros$$ VLookup Problem

[PRAVESH KUMAR]

Hi Group,

I am unable to extract AHT Column data through Vlookup.
Please sove my query..

Thanks in Advance

With Regards,
Pravesh

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Question.xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ VLookup Problem

[NOORAIN ANSARI]

Dear Pravesh,

Please use..this one..see attached sheet..

=INDEX($D$2:$D$10,MATCH(K2,TIME(HOUR($B$2:$B$10),MINUTE($B$2:$B$10),SECOND($B$2:$B$10)),0))

with ctrl+shift+Enter

-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 
On Tue, Jan 31, 2012 at 9:39 AM, PRAVESH KUMAR wrote:

> Hi Group,
>
> I am unable to extract AHT Column data through Vlookup.
> Please sove my query..
>
> Thanks in Advance
>
> With Regards,
> Pravesh
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Question(Solved).xls
Description: MS-Excel spreadsheet

Re: $$Excel-Macros$$ VLookup Problem

[NOORAIN ANSARI]

=OFFSET($D$1,MATCH(K2,TIME(HOUR($B$2:$B$10),MINUTE($B$2:$B$10),SECOND($B$2:$B$10)),0),0)
With ctrl+shift+enter

On Tue, Jan 31, 2012 at 11:24 AM, NOORAIN ANSARI
wrote:

> Dear Pravesh,
>
> Please use..this one..see attached sheet..
>
>
> =INDEX($D$2:$D$10,MATCH(K2,TIME(HOUR($B$2:$B$10),MINUTE($B$2:$B$10),SECOND($B$2:$B$10)),0))
>
> with ctrl+shift+Enter
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>  On Tue, Jan 31, 2012 at 9:39 AM, PRAVESH KUMAR 
> wrote:
>
>> Hi Group,
>>
>> I am unable to extract AHT Column data through Vlookup.
>> Please sove my query..
>>
>> Thanks in Advance
>>
>> With Regards,
>> Pravesh
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
>
>


-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Help Required

[NOORAIN ANSARI]

attachment Missing

On Tue, Jan 31, 2012 at 9:26 AM, PRAVESH KUMAR wrote:

>  Hi Group,
>
> I want to extract my data horizently according to my header from vertical
> row data.
> See attached sheet for more clearity.
>
> Thanks in advance
>
>
> With Regards,
> Pravesh
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Payslip macro need

[chandra sekaran]

hi rajan,

Macro is protected  can you sent with protection
regards
chandru




On Tue, Jan 31, 2012 at 11:18 AM, Rajan_Verma wrote:

>  Hi Chandru
>
> I have create a macro for the same before some time ,You can modify this
> macro according to your Requirement… Please see the attached sheet.
>
> ** **
>
> Thanks
>
> Rajan Verma
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *chandra sekaran
> *Sent:* Jan/Tue/2012 11:09
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Payslip macro need
>
> ** **
>
> Dear all, 
>
>  
>
> Here with attached  salary data  and  payslip format  i have data sheet
> that is containing emp salary details  & i have sheet   Payslip   .  this
> is containing   Payslip formati need  macro for this only once  i will
> run macro  all the employee  should generate this format and convert to PDF
> and sent to respecite employee  
>
>  
>
> any one help to make macro   for this   
>
>  
>
> Advance thanks to all 
>
>  
>
> Regards
>
> chandru 
>
>  
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Income tax calculation for employee :- Usefull to our Group Member

[Rajan_Verma]

Great

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of chandra sekaran
Sent: Jan/Tue/2012 11:44
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Income tax calculation for employee :- Usefull to
our Group Member

 

Dear all 

 

I found  this income tax calculation  from  ynithya.comthis is realy use
full to our group member   

 

 


If you want me to send you a new version whenever I update, please send a
blank e-mail to taxc...@ynithya.com with only the subject as "SUBSCRIBE".
There are no charges for subscription

 

Thankig you 

Regards

chandru 

 

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Income tax calculation for employee :- Usefull to our Group Member

[NOORAIN ANSARI]

very usefurl site..

On Tue, Jan 31, 2012 at 11:43 AM, chandra sekaran wrote:

> Dear all
>
> I found  this income tax calculation  from  ynithya.comthis is realy
> use full to our group member
>
>
> If you want me to send you a new version whenever I update, please
> send a blank e-mail to *taxc...@ynithya.com* with only the subject as "*
> SUBSCRIBE*". There are no charges for subscription
>
> Thankig you
> Regards
> chandru
>
>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Payslip macro need

[chandra sekaran]

Thanks rajan




On Tue, Jan 31, 2012 at 11:46 AM, Rajan_Verma wrote:

>  Hi Chandru
>
> Please find unprotected version
>
> ** **
>
> Rajan.
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *chandra sekaran
> *Sent:* Jan/Tue/2012 11:32
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Payslip macro need
>
> ** **
>
> hi rajan,
>
>  
>
> Macro is protected  can you sent with protection 
>
> regards
>
> chandru
>
>  
>
>
>
>  
>
> On Tue, Jan 31, 2012 at 11:18 AM, Rajan_Verma 
> wrote:
>
> Hi Chandru
>
> I have create a macro for the same before some time ,You can modify this
> macro according to your Requirement… Please see the attached sheet.
>
>  
>
> Thanks
>
> Rajan Verma
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *chandra sekaran
> *Sent:* Jan/Tue/2012 11:09
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Payslip macro need
>
>  
>
> Dear all, 
>
>  
>
> Here with attached  salary data  and  payslip format  i have data sheet
> that is containing emp salary details  & i have sheet   Payslip   .  this
> is containing   Payslip formati need  macro for this only once  i will
> run macro  all the employee  should generate this format and convert to PDF
> and sent to respecite employee  
>
>  
>
> any one help to make macro   for this   
>
>  
>
> Advance thanks to all 
>
>  
>
> Regards
>
> chandru 
>
>  
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> ** **
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsibl

RE: $$Excel-Macros$$ Payslip macro need

[Rajan_Verma]

You just need to change Range reference in Macro Code.. 

 

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of chandra sekaran
Sent: Jan/Tue/2012 11:51
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Payslip macro need

 

Thanks rajan

 



 

On Tue, Jan 31, 2012 at 11:46 AM, Rajan_Verma 
wrote:

Hi Chandru

Please find unprotected version

 

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of chandra sekaran
Sent: Jan/Tue/2012 11:32
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Payslip macro need

 

hi rajan,

 

Macro is protected  can you sent with protection 

regards

chandru

 



 

On Tue, Jan 31, 2012 at 11:18 AM, Rajan_Verma 
wrote:

Hi Chandru

I have create a macro for the same before some time ,You can modify this
macro according to your Requirement. Please see the attached sheet.

 

Thanks

Rajan Verma

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of chandra sekaran
Sent: Jan/Tue/2012 11:09
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Payslip macro need

 

Dear all, 

 

Here with attached  salary data  and  payslip format  i have data sheet
that is containing emp salary details  & i have sheet   Payslip   .  this is
containing   Payslip formati need  macro for this only once  i will run
macro  all the employee  should generate this format and convert to PDF and
sent to respecite employee  

 

any one help to make macro   for this   

 

Advance thanks to all 

 

Regards

chandru 

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To

RE: $$Excel-Macros$$ Minimum & Maximum value of a Cell

[Rajan_Verma]

For  Min

=MIN(INT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

For Max

=MAX(INT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))

 

Use with CSE

 

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of PRAVESH KUMAR
Sent: Jan/Tue/2012 09:42
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Minimum & Maximum value of a Cell

 

Hi Group,

I need to extract min & max value of a cell.
Sample sheet is attached herewith.

Thanks in advance.

 

With Regards,

Pravesh

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Password protect hidden sheets

[PrIyAnKa]

Dear Team

Kindly let me know if  i have a workbook having 3 sheets in it, and i hide
2 sheets of it and when next time i unhide those  sheets a password box
gets open and without password it can't be unhide

I have excel 2007 version

If any more details req kindly let me know

Regards
Priyanka

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ Password protect hidden sheets

[Rajan_Verma]

You can Protect Workbook. After hiding Worksheets..

 

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of PrIyAnKa
Sent: Jan/Tue/2012 12:05
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Password protect hidden sheets

 

Dear Team

 

Kindly let me know if  i have a workbook having 3 sheets in it, and i hide 2
sheets of it and when next time i unhide those  sheets a password box gets
open and without password it can't be unhide

 

I have excel 2007 version 

 

If any more details req kindly let me know

 

Regards

Priyanka

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Minimum & Maximum value of a Cell

[NOORAIN ANSARI]

Dear Pravesh,

Try it..UDF

*For Minimum Value..*.

Function Minval(x As String)
Dim i As Integer, minx As Integer
For i = 1 To Len(x)
If IsNumeric(Mid(x, i, 1)) Then
minx = Mid(x, i, 1)
Exit For
End If
Next
For i = 1 To Len(x)
If IsNumeric(Mid(x, i, 1)) Then
If Mid(x, i, 1) < minx Then
minx = Mid(x, i, 1)
End If
End If
Next i
Minval = minx
End Function
*For Maximum Value*
Function Maxval(x As String)
Dim i As Integer, maxx As Integer
For i = 1 To Len(x)
If IsNumeric(Mid(x, i, 1)) Then
maxx = Mid(x, i, 1)
Exit For
End If
Next
For i = 1 To Len(x)
If IsNumeric(Mid(x, i, 1)) Then
If Mid(x, i, 1) > maxx Then
maxx = Mid(x, i, 1)
End If
End If
Next i
Maxval = maxx
End Function
See attached sheet
-- 
 Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

On Tue, Jan 31, 2012 at 9:42 AM, PRAVESH KUMAR wrote:

> Hi Group,
>
> I need to extract min & max value of a cell.
> Sample sheet is attached herewith.
> Thanks in advance.
>
> With Regards,
> Pravesh
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


query(Solved).xlsm
Description: Binary data

Re: $$Excel-Macros$$ prefix 0 problem

[Shankar Bheema]

Good afternoon all.

Thank you very much Sam and Asa.  Actually I am not so sound in code
building.  I am working in a office and trying to minimise and simplifying
my dealing work by automating the data entry.  For that I joined for VB 6.0
and built some programs but many were failed.  After a long gap, I saw this
community in Google and registered and with the help of this community only
I built those failed programmes in EXCEL VBA and now I am very comfortable
with my work.  Thanks one and all. I never forget your valuable help.

regards
shankar

On Tue, Jan 31, 2012 at 1:26 AM, Rajan_Verma wrote:

> WorksheetFunction.Text(1,””) = 0001
>
> ** **
>
> ** **
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Shankar Bheema
> *Sent:* Jan/Mon/2012 11:32
>
> *To:* excel-macros@googlegroups.com; Sam Mathai Chacko
> *Subject:* $$Excel-Macros$$ prefix 0 problem
>
> ** **
>
> Dear Experts
>
>
> In the above attached file, there exists a worksheet containing total
> service cell.  It takes the value from the userform data and saves on it.
> when a single digit value comes in years or months or days textbox, it
> should show prefixing 0.
>
> application.worksheetfunction.text(arg1,arg2),"interval as string"
>
> is not working and throwing error.  Pls clarify.
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ about set focus event

[Shankar Bheema]

thank you so much


On Tue, Jan 31, 2012 at 7:53 AM, NOORAIN ANSARI wrote:

> Thnks
>
>
> On Mon, Jan 30, 2012 at 12:43 AM, Asa Rossoff  wrote:
>
>>  Thanks for posting a useful, practical application, Noorain.
>>
>> Asa
>>
>> ** **
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *NOORAIN ANSARI
>> *Sent:* Sunday, January 29, 2012 9:41 AM
>> *To:* excel-macros@googlegroups.com
>>
>> *Subject:* Re: $$Excel-Macros$$ about set focus event
>>
>> ** **
>>
>> Dear Shankar,
>>
>>  
>>
>> Please see attached sheet..
>>
>>  
>>
>> Private Sub TextBox1_Change()
>>If Len(TextBox1) = 2 Then
>>   TextBox2.SetFocus
>>End If
>> End Sub
>>
>>
>> Private Sub TextBox2_Change()
>>If Len(TextBox2) = 2 Then
>>   TextBox3.SetFocus
>>End If
>> End Sub
>>
>> On Sat, Jan 28, 2012 at 10:36 AM, Shankar Bheema 
>> wrote:
>>
>> Dear Experts
>>
>> In VB there is a facility of SET FOCUS event whereas in Excel VBA it is
>> not provided.  Is there any alternative for it in excel vba ?
>>
>> regards
>> shankar sb
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>>
>>
>>
>> -- 
>>
>> Thanks & regards,
>>
>> Noorain Ansari
>>
>> *http://excelmacroworld.blogspot.com/*
>> 
>>
>> *http://noorain-ansari.blogspot.com/*
>> 
>>
>> ** **
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. F

Re: $$Excel-Macros$$ Password protect hidden sheets

[NOORAIN ANSARI]

Hi Priyanka,

I think you can't unhide sheet without password..

refer to below link..
http://www.howtogeek.com/howto/14160/hide-and-unhide-worksheets-and-workbooks-in-excel-2007-2010/



On Tue, Jan 31, 2012 at 12:05 PM, PrIyAnKa  wrote:

> Dear Team
>
> Kindly let me know if  i have a workbook having 3 sheets in it, and i hide
> 2 sheets of it and when next time i unhide those  sheets a password box
> gets open and without password it can't be unhide
>
> I have excel 2007 version
>
> If any more details req kindly let me know
>
> Regards
> Priyanka
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>



-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Sent Mailer data

[Anjul Porwal]

hi.

attach data for mailer. we want macro for sent data for anil.

Regards
Anjul.

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


Mailers data.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

RE: $$Excel-Macros$$ Sent Mailer data

[Rajan_Verma]

Can you send the output of mail .. 

What will the format?

 

Rajan.

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Anjul Porwal
Sent: Jan/Tue/2012 12:40
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Sent Mailer data

 

hi.

 

attach data for mailer. we want macro for sent data for anil.

 

Regards

Anjul.

 

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Formula for minimum no..

[chhajersandeep]

Thanks Noorain its working great!

Sandeep Chhajer.

Sent on my BlackBerry® from Vodafone

-Original Message-
From: NOORAIN ANSARI 
Sender: excel-macros@googlegroups.com
Date: Tue, 31 Jan 2012 10:56:00 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Formula for minimum no..

Dear Sandeep,

You can also use...

=MIN(IF($A$1:$A$14=A17,$B$1:$B$14,"")) with ctrl+shift+enter

Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 


On Tue, Jan 31, 2012 at 10:47 AM, NOORAIN ANSARI
wrote:

> Dear Sandeep,
>
> Please try it,,
>
> =SMALL(IF($A$1:$A$14=A17,$B$1:$B$14,""),1) with ctrl+Shift+enter(CSE)
> See attached sheet.
>
>  On Tue, Jan 31, 2012 at 10:06 AM,  wrote:
>
>> Dear Excel guru,
>>
>> I have a data like below
>>
>> Admin.  5
>> Household.7
>> Production.2
>> Stores.  3
>>
>> Admin.  2
>> Household.3
>> Production.1
>> Stores.  5
>>
>> Admin.  6
>> Household.1
>> Production.3
>> Stores.  2
>>
>>
>> Now I want here the minimum no of respective dept by formula..like
>>
>> Admin.  2 (min of 5,2,6)
>> Household.1
>> Production.1
>> Stores.  2
>>
>> Please help asap.
>>
>> With Thanks
>>
>> Sandeep Chhajer.
>> Sent on my BlackBerry® from Vodafone
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5)  Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE  : Don't ever post personal or confidential data in a workbook.
>> Forum owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --




-- 
Thanks & regards,
Noorain Ansari
 *http://excelmacroworld.blogspot.com/*
*http://noorain-ansari.blogspot.com/* 

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Password protect hidden sheets

[PrIyAnKa]

I want that other person can open the whole sheet but he can't open the
hidden sheets w/o password

Can it be done?

protecting a whole workbook is not useful for me , Rajan

On Tue, Jan 31, 2012 at 12:31 PM, NOORAIN ANSARI
wrote:

> Hi Priyanka,
>
> I think you can't unhide sheet without password..
>
> refer to below link..
>
> http://www.howtogeek.com/howto/14160/hide-and-unhide-worksheets-and-workbooks-in-excel-2007-2010/
>
>
>
> On Tue, Jan 31, 2012 at 12:05 PM, PrIyAnKa  wrote:
>
>> Dear Team
>>
>> Kindly let me know if  i have a workbook having 3 sheets in it, and i
>> hide 2 sheets of it and when next time i unhide those  sheets a password
>> box gets open and without password it can't be unhide
>>
>> I have excel 2007 version
>>
>> If any more details req kindly let me know
>>
>> Regards
>> Priyanka
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

RE: $$Excel-Macros$$ PivotByMultiSheet2

[Rajan_Verma]

Press Shift + Ctrl P and add the ranges to list box.. Range should have same
column.. Not a single heading should be blank.. 

First it copy all range data to A temp sheet  and Create a pivot table..
then it  will delete that temp sheet.. 

 

With Pivot table all data will store in Pivot Cache you can retrieve the
data again ..

 

Thanks

Rajan

 

From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Ashish Bhalara
Sent: Jan/Tue/2012 12:49
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ PivotByMultiSheet2

 

Dear Experts

 

Kindly know me how to use the file of PivotByMultiSheet2 which I downloaded
from your mails.

 

Thanks & regards

 

Ashish Bhalara

-- 
FORUM RULES (986+ members already BANNED for violation)
 
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
 
2) Don't post a question in the thread of another member.
 
3) Don't post questions regarding breaking or bypassing any security
measure.
 
4) Acknowledge the responses you receive, good or bad.
 
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited. 
 
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
 

--
To post to this group, send email to excel-macros@googlegroups.com

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Sent Mailer data

[Anjul Porwal]

Thanks noorain...

Regards
anjul

On Tue, Jan 31, 2012 at 12:51 PM, NOORAIN ANSARI
wrote:

> Dear Anjul,
>
> Please try it and change email Id as per your requiremrnt..
>
> Sub maildata()
> Dim a As Workbook
> Dim data As Range
> Set data = ThisWorkbook.Sheets(1).UsedRange
> data.Copy
> Set a = Workbooks.Add
> a.Sheets(1).Cells(1, 1).Select
> ActiveSheet.Paste
> MsgBox ThisWorkbook.Path
> a.SaveAs ThisWorkbook.Path & "Automatic_ Email.xls"
> a.Close
> Dim olApp As Outlook.Application
> Dim olMail As MailItem
> Dim SigString As String
> Dim Signature As String
>
> Application.ScreenUpdating = False
> Set olApp = New Outlook.Application
> Set olMail = olApp.CreateItem(olMailItem)
> With olMail
> .To = "noorain.ans...@gmail.com"
> .CC = "noorain.ans...@one97.net"
>  .Subject = "Incoming Status!"
> .Body = "Messages " & vbCrLf & vbCrLf & " Thanks " & vbCrLf & "Regards" &
> vbCrLf & " Noorain Ansari"
> .Attachments.Add ThisWorkbook.Path & "Automatic_ Email.xls"
> .Send
>  End With
> Set olMail = Nothing
> 'Kill ThisWorkbook.Path & "\abc1.xls"
> End Sub
>
> --
> Thanks & regards,
> Noorain Ansari
>  *http://excelmacroworld.blogspot.com/*
> *http://noorain-ansari.blogspot.com/*
>
>
> On Tue, Jan 31, 2012 at 12:40 PM, Anjul Porwal wrote:
>
>> hi.
>>
>> attach data for mailer. we want macro for sent data for anil.
>>
>> Regards
>> Anjul.
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

$$Excel-Macros$$ Pareto-chart-

[Anjul Porwal]

Hi expert,

Can u make Pareto-chart- for attach data.

Regads,
Anjul

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com


data for peroto chart.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

Re: $$Excel-Macros$$ Password protect hidden sheets

[Shaik Waheed]

First assign a password to the sheet before hiding it, so that when other
user unhide's it, the sheet will be in read only mode and if the user want
to do changes it will prompt for password.

You can do this by using keyboard shortcut: ALT+T,P,P and set the password.

Hope it will help u.

On Tue, Jan 31, 2012 at 12:58 PM, PrIyAnKa  wrote:

> I want that other person can open the whole sheet but he can't open the
> hidden sheets w/o password
>
> Can it be done?
>
> protecting a whole workbook is not useful for me , Rajan
>
> On Tue, Jan 31, 2012 at 12:31 PM, NOORAIN ANSARI  > wrote:
>
>> Hi Priyanka,
>>
>> I think you can't unhide sheet without password..
>>
>> refer to below link..
>>
>> http://www.howtogeek.com/howto/14160/hide-and-unhide-worksheets-and-workbooks-in-excel-2007-2010/
>>
>>
>>
>> On Tue, Jan 31, 2012 at 12:05 PM, PrIyAnKa  wrote:
>>
>>> Dear Team
>>>
>>> Kindly let me know if  i have a workbook having 3 sheets in it, and i
>>> hide 2 sheets of it and when next time i unhide those  sheets a password
>>> box gets open and without password it can't be unhide
>>>
>>> I have excel 2007 version
>>>
>>> If any more details req kindly let me know
>>>
>>> Regards
>>> Priyanka
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>>
>>
>> --
>> Thanks & regards,
>> Noorain Ansari
>>  *http://excelmacroworld.blogspot.com/*
>> *http://noorain-ansari.blogspot.com/*
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to

Re: $$Excel-Macros$$ PivotByMultiSheet2

[Ashish Bhalara]

Thanks Rajan, but data not display in PivotSheet, anything wrong with me?

Ashish Bhalara

On Tue, Jan 31, 2012 at 12:59 PM, Rajan_Verma wrote:

> Press Shift + Ctrl P and add the ranges to list box.. Range should have
> same column.. Not a single heading should be blank.. 
>
> First it copy all range data to A temp sheet  and Create a pivot table..
> then it  will delete that temp sheet.. 
>
> ** **
>
> With Pivot table all data will store in Pivot Cache you can retrieve the
> data again ..
>
> ** **
>
> Thanks
>
> Rajan
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Ashish Bhalara
> *Sent:* Jan/Tue/2012 12:49
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ PivotByMultiSheet2
>
> ** **
>
> Dear Experts
>
> ** **
>
> Kindly know me how to use the file of PivotByMultiSheet2 which I
> downloaded from your mails.
>
> ** **
>
> Thanks & regards
>
> ** **
>
> Ashish Bhalara
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> --
> To post to this group, send email to excel-macros@googlegroups.com
>

-- 
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security measure.

4) Acknowledge the responses you receive, good or bad.

5)  Cross-promotion of, or links to, forums competitive to this forum in 
signatures are prohibited. 

NOTE  : Don't ever post personal or confidential data in a workbook. Forum 
owners and members are not responsible for any loss.

--
To post to this group, send email to excel-macros@googlegroups.com

Re: $$Excel-Macros$$ Password protect hidden sheets

[Yahya Muhammad]

Hi Priyanka

Yes. But there is a work around for it using VBA . But without using any
codes.  It goes like this:

   1.  Right click on the sheet tab to be made hidden, View Code. See the
   properties menu on the left bottom window. Choose the visible mode as  "2-
   XLSheetVeryHidden"
   2. Then protect the worksheet vba with a password. (Tools- VBA project
   properties-protection)
   3. Now this worksheet will be hidden and invisible. You only know that
   there is a hidden worksheet and you can only unhide it !




On Tue, Jan 31, 2012 at 10:42 AM, Shaik Waheed  wrote:

> First assign a password to the sheet before hiding it, so that when other
> user unhide's it, the sheet will be in read only mode and if the user want
> to do changes it will prompt for password.
>
> You can do this by using keyboard shortcut: ALT+T,P,P and set the password.
>
> Hope it will help u.
>
> On Tue, Jan 31, 2012 at 12:58 PM, PrIyAnKa  wrote:
>
>> I want that other person can open the whole sheet but he can't open the
>> hidden sheets w/o password
>>
>> Can it be done?
>>
>> protecting a whole workbook is not useful for me , Rajan
>>
>> On Tue, Jan 31, 2012 at 12:31 PM, NOORAIN ANSARI <
>> noorain.ans...@gmail.com> wrote:
>>
>>> Hi Priyanka,
>>>
>>> I think you can't unhide sheet without password..
>>>
>>> refer to below link..
>>>
>>> http://www.howtogeek.com/howto/14160/hide-and-unhide-worksheets-and-workbooks-in-excel-2007-2010/
>>>
>>>
>>>
>>> On Tue, Jan 31, 2012 at 12:05 PM, PrIyAnKa wrote:
>>>
 Dear Team

 Kindly let me know if  i have a workbook having 3 sheets in it, and i
 hide 2 sheets of it and when next time i unhide those  sheets a password
 box gets open and without password it can't be unhide

 I have excel 2007 version

 If any more details req kindly let me know

 Regards
 Priyanka

 --
 FORUM RULES (986+ members already BANNED for violation)

 1) Use concise, accurate thread titles. Poor thread titles, like Please
 Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
 will not get quick attention or may not be answered.

 2) Don't post a question in the thread of another member.

 3) Don't post questions regarding breaking or bypassing any security
 measure.

 4) Acknowledge the responses you receive, good or bad.

 5) Cross-promotion of, or links to, forums competitive to this forum in
 signatures are prohibited.

 NOTE : Don't ever post personal or confidential data in a workbook.
 Forum owners and members are not responsible for any loss.


 --
 To post to this group, send email to excel-macros@googlegroups.com

>>>
>>>
>>>
>>> --
>>> Thanks & regards,
>>> Noorain Ansari
>>>  
>>> *http://excelmacroworld.blogspot.com/*
>>> *http://noorain-ansari.blogspot.com/*
>>>
>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> --
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> --
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>
>  --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use conci