try attached
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unkno
ately appreciated.
Thanks & Regards,
Alokeshwar Tiwary
+1 617 337 2256_"There are known knowns. These are things we know that we know. There are known unknowns. That is to say, there are things t
Add following line after .To line:
.Cc = "addre...@email.com, addre...@email.com, addre...@email.com"
You can replace contents after = wih reference to cells containing cc addresses.
.Cc = ActiveSheet.Range("f" & i).Text & ", " & ActiveSheet.Range("g" & i).Text
You should also replace For i
Use following formulat in data validation:
=COUNTIF($E:$E,$E1)=1
Please see attached for example.
Regards
Alokeshwar
09891442001
_
"There are known knowns. These are things we know that we know. Ther
Add following code before the line:
Worksheets("Ratios").Select (to select the sheet before you select the
range in the sheet)
New code should look like:
Private Sub CommandButton3_Click()
Worksheets("Ratios").Select
Worksheets("Ratios").Range(Cells(3, 25), Cells(3, 26)).Select
End Sub
Try this:
Sub test1()
Dim i As Integer, str1 As String, str2 As String
For i = 2 To 4
str1 = "Sheet1!C" & i & ":D" & i'Replace Sheet1 and Sheet2 with the sheet
names in your workbook
str2 = "Sheet2!C" & i & ":D" & i'Replace "!C" and "!D" with column names.
Range(str1).Copy Range(str2)
PFA the workbook with the solution.
Regards
Alokeshwar
09891442001
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we
Ashish,
It's absoulutely possible. Can you attach a few word files for example.
Regards,
Alokeshwar
09891442001
_
"There are known knowns. These are things we know that we know. There are known
unk
Consider using INDIRECT to refer to ranges. You can also use ADDRESS with ROW
and COLUMN functions with INDIRECT.
Regards,
Alokeshwar
9891442001
_
"There are known knowns. These are things we know t
gs we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________
From: Alokeshwar Tiwary
To: excel-macros@goog
additional trouble for forgetting to mention the
>Excel Version I was using.
>
>Thanks a lot. Many Many Many times over. You are a lifesaver :)
>
>
>Thanks once again.
>
>
>Ashish
>
>On Wed, Jan 6, 2010 at 4:52 AM, Alokeshwar Tiwary <
>alokeshwar.tiw...@yahoo.
know. But
there are also unknown unknowns. There are things we don't know we don't know."
____
From: Zilla
To: Alokeshwar Tiwary
Sent: Sun, 10 January, 2010 2:51:33 AM
Subject: Re: $$Excel-Macros$$ Export from word to excel
I like the the first ex
Please share the workbook?
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also
ubject: Re: $$Excel-Macros$$ Export from word to excel
thanks a lot for the solution can u please make it for notepad files too
On Sun, Jan 10, 2010 at 3:02 PM, Alokeshwar Tiwary
wrote:
Criteria or parameters aren't necessory but it may not be extracting data in a
useful format.
>I h
you can use following function to get date range instead of a vlookup:
Function GetDateRange(Day1 As Date, CurrentDay As Date) As String
Dim i As Integer, LeftRange As Date, RightRange As Date
If Day1 > CurrentDay Then
DateRange = "#INVALID#"
Exit Function
End If
i = Application.Workshee
I believe below is what you should use:
Range("ca5") = "abc" & Range("n" & i)
Regards,
Alokeshwar
+1 617 710 8441
_
"There are known knowns. These are things we know that we know. There are known
unk
I would suggest that you use JitBit macro recorder for this purpose.
www.jitbit.com
Regards,
Alokeshwar
+1 617 710 8441
_
"There are known knowns. These are things we know that we know. There are know
62, India
On 1/30/10, Alokeshwar Tiwary wrote:
I would suggest that you use JitBit macro recorder for this purpose.
>www.jitbit.com
>
>Regards,
>Alokeshwar
>+1 617 710 8441
>
>_
>
PFA from vbaexpress.com
Regards,
Alokeshwar
+1 617 710 8441
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know w
1. Start Pivot Table Wizard and select external data source.
2. You can also use DAO to query data from database:
Example procedure is as mentioned below. Remember add reference to DAO in VBA
reference library.
Function GetDBData()
Dim db As Database, rs As Recordset
Set db = OpenDatabase(Curren
The Law of the Garbage Truck™ by David J. Pollay
How often do you let other people’s nonsense change your mood? Do you let a
bad driver, rude waiter, curt boss, or an insensitive employee ruin your
day? Unless you’re the Terminator, you’re probably set back on your heels.
However, the mark of yo
that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________
From: Alokeshwar Tiwary
To: alokeshwar.tiw...@yaho
Opening Power Point is easy:
Sub OpenPPT()
Application.ActivateMicrosoftApp xlMicrosoftPowerPoint
End Sub
_
"There are known knowns. These are things we know that we know. There are known
unknowns.
ion
To: "MS Excel & VBA Macros"
Date: Friday, 29 August, 2008, 1:15 PM
Thanks for your help.
i have one more question...
i just want to browse folders and select one excle file, without any
predefined file name in program itself.
Thanks,
GK
On Aug 28, 5:34 am, Alokeshwar Tiwary <[
Ravi, here is a tip from J. Walk
VBA does not include a method to retrieve a value from a closed file. You can,
however, take advantage of Excel's ability to work with linked files.
This tip contains a VBA function that retrieves a value from a closed workbook.
It does by calling an XLM macro.
Att. [EMAIL PROTECTED]
Can you please send the files for example?
Regarding the file name change issue you can modify following code to variable
FileName whereever your want.
Sub GetFileName()
Dim FileName As String
Dim Msg As String
FileName = Application.GetOpenFilename
Msg = "You selected: "
why do u need a macro for the same? simply set a filter to delete all such
messages..
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, ther
why do u need a macro for the same? simply set a filter to delete all such
messages..
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, ther
Att. [EMAIL PROTECTED]
Place below code in your sheets personal module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
If Application.Calculation = xlCalculationManual Then
Msg = MsgBox("Sheet has changed. Do you want to recalculate?", vbYesNo +
vbDefaultButton1)
If Msg
U need to write the macro in a compile sheet in followint sequence:
1. Have two lists in compiler sheet with names of those 2 x 300 books in two
columns together (say A1 for datas1, B1 for datag1, A2 for datas2, B2 for
datag2...)
2. Write a for next loop to pick values for those files:
For
Place this code in private module of the worksheet:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If ActiveCell.Address = "$C$3" Then
Cancel = True
Call MyMacro
End If
End Sub
As soon as user double clicks the Cell C3, it cancels the click action and
call
4004308
New * Email ID: [EMAIL PROTECTED]
From: excel-macros@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of
Alokeshwar Tiwary
Sent: Friday, September 12, 2008 4:17 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: Make a cell a control that launches macro
whenclicked
Checkout indirect function in excel help.
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
Sub Macro1()
Dim sht
For sht = 1 To Sheets.Count
Sheets(sht).Activate
ActiveWindow.Zoom = 100
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 9
End With
Range("A1").Select
Next sht
End Sub
_
Attention Vinu
Here is the code:
Sub Macro1()
Dim sht
For sht = 1 To Sheets.Count
Sheets(sht).Activate
ActiveWindow.Zoom = 100
Cells.Select
With Selection.Font
.Name = "Arial"
.Size = 9
End With
Range("A1").Select
Next sht
Sheets(1).Activate
End Sub
___
Use below code.
Sub DupFinder()
Dim r As Range, t As Range
Cells(1, ActiveCell.Column).Select
Range(Selection, Selection.End(xlDown)).Select
Set t = Selection
For Each r In t
v = r.Value
If Application.WorksheetFunction.CountIf(t, v) > 1 Then
r.Interior.ColorIndex = 3
End If
Next
End Sub
Select
There is a simple formula for column F:
=IF((B3-TODAY())/7>1,(B3-TODAY())/7,ROUNDUP((B3-TODAY())/7,0))
Then custom format the range with this code
"Payment due in "0" weeks";"Overdue as of today"
_
Use CountIf function. see attached file for example. Any cells having value
more than 1 in column B has a duplicate record on its left.
_
"There are known knowns. These are things we know that we kno
right click on cell and go to Format -> Protection tab.. if locked box is
checked, cell will locked after protecting the sheet. If hidden box is checked,
cell formula will be hidden (i.e. will not show in formula bar) after
protecting the sheet. To lock a range, select your range and then go to
Use below functions:
For date:
=TEXT(A1,"mm/dd/")
For time:
=TEXT(A1,"hh:mm:ss AM/PM")
A1 is the cell where the data (date and time) is stored.
_
"There are known knowns. These are things we kn
Dave, see attached workbook. May be this will help.
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't
=Countif(B:B,A2)
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there are also unknown u
Go to Workbook's private module and type this code in Open Workbook event:
If Environ("UserName") <> "USERNAME HERE" Then
'your code here
ThisWorkbook.close ' if you want to close the workbook
End If
I am not sure what the problem is. I am using excel 2003 and its working fine..
can you forward your workbook as attachment.
_
"There are known knowns. These are things we know that we know. There ar
Go to Data -> Validation - > and select validation criteria as allow list...
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things
Try this link:
http://www.orbitfiles.com/download/id2177479820.html
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that w
Lets stop this topic and continue sharing excel-vba tips, problems and
solutions.
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are
Naveen Kumar M N
Transport Supervisor(HP)
91-08-99728 10886
Email :[EMAIL PROTECTED]
Best Wishes-Karunaada Kuvara
--- On Tue, 12/2/08, Alokeshwar Tiwary <[EMAIL PROTECTED]> wrote:
From: Alokeshwar Tiwary <[EMAIL PROTECTED]>
Subject: $$Excel-Macros$$ Using VBA Macro as an Altern
Sub test()
For r = 1 To Workbooks.Count
If Right(Workbooks(r).Name, 12) = "practice.xls" Then
Workbooks(r).Activate
Exit For
End If
Next r
End Sub
_
"There are known knowns. These are things we know t
Sub test()
For r = 1 To Workbooks.Count
If Right(Workbooks(r).Name, 12) = "practice.xls" Then
Workbooks(r).Activate
Exit For
End If
Next r
End Sub
_
"There are known knowns. These are things we know t
Try below function
Function FindX(ValueX)
'Row Number
FindX = Columns("A:A").Find(What:=ValueX).Row
'Column Number
'FindX = Columns("A:A").Find(What:=ValueX).Column
'Cell Address
'FindX = Columns("A:A").Find(What:=ValueX).Address
End Function
_
or this one:
Function FindX(RangeX As Range, ValueX As String)
'Row Number
FindX = RangeX.Find(What:=ValueX).Row
End Function
_
"There are known knowns. These are things we know that we know. There a
I want to create a userform to save data in a closed excel workbook using
database. The closed workbook will have many sheets and I want the VBA to
select worksheet based on windows NT user ids. Worksheets' names will be same
as username. Any suggestions?
Thanks
Alokeshwar
___
go to tools - > options -> calculation and and turn Manual calculation on.
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things
Is there a way to retrieve domain name using vba...? I need this to make sure
that my vba tool will run only within the network of my compay.
Thanks and Regards,
Alokeshwar Tiwary
_
"There are
Is there a way to retrieve domain name using vba...? I need this to make sure
that my vba tool will run only within the network of my compay.
Thanks and Regards,
Alokeshwar Tiwary
_
"Ther
omain name using VBA
Hi Alok,
Try this command :
Environ("UserDomain")
Cheers,
Lohith
On Jan 6, 10:16 am, Alokeshwar Tiwary
wrote:
> Is there a way to retrieve domain name using vba...? I need this to make sure
> that my vba tool will run only within the network of my compa
Ayush, please find the find attached file.. let me know if you need to crack
vba password for a word doc
_
"There are known knowns. These are things we know that we know. There are known
unknowns. T
See attached, this might help you.
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. But
there a
Hi,
I am trying to populate an excel userform combo box from a table in access
database using DAO.
When I pass arguments as following:
AddItemtoDropDownList Me.cboUserName, "LookupLists", "UserId"
VBA returns following error:
===
Run-time error '-2147352571 (80020005)
Type M
ow we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________
From: Alokeshwar Tiwary
To: excel-macros@googlegroups.com
Sent: Monday, 9 February, 2009 5:52:54 AM
Subject: $$Excel-Macros$$ Run-time err
7;-2147352571 (80020005) - Type
Mismatch
So why you don't want to share with group mates???
- Original Message -
From: Alokeshwar Tiwary
To: excel-macros@googlegroups.com
Sent: Monday, February 09, 2009 2:46 PM
Subject: $$Excel-Macros$$ Re: Run-time error '-21
Go to Tools -> Options - > View Tab
Select checkbox Formula in Window Options. Click OK
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, th
I received this email from some so-called moderator.. the email was a total
non-sense.. i didn't spam the group, I just replied to a member's query.
Anyone who uses excel could understand the email and the attachment...
Here is the content of the excel sheet I attached. The excel sheet contai
Could you share the workbooks your team members are using?
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we
See attached. I have attached a macro that might help you.
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we d
gs that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
From: shay shay
To: Alokeshwar Tiwary
Sent: Wednesday, 11 March, 2009 8:32:02 PM
Subject: Re: $$Excel-Macros$$ Re: charts
Just thought of sharing this function with the group. I created it to elaminate
lengthy match index formula from my reports
_
"There are known knowns. These are things we know that we know. There are k
.
It is a good function but proper usage is not clear from ur example.
On Tue, Mar 17, 2009 at 7:43 AM, Alokeshwar Tiwary
wrote:
Just thought of sharing this function with the group. I created it to elaminate
$ Re: MS Excel -Animation
Hi Alokeshwar,
Whtat does [F17] mean here? what is it for?
On Mar 29, 1:52 pm, Alokeshwar Tiwary
wrote:
> Though it looks like flash or something but its not. There are several
> picture in each sheet which become visible and invisible one after another.
>
See below. this freeware clicks yes
automatically:http://www.snapfiles.com/get/clickyes.html
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say
Sub SelectFirstEmptyRow()
Dim r As Long
r = Application.WorksheetFunction.CountA(Range("A:A"))
Cells(r, 1).Select
End Sub
_
"There are known knowns. These are things we know that we know. There are kn
Hi All,
Is there a way to change color of a message box back ground and font?
Please see attached. I want the OK message box to be in same color as the
userform.
Thanks in anticipation
Alokeshwar
x27;t know. But
there are also unknown unknowns. There are things we don't know we don't know."
- Forwarded Message ----
From: Alokeshwar Tiwary
To: excel-macros@googlegroups.com
Sent: Sunday, 17 May, 2009 2:38:40 AM
Subject:
Hi All,
Is there a way to change color of a me
Hi All,
Does anyone know why this error occurs? I am using DAO in a Excel Userform to
save data in MS Access. Approx 40 users are using the userform and its working
fine however sometimes it shows following error message and crashes:
Run Time Error 3050 - Could Not Lock File
What could be the r
http://www.contextures.com/xlUserForm01.html
_
"There are known knowns. These are things we know that we know. There are known
unknowns. That is to say, there are things that we know we don't know. B
e are things that we know we don't know. But
there are also unknown unknowns. There are things we don't know we don't know."
________
From: Alokeshwar Tiwary
To: excel-macros@googlegroups.com
Sent: Saturday, 6 June, 2009 7:07:58 AM
Subject: $$Excel-Macros$
---
Regards
Ashish Jain
http://www.excelitems.com
http://www.openexcel.com
*Developer of Open XL*
-
On Jun 6, 3:11 pm, Alokeshwar Tiwary
wrote:
> Dear All,
>
> I encountered a very strange problem. I have two userforms and both of them
> def
---
Regards
Ashish Jain
http://www.excelitems.com
http://www.openexcel.com
*Developer of Open XL*
-
On Jun 6, 3:11 pm, Alokeshwar Tiwary
wrote:
> Dear All,
>
> I encountered a very strange problem. I have two userforms and both of them
> def
---
Regards
Ashish Jain
http://www.excelitems.com
http://www.openexcel.com
*Developer of Open XL*
-
On Jun 6, 3:11 pm, Alokeshwar Tiwary
wrote:
> Dear All,
>
> I encountered a very strange problem. I have two userforms and both of them
> def
80 matches
Mail list logo
