Dear Don have forwarded the file to you
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedi
Hi,
Further to Sam's solution, here is the complete code
Sub converToDate()
Dim sht As Worksheet
Dim rng As Range
Dim r1 As Integer, r2 As Integer
For Each sht In ThisWorkbook.Sheets
Set rng = sht.Range("A1")
Do While UCase(Trim(rng.Value)) <> "DATE"
Se
Well, actually, use
Range("A3:A6").TextToColumns Destination:=Range("A3"), DataType:=1,
TextQualifier:=1, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
Sorry for posting without testing the previous one.
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 6, 2011 at 10:57 AM, Sam Mathai Chacko w
Range("A3:A6").TextToColumns Range("A3"), 0, 1, False, True, False, False,
False, False, Array(1, 4), True
Regards,
Sam
On Thu, Oct 6, 2011 at 10:56 AM, B.N.Chethan kumar <
chetankumar1...@gmail.com> wrote:
> Hi,
>
> As the data volume is high... i will have 100 to 200 sheets in a
> month..henc
Hi,
As the data volume is high... i will have 100 to 200 sheets in a
month..hence take more time
Can we write any macro to convert for all sheet in the work book.
Thanks Asa Rossoff...i will try below option.
Regards
Chethan Kumar BN
On Thu, Oct 6, 2011 at 10:48 AM, Asa Rossoff wrote:
>
Hi,
Solution given by Sam is better than using loop.
Regards,
Swapnil.
On Wed, Oct 5, 2011 at 11:24 PM, Sam Mathai Chacko wrote:
> You could do it without a loop. As indicated by Hariharan, selecting all
> sheets and then adding the code will do the trick. For a VBA, paste this to
> the code
Hi B.N Chetan kumar,
Try this...
=--SUBSTITUTE(A3," ","-")
Or
=IF(ISERR(--SUBSTITUTE(A3," ","-")),"",--SUBSTITUTE(A3," ","-"))
Hope that helps!
---
Ms.Exl.Learner
---
On Thu, Oct 6, 2011 at 10:20 AM, B.N.Chethan kumar <
chetankumar1...@gmail.com> wrote
Hello B.N.Chethan,
Here are two methods courtesy of
http://www.ozgrid.com/Excel/convert-true-time-date.htm
1. Option 1: Copy an empty cell, then highlight the cells (or entire
column(s)) containing your textual date/times, then Paste Special>Add. The
add operation forces Excel to convert t
Thanks SAM, for prompt response...currently i using the same option to
convert date and text to columns option for time...
Due to data size...it is taking more time.
Regards
Chethan Kumar BN
On Thu, Oct 6, 2011 at 10:43 AM, Sam Mathai Chacko wrote:
> =DATEVALUE(LEFT(A3,2)&"/"&MID(A3,FIND(" ",A3
Hi,
Any idea about all these reports?
Daily CVA Risk Reports
Consolidated GRC Risk Report
BCM Risk Reports
Hedge Report & Monthly Risk Reconciliation
o Generate PNL reports:
GRC CVA Risk-based P&L Predicts
Public Finance CVA Report
DPCs & Monolines CVA Report
o Explain the da
=DATEVALUE(LEFT(A3,2)&"/"&MID(A3,FIND(" ",A3)+1,3)&"/"&RIGHT(A3,4))
Regards,
Sam Mathai Chacko (GL)
On Thu, Oct 6, 2011 at 10:20 AM, B.N.Chethan kumar <
chetankumar1...@gmail.com> wrote:
> Hi All,
>
> I have attached file where i get some data in text format. I need convert
> to date and time f
Hi All,
I have attached file where i get some data in text format. I need convert to
date and time format.
Currently using text to coloumn method which is manually pocess. As sheet
count is high in a workbook, these is very painful work to do in month end.
Kindly request your help to very painf
Thank excel-macros@googlegroups.com gave me somequestions and answers
messages, Thank you
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailyt
Thanks sam first of all,for the kind reply.The code u posted is not the same
i m looking for.I had explained what i m in need of...Kindly Read my last
two updates & help me..
thanks...
On Thu, Oct 6, 2011 at 8:57 AM, Sam Mathai Chacko wrote:
> Isn't that what the code I posted does?
>
> Sam
>
>
Isn't that what the code I posted does?
Sam
On Thu, Oct 6, 2011 at 8:52 AM, Mr excel wrote:
> Any ideas please..:?
>
>
> On Wed, Oct 5, 2011 at 5:30 AM, Mr excel wrote:
>
>> ADDITION: i just need the raw data to be exported to the respective sheets
>> based on REP column.No need of calculation
Any ideas please..:?
On Wed, Oct 5, 2011 at 5:30 AM, Mr excel wrote:
> ADDITION: i just need the raw data to be exported to the respective sheets
> based on REP column.No need of calculations of any kind.Hope i made it
> clear...
>
>
> On Wed, Oct 5, 2011 at 5:28 AM, Mr excel wrote:
>
>> I m in
Hi ChilExcel,
I'm glad you have a good solution.
I did not understand the need for a VBA message box when Data Validation
also generates a dialog with it's Error Alert options, but perhaps it is
because you need a custom message depending on what data is duplicated? If
so, I see your point!
Dear All,
I am very soory if I have Hijacked any ones thread. I have just replied to
the to the mail received by mesent by others congratulating Ayush on his
achievement . I have done so because it is the easiest way of getting reply
from the group and while using reply to earlier mail you cannot
Congratulations Ayush Jain
chilexcel
2011/10/5
> Dear sir the same problem is faced by all of us who r useing data to export
> in excel we can not use 'dr' as filter command as when we go to the cell
> which contains numbers it only shows no. Not number with the term 'dr' or
> 'cr'.
> It becom
Thanks Asa, but I need to launch MsgBox Alert
I have used function and runs well .. I have no problem
Thanks good contribution
2011/10/5 Asa Rossoff
> Hi Chil,
>
> I didn't realize you were looking for a pair of cells matching to determine
> duplicates when I suggested the data validation r
Jai
another excellent solution provided by Rajan Berma
Try this :
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err:
Dim lngValueAs Long
Dim intICounter As Integer
Dim strMsg As String
Dim lngValue2 As Long
lngValue = Target.Value
lng
excellent solution, launching the message showing the repeat value
thank you very much for your time and attention Rajan
Chilexcel
2011/10/5 rajan verma
> Try this :
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error GoTo Err:
> Dim lngValueAs Long
> Dim i
I don’t think so. Attach here or send to dguillett1 @gmail.com
Don Guillett
SalesAid Software
dguille...@gmail.com
From: shaneallen
Sent: Wednesday, October 05, 2011 6:20 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Working out of Statistics on Exam Results URGENT
solutio
Hi Don
Did u get my file. If not can u please give me an addy where I can post it
to.
Thanx
shaneallen
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://tw
Can I use indirect function as a formula in my case! Please suggest.
Sent on my BlackBerry® from Vodafone
-Original Message-
From: Bé Trần Văn
Sender: excel-macros@googlegroups.com
Date: Thu, 6 Oct 2011 01:35:52
To:
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$
2011/9/30 NOORAIN ANSARI
> Dear Nalini,
>
> See attached sheet if it help to u.
>
> On Thu, Sep 29, 2011 at 6:23 PM, N Pradhan
> wrote:
>
>> **
>> Dear Noorain Sir,
>>
>> I am having some problem in the attached pivot.
>>
>> I want a calculated field (count of name - count of absent) which is
You could do it without a loop. As indicated by Hariharan, selecting all
sheets and then adding the code will do the trick. For a VBA, paste this to
the code module of the first sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then
Target.Select
If you need prompts, and if the files are in different folders, then
Application.GetOpenFileName (my earlier code uses it) can be helpful
Regards,
Sam
On Wed, Oct 5, 2011 at 11:06 PM, Sam Mathai Chacko wrote:
> Just take care of the sheet activation also. If you are going with the
> loop, then
Just take care of the sheet activation also. If you are going with the loop,
then use Sheets("List" & i).activate
Regards,
Sam
On Wed, Oct 5, 2011 at 10:32 PM, dguillett1 wrote:
> That can be done but it's probably easier to know the folder you need and
> modify my idea to suit
> Sub GetDataSA
You have option explicit at the top so you need to declare variables.
dim lr as long
Attach or send me your file if necessary
From: SAJID MEMON
Sent: Wednesday, October 05, 2011 11:20 AM
To: Excel Group
Subject: $$Excel-Macros$$ Project Part Error
Dear Dguillett1 & Roshan Mathew,
[ Dguille
That can be done but it's probably easier to know the folder you need and
modify my idea to suit
Sub GetDataSAS()
'DATA_20110101, DATA_20110201
myfolder = "yourdriveletter:\yourfoldername\" 'these variables could be
entered in a cell
startname = "DATA_20110"
endname = "01"
For i = 1 To 3
mys
I'm sure you can modify this...
Sub uvoz_podatkov()
'
' uvoz_podatkov Makro
'
'
Dim strFile As String
strFile = Application.GetOpenFilename("Comma Separated Value Files
(*.csv), *.csv", , "Select first file", , False)
If strFile = "False" Then
Exit Sub
End If
With Act
Thanks a lot for all of you Mahesh, Rajan & Sam
Regards,
Shrinivas
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of rajan verma
Sent: Tuesday, October 04, 2011 9:22 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Exc
The only formula that is 'LIKE' indirect, is indirect itself. Don has also
suggested the same. Are you indirectly asking 'HOW' to apply an INDIRECT
formula?
Sam
On Wed, Oct 5, 2011 at 8:40 PM, wrote:
> I don't have any worksheet readily available with me now.
> But I am trying to explain it wit
Thank you for your help, however, if I understand your suggestion
correctly, this would still open files from one and only location? In
my case, the source files are in different locations every month and
have different endings in their names: DATA_20110101, DATA_20110201,
etc...
That is why I nee
Select all the sheets first and then write a formula wherever you need
in Excel.
The formula will be coming in all the sheets.
Thanks,
Hariharan Tharagan
-Original Message-
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of renuka chari
Sent: Tuesday,
Thanks for sending messages to me.
These references are very useful. Thank you very much
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailyti
I don't have any worksheet readily available with me now.
But I am trying to explain it with example:
I have a worksheet 1 with below mentioned sheets:
Annex 1
Annex 2
And so on...
I have anothe sheet say worksheet 2 with same name sheets viZ
Annex 1
Annex 2
And so on...
Now in annex 1 of works
It seems to me that, depending on the amount of data per student, that you
could have one file with the data for each student in columns and then make a
report in the same file for each student as needed. See simple sample
From: shashank bhosle
Sent: Wednesday, October 05, 2011 12:47 AM
To: exc
The example you provide makes it fairly easy
for i= 1 to 3
Sheets("List" & 1).QueryTables.Add(Connection:= _
"TEXT;Path to my file" & I &".csv", Destination:= sheets("List" &
1).range("a1")
'etc
next i
However, you can probably do it with ONE query table using the variables and
have your mac
I, for one, still do not understand your requirement. Perhaps attaching a
file or two would be helpful.
-Original Message-
From: chhajersand...@gmail.com
Sent: Wednesday, October 05, 2011 9:17 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Linking same format sheet
Dear all
actually I get daily sheet from branches (they send day wise) and need to
update all my annexures (I need it to be kept for every day with the same
format) accordingly.
So please suggest any other furmula if indirect dose not work.
Sent on my BlackBerry® from Vodafone
-Original M
Thank you very much Experts
for co-operate me in my project
regards
Sajid Memon
+91-9437638986
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://
Indirect may only be used with OPEN workbooks.
Instead, have your formula to the closed workbook and use edit/replace to
modify formulas.
-Original Message-
From: chhajersand...@gmail.com
Sent: Wednesday, October 05, 2011 7:16 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Mac
Doubt 1. See attached
From: SAJID MEMON
Sent: Wednesday, October 05, 2011 3:23 AM
To: Excel Group
Subject: $$Excel-Macros$$ Project Part
Dear all experts,
I have 3 different types of doubt in my project. Need your help to accomplish
my project . I have attach a part of that project. please
Try this :
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err:
Dim lngValueAs Long
Dim intICounter As Integer
Dim strMsg As String
Dim lngValue2 As Long
lngValue = Target.Value
lngValue2 = Target.Offset(0, -1).Value
If Intersect(Target
Hi,
Do You want something Like this :
Sub MakeComponentinRunTImeOnSpreadSheet()
On Error Resume Next
Dim cb As OLEObject
n = 5
For i = 1 To 10
Set cb = ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1")
With cb
.Top = n
.Object.Caption
See if it helps :
https://sheet.zoho.com/excelviewer
On Tue, Oct 4, 2011 at 9:33 AM, syed aliya raza hashim
wrote:
> Can excel file such as reports be access from web or can be acccess as web
> pages suppose we have 10 sheets but from some url we want to see the reports
>
> --
> syed aliya
>
> -
Dear sir the same problem is faced by all of us who r useing data to export in
excel we can not use 'dr' as filter command as when we go to the cell which
contains numbers it only shows no. Not number with the term 'dr' or 'cr'.
It becomes a cellformat as like of currency sign with number in exce
You should never “hijack” a thread to ask a question. Instead, start your own
with a new subject line. However, can you not use data>autofilter>custom>ends
with DR?
From: P.VIJAYKUMAR
Sent: Tuesday, October 04, 2011 8:22 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Thanks
Dear excel gurus
I need to link two work sheet with same format.
Like I have to link annexure 1 (name of a sheet) with corresponding figure in
another workbook where also the sheet named annexure 1(I have 25 sheets with
name annexure 1 to 25 and have to link all the sheets with corresponding na
hi Suman,
check the below mentioned link
sanjib
http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx
On Wed, Oct 5, 2011 at 3:12 PM, Suman wrote:
> Hi Group
> I have multiple data in column A (Alphabet with numeric values) n i want to
> s
Hi Group
I have multiple data in column A (Alphabet with numeric values) n i want to
separate them?
Available Data
*A*
suman1456 Sam221 Manni457
I want like this:
suman 1456 Sam 221 Manni 457
--
Suman Kumar
Mob: +91 9810333884
--
--
Hi all,
I have to import data from 3 different files into 1 and format the
data. Since everything is more or less fixed, I have recorded the
macro below and removed the code for scrolling and such. The only
problem for me is that each month the locations and filenames of the
files to import are ch
Dear all experts,
I have 3 different types of doubt in my project. Need your help to accomplish
my project . I have attach a part of that project. please co-operate me.
Awaiting your positive reply,
Regards
Sajid Memon
+91-9437638986
--
--
Or (slaps head) --
Just use:
=countifs($B$2:$B$1000,$B2,$C$2:$C$1000,$C2)=1
Asa
From: Asa Rossoff [mailto:a...@lovetour.info]
Sent: Tuesday, October 04, 2011 9:40 PM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row
Hi
Hi Chil,
I didn't realize you were looking for a pair of cells matching to determine
duplicates when I suggested the data validation rule using countif.
You can modify that data validation rule using a sumproduct formula that
will search for that pair of data.
This validation rule works fo
57 matches
Mail list logo