Hi,
Try this:
Range(C CStr(LCurSRow)).Formula = =COUNTIF(Dept!AR CStr(lRow)
:CE CStr(lRow) ,0)
Regards = Dave.
Date: Wed, 23 Sep 2009 17:12:13 -0700
Subject: $$Excel-Macros$$ Help with quotes in COUNTIF formula
From: cad...@gmail.com
To: excel-macros@googlegroups.com
Hi
Hi,
If you attach a sample file, I think we can help you.
Regards - Dave.
Date: Wed, 23 Sep 2009 14:33:11 -0700
Subject: $$Excel-Macros$$ Substring across a range of cells
From: sjharri...@btinternet.com
To: excel-macros@googlegroups.com
I have a spreadsheet populated with game score
Hi Alex,
Not sure if this is helpful, but it's something to consider.
A macro can identify the button that called it, and the text on that button.
So you can have just one macro, with lots of different buttons calling it. The
code within the macro can do different things depending on which
2009 15:20:24 +0100
ah hi see, I think its my fault for not explaining properly, ill email you a
copy off the sheet to let you see, you will see the form called farm take a
look at that J
alex
From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On
Behalf Of Dave
Hi Anish,
I also think it's just a mis-count on the End If's
You can simplify your code like this.
Sub dept()
i = 1
z = Cells(Rows.Count, B).End(xlUp).Row
While i = z
If Cells(i, 2).Value Like *FINANCE* Then Cells(i, 4) = FINANCE /
ACCOUNTING: GoTo 100
If Cells(i,
of ENDIF??
Cheers!!
ANISH
On Mon, Oct 26, 2009 at 2:23 PM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Anish,
I also think it's just a mis-count on the End If's
You can simplify your code like this.
Sub dept()
i = 1
z = Cells(Rows.Count, B).End(xlUp).Row
While i = z
Hi Simon,
If you post your current code, someone can probably suggest the necessary mods.
Regards - Dave
Date: Tue, 27 Oct 2009 08:12:32 -0700
Subject: $$Excel-Macros$$ New user VBA to help random sheet
From: simongall...@googlemail.com
To: excel-macros@googlegroups.com
Hello,
I
Hi Bonnie,
This is definitely a job for the SUMPRODUCT function, which is a wizz at doing
just what you are asking about.
If you post a sample of your data, there would be many is this group that could
help you with it.
Regards - Dave.
Date: Thu, 5 Nov 2009 11:36:35 -0800
Subject:
Hi Dellosa,
See if the attached file gives you what you need.
Regards - Dave.
Date: Fri, 6 Nov 2009 10:20:53 +0800
Subject: $$Excel-Macros$$ Fwd: FW: lookuphelplease.xls
From: osav...@gmail.com
To: excel-macros@googlegroups.com
Dear Gurus,
Im trying to look up a value from a
You're welcome
Dave.
Date: Mon, 9 Nov 2009 09:20:53 +0800
Subject: $$Excel-Macros$$ lookuphelp
From: osav...@gmail.com
To: excel-macros@googlegroups.com
Dear Dave and Dilip,
The solution you suggested where out of the box. Its genuis, While I solved it
by naming uniquely each rows, and
By the way, if you have XL2007, you can shorten the formulas considerably using
the IFERROR function.
Dave.
Date: Mon, 9 Nov 2009 09:20:53 +0800
Subject: $$Excel-Macros$$ lookuphelp
From: osav...@gmail.com
To: excel-macros@googlegroups.com
Dear Dave and Dilip,
The solution you suggested
Hi Anil,
Do you want the value in the same cell as the one you write the name in?
If so, do you want the value to replace the name? (ie, Anil automatically
changes to 100)
Or do you want the value added to the name? (ie, Anil automatically becomes
Anil 100)
Regards - Dave.
Date: Thu, 19
Hi,
The attached uses a macro to do your bidding. But I think XL2007 can do this
without macros, but I only have XL2003.
Regards - Dave
Date: Thu, 19 Nov 2009 12:45:00 +0530
Subject: Fwd: $$Excel-Macros$$ How to sum on the basis of color
From: manojsnegi.uttranc...@gmail.com
To:
Hi Pooja,
Does the attached do what you need?
Regards - Dave.
Date: Thu, 19 Nov 2009 13:35:28 +0530
Subject: $$Excel-Macros$$ Require help on a condition
From: vatspoojav...@gmail.com
To: excel-macros@googlegroups.com
Hi All,
I've attached a file in which, a data is mentioned with the
-macros@googlegroups.com
Thank you very much Dave.
Can you suggest some clue how to do the same think in excel 2007 without macro.
Unfeigned Regards
Manoj S Negi
Skype - manojsnegi
-- Forwarded message --
From: Dave Bonallack davebonall...@hotmail.com
Date: Thu, Nov 19, 2009 at 1
Hi, If you can open the file in Read-only mode, then do so, make the changes
you want, then Save As whatever name you want. Then just delete the original.
Regards - Dave
Date: Fri, 20 Nov 2009 17:59:46 -0800
Subject: $$Excel-Macros$$ Urgent Help Needed
From: sheetallakho...@gmail.com
To:
Hi Mike,
Try this code. It may be quicker. But it won't go to the end of Col B if Col B
has any blank cells. Let me know if this is a problem.
Sub NumColA()
Columns(A:A).Insert Shift:=xlToRight
Range(A1) = 1: Range(A2) = 2
Range(A1:A2).AutoFill Destination:=Range(Cells(1, 1),
!
On Nov 21, 12:00 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Mike,
Try this code. It may be quicker. But it won't go to the end of Col B if
Col B has any blank cells. Let me know if this is a problem.
Sub NumColA()
Columns(A:A).Insert Shift:=xlToRight
Hi Om,
Have a look at the attached. It uses a macro to sum your column, based on the 2
different formats you've used.
I don't know how to do this with worksheet functions, but we could add an event
macro that re-sums every time there is a change in the data, if you need. Or
you can just do it
Hi,
If you double click in the first cell, then press Enter, the cell is displayed
as you require.
You can either do this in each cell manually, or you can write a macro to do it
for you.
Regards - Dave.
Date: Tue, 24 Nov 2009 12:58:32 +0530
Subject: $$Excel-Macros$$ Change Date Format
From:
that every cell
without a value is always going to be a Null / Blank only ... you could extend
this formula to look at more columns although you'd maybe have to be careful
with the number of OR's used.
Luke
On 24 Nov 2009, at 09:01, Dave Bonallack wrote:
Hi Steve,
If you use the suggested =B4C4
-macros@googlegroups.com
I'm really wondering why are we using custom number formating and vba
for this task.
On Nov 24, 3:49 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Om,
Have a look at the attached. It uses a macro to sum your column, based on
the 2 different formats
differentiate
between pos., neg, and zero values
On Nov 24, 10:21 pm, Dave Bonallack davebonall...@hotmail.com wrote:
Hi yalters,
I couldn't see any other way of doing it, considering the format of the
original data. But I'm open to anything (simpler) that works.
Regards - Dave
I forgot to say that the formula is extended to the last row of data when a
Name is entered into Column A, and a number entered into Col B
Regards - Dave.
Date: Wed, 25 Nov 2009 21:48:40 +0530
Subject: $$Excel-Macros$$ Fwd: Copy the formula till end of data without using
copy paste every
Hi Jitesh,
Format the cells as 'Text' before entering your data.
However, if your data is imported, or if you are pasting it from another place,
it may bring formatting with it.
If you are pasting it from another place, use Paste Special, then Values. This
will prevent the pasting of unwanted
Hi Try this code:
Sub AddFormula()
Range(A6) = Range(A6).Formula /SUMPRODUCT((C4:C8=B1)*(D4:D8=B2))
End Sub
Regards - Dave.
Date: Fri, 27 Nov 2009 14:46:15 -0800
Subject: $$Excel-Macros$$ Add to an Existing formula in a Cell using VBA
From: stvn.tay...@gmail.com
To:
Hi Pete,
You can shorten your current code to:
Worksheets(Sheet1).Activate
ActiveCell = Interest
ActiveCell.Offset(-1, 3).Copy ActiveCell.Offset(1, -1)
ActiveCell.FormulaR1C1 = =R[-1]C+RC[-1]*0.1/12 'need to make non absolute
ActiveCell.Offset(rowOffset:=1, columnOffset:=-3).Activate 'next
% divide by 12.
Any ideas
cheers
Peta
On Nov 30, 2:58 pm, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Pete,
You can shorten your current code to:
Worksheets(Sheet1).Activate
ActiveCell = Interest
ActiveCell.Offset(-1, 3).Copy ActiveCell.Offset(1, -1
Hi Janet,
If you are using XL2007, then the range you are seleceting contains about 1
million cells, so this code will take a while.
If your data doesn't actually go to the end of Column N, you could speed it up
considerably by changing your first line to:
Range(N2:N2).Select
If this range
Hi,
Try doing it manually with the macro recorder on, then look at the syntax.
Dave.
Date: Mon, 30 Nov 2009 10:05:31 -0800
Subject: $$Excel-Macros$$ Run-time error '1004': PasteSpecial method of Range
class failed
From: jon.wester...@gmail.com
To: excel-macros@googlegroups.com
This
Check to see if there is a SheetActivate event macro for that particular sheet.
Regards - Dave.
Date: Tue, 1 Dec 2009 08:06:15 -0800
Subject: $$Excel-Macros$$ excel help
From: karthikeyansankar...@gmail.com
To: excel-macros@googlegroups.com
Hi,
A particular sheet in excel file
Hi friends,
I am using XL2003.
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook.
My code is this:
Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range(A1))
If Not c Is Nothing
Hi,
Take a look at the attached to see if I have understood you correctly.
Regards - Dave.
Date: Fri, 4 Dec 2009 11:15:46 +0530
Subject: Re: $$Excel-Macros$$ Help required in repeating calculation using
macro (This is urgent, can anyone help me)
From: rfhyd1...@gmail.com
To:
Hi Chris,
You could do this with VBA, but it's better, and easier, done with conditional
formatting.
If you are using XL2003 or earlier, select Col B, then put this formula into
conditional formating:
=COUNTIF(A:A,A1)1
Set your format as desired.
If you've not used conditional format before,
Hi Abhishek,
Attach your workbook, and lets see what you've got so far.
Regards - Dave.
Date: Mon, 14 Dec 2009 10:23:03 +0530
Subject: Re: $$Excel-Macros$$ A Simple VBA Query
From: abhishek@gmail.com
To: excel-macros@googlegroups.com
Hi folks,
Any update ?
Regards,
On Fri, Dec
Just attach it as you would any attachment.
Dave.
Date: Sun, 13 Dec 2009 23:16:49 -0800
Subject: $$Excel-Macros$$ Re: Rota planning
From: j...@jpwebs.co.uk
To: excel-macros@googlegroups.com
Sheet did not attach - where do I do this please?
John
--
,
Abhishek Jain
On Mon, Dec 14, 2009 at 11:32 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Abhishek,
Attach your workbook, and lets see what you've got so far.
Regards - Dave.
Date: Mon, 14 Dec 2009 10:23:03 +0530
Subject: Re: $$Excel-Macros$$ A Simple VBA Query
From: abhishek
Hi,
Try this code:
Sub DeleteCokeRows()
B = ActiveCell.SpecialCells(xlLastCell).Address
For Each c In Range(Cells(1, 1), B)
With c
Set A = .Find(coke, LookIn:=xlFormulas, LookAt:=xlPart,
MatchCase:=False)
End With
If Not A Is Nothing Then
hi Jon,
This has always been a problem with data validation and paste.
The only way around it, that I know of, is to use a 'Change' event macro.
You ask VBA to examin any new data to see if it is suitable. If it isn't, get
VBA to delete the entry, and post a message box politely informing the
Hi XL'ers
I am using XL2003
This is a follow-on from a previous post.
I refer to the use of the following (meaningless) Worksheet Window macro:
Private Sub Worksheet_Activate()
With Range(CompNames)
End With
End Sub
If the named range (CompNames) is on the sheet being actived, the
.
interesting...
Paul
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Mon, December 21, 2009 12:00:41 AM
Subject: $$Excel-Macros$$ Named ranges in macros
Hi XL'ers
I am using XL2003
This is a follow-on from a previous
Singh
Sent via BlackBerry Wireless
On Wed, Dec 23, 2009 at 12:40 PM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Vinod,
The attached shows how to create an instantly updated unique list from a list
with duplicates, using a single line ov VBA. It doesn't matter what kind
Hi Mahesh,
Firstly, unless TN, North, South, West, East, Dehli, Mumbai, Kolkata are named
ranges, those words should be in double quotes.
You're welcome.
Date: Wed, 23 Dec 2009 21:21:00 +0530
Subject: Re: $$Excel-Macros$$ GetUnique_Collection - to capture numeric values
From: nvino...@gmail.com
To: excel-macros@googlegroups.com
Thanks Dave This works awesome ..
Vinod
On Wed, Dec 23, 2009 at 12:40 PM, Dave Bonallack
Huh?
Date: Thu, 24 Dec 2009 10:24:02 +0530
Subject: Re: $$Excel-Macros$$ GetUnique_Collection - to capture numeric values
From: rfhyd1...@gmail.com
To: excel-macros@googlegroups.com
:) at least say thanks who help u out in problem
On Thu, Dec 24, 2009 at 5:53 AM, Dave Bonallack davebonall
Hi Sachin,
Have a look at the attached to see if it does what you need.
Regards, and Merry Christmas.
Dave.
Date: Thu, 24 Dec 2009 17:40:36 +0530
Subject: $$Excel-Macros$$ need urgent help
From: sachinmbafina...@gmail.com
To: excel-macros@googlegroups.com
Dear Friend,
Pls find attached sheet
Please attach a workbook with a sample of English and non-English characters
Regards - Dave
Date: Mon, 4 Jan 2010 15:40:08 +0530
Subject: $$Excel-Macros$$ Re: Urgent Help Needed!!
From: anish@gmail.com
To: excel-macros@googlegroups.com
Experts!!!
Any updates on below query??
Thanks!!
have seprated titles
Character wise...we have 3 categories..
ENGLISH, EUROPEAN (Diacritics) and Multibyte (Chinese, Japanese, Korean etc).
Please have a look and help me if it is possible.
Thank you very much!!
ANISh
On Mon, Jan 4, 2010 at 6:43 PM, Dave Bonallack davebonall...@hotmail.com
Hi Alex,
You need to use absolute refs.
Instead of:
=LARGE(D12:S12,1), =LARGE(D12:S12,2), and =LARGE(D12:S12,3)
try:
=LARGE($D$12:$S$12,1), =LARGE($D$12:$S$12,2), and =LARGE($D$12:$S$12,3)
Regards - Dave.
Date: Fri, 8 Jan 2010 07:52:45 -0800
Subject: $$Excel-Macros$$ Conditional Formatting or
Hi S,
Not sure if there's a solution to this. Probably is, but since you're doing it
by macro, I'd just repeat the print command for each sheet. You can put a
single print code-line into a loop if there's lots of sheets.
Regards - Dave.
Date: Sat, 9 Jan 2010 11:08:44 -0800
Subject:
Hi Anant,
I don't think this is possible in XL. It is standard procedure in Access. Can
you use that instead?
Regards - Dave.
Date: Mon, 11 Jan 2010 15:29:04 +0530
Subject: $$Excel-Macros$$ HOW CAN I WORK IN ONE WORKBOOK IN MULTIPLE LOCATION
From: anant.shelk...@gmail.com
To:
.
Ramesh
--- On Mon, 1/11/10, Dave Bonallack davebonall...@hotmail.com wrote:
From: Dave Bonallack davebonall...@hotmail.com
Subject: RE: $$Excel-Macros$$ HOW CAN I WORK IN ONE WORKBOOK IN MULTIPLE
LOCATION
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Date: Monday, January 11
Hi,
The syntax is:
ActiveSheet.Name = Range(Sheet1!C4)
This line renames the active sheet to whatever is in Sheet1!C4
Change the details to suit your requirements.
Regards - Dave
Date: Mon, 11 Jan 2010 18:08:49 +0530
Subject: Re: $$Excel-Macros$$ Using macro move sheets
Hi,
Any special reason for using VBA?
I think this would be done more easily using the worksheet function COUNTIF
Regards - Dave.
Date: Mon, 18 Jan 2010 06:03:26 -0800
Subject: $$Excel-Macros$$ Problem w/ VBA Code Evaluating a Range
From: mdai...@xomox.com
To:
Hi Sudhir
Have a look at the attached. It took some sorting! Hopefully it's what you need.
Regards - Dave.
Date: Mon, 18 Jan 2010 14:12:24 +0530
Subject: $$Excel-Macros$$ Help
From: bluecore...@gmail.com
To: excel-macros@googlegroups.com
Hi Frnds
In the attached sheet calcuation
Hi OE,
Progmatically toggle what button?
VBA can change a button completely - the text, colour, shape, and the macro it
activates.
Regards - Dave.
Date: Tue, 19 Jan 2010 12:37:55 -0800
Subject: $$Excel-Macros$$ Reset Ignored Errors Excel Error Checking Options
From:
Hi,
Have a look at the attached. Let me know if it's what you need.
Regards - Dave.
Date: Wed, 20 Jan 2010 10:59:47 +0530
Subject: $$Excel-Macros$$ Sum Based on date criteria
From: mahes...@gmail.com
To: excel-macros@googlegroups.com
Dear All
In attach sheet i need a formula to sum status of
Hi Paddy,
Try this:
Sub MyFormula()
ActiveCell = =Your Formula goes here
End Sub
Regards - Dave
Date: Thu, 21 Jan 2010 01:27:19 -0800
Subject: $$Excel-Macros$$ How do i
From: ryan0...@gmail.com
To: excel-macros@googlegroups.com
create a macro that means i can select any cell
Hi,
Try:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dave.
Date: Thu, 21 Jan 2010 23:18:50 +0530
Subject: $$Excel-Macros$$ Workbook.Selection Change Event
From: harpreetguj...@gmail.com
To: excel-macros@googlegroups.com
Hi, Need help regarding the following code:
I have written
Oops
and:
If Activeworksheet.Name = Main Then
From: davebonall...@hotmail.com
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Workbook.Selection Change Event
Date: Fri, 22 Jan 2010 12:52:44 +0800
Hi,
Try:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dave.
Hi Vinrod,
You could use this in the sheet window:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Columns.Count = 256 Then
If Application.WorksheetFunction.CountA(Selection) 0 Then
MsgBox (Row not empty. Entire Row cannot be selected.)
Hi Paul,
I've installed the add-in, but when I select Control Panel, I get an error
message Cannot open key
I'm using XL2000
Regards - Dave.
Date: Fri, 22 Jan 2010 13:04:40 -0800
Subject: $$Excel-Macros$$ Excel Data Automator
From: pau...@gmail.com
To: excel-macros@googlegroups.com
I've
in using admin account? It tries to read the registry for the
list of ODBC links set up in your machine, so you account has to be able to
read registry.
On Sat, Jan 23, 2010 at 12:37 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Paul,
I've installed the add-in, but when I select
Hi Shilpa,
If you really want to do this with VBA, use the code below.
Sub VlookupVBA()
A = 1 'Start Row
Do Until Cells(A, 1) =
With Sheets(Sheet2).Range(A1:A6)
Set c = .Find(Cells(A, 1), LookIn:=xlFormulas, lookat:=xlWhole)
If Not c Is Nothing Then
Hi XLCPA,
I don't know a quick method of doing this, although there may be one.
However, to test for an empty clipboard, you could paste to an unused cell,
then test the cell.
To clear the clipboard you could Copy a blank cell, then use
Application.CutCopyMode = False
Just some ideas.
Regards:
CMA Ankur Pandey
(Someone Different)
I'm not the best but i'm not like the rest~~
On Wed, Jan 27, 2010 at 10:23 AM, Dave Bonallack
davebonall...@hotmail.com wrote:
Hi XLCPA,
I don't know a quick method of doing this, although there may be one.
However, to test
Hi Captain,
I have attached a simple spreadsheet
Almost.
Regards - Dave
Date: Thu, 28 Jan 2010 16:05:23 -0800
Subject: $$Excel-Macros$$ IF statement logic
From: capted...@googlemail.com
To: excel-macros@googlegroups.com
Hi there
I have attached a simple spreadsheet with a table in it
Hi,
If I understand correctly, I would use a dynamic named range.
This link shows the method.
http://www.contextures.com/xlNames01.html#Dynamic
Regards - Dave.
Date: Mon, 1 Feb 2010 03:55:39 -0800
Subject: $$Excel-Macros$$ Dynamic criterion range for Advanced filtering
From:
Hi Ayush,
What do you mean by outside the procedure'?
Dave.
Date: Tue, 2 Feb 2010 08:24:36 -0800
Subject: $$Excel-Macros$$ how to declare define global range in vba
From: jainayus...@gmail.com
To: excel-macros@googlegroups.com
Hello Group,
How can we declare and define Global range
I forgot to say that YEARFRAC is from the Analysis ToolPak
Dave.
From: davebonall...@hotmail.com
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Help on excel formula
Date: Wed, 3 Feb 2010 13:01:33 +0800
Hi
I've used the YEARFRAC funtion and multiplied it by 12.
Have a
Hi,
Don't quite understand your requirement.
You want to insert 3 new columns into F,G,H, but you want to retain the current
column H as it is. If you insert 3 columns at F,G,H, current Column H will
become Column K, and current Column I will become Column Column L.
Regards - Dave.
Date:
Hi Nick,
With regards to the protection code line, are you using double quotes?
Sheets(input).Protect Password:=mypassword
Also, your Unprotect code lines seem to be in error, on 2 counts. You have:
Sheets(input).Unprotect.Protect Password:=(mypassword)
The .Protect is incorrect. It should be:
Hi Jason,
One way to increment your cell is to use the Cells thingy.
The Cells thingy uses Row, then Column within it's brackets, and it uses
numbers for both - not letters for Columns.
So, Cells(1, 3) refers to Row 1, Column 3, which is the same as Range(C3)
But with the cells thingy, you
!
it keeps from having to insert numbers to find out what the column number is
for column XY
of course, Range(XY1).Column would give me the number... but still...
Paul
From: Dave Bonallack davebonall...@hotmail.com
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Sent: Mon
and store all of the cell values in an array.
You can use:
For R = 1 to 100
For C = 1 to 10
tArray(R,C) = Cells(R,C)
Next C
Next R
I'm sure there is more that Cells() can do that isn't available in Range()
But usually, I just use the one that fits my code.
P
From: Dave Bonallack
Hi Ayush,
Not sure about SumIf, but we can use SumProduct:
=SUMPRODUCT((A1:A1000)*((B1:B1000=AJ)+(B1:B1000=CM)+(B1:B1000=12)))
Note that SumProduct doesn't seem to support using whole column refs.
Regards - Dave.
Date: Wed, 10 Feb 2010 07:30:38 -0800
Subject: $$Excel-Macros$$ URGENT
And never underestimate the value of the macro recorder. The code may need a
bit of cleaning up, but syntax is all there for you to take in, and reapply in
your own code.
Dave.
Date: Thu, 11 Feb 2010 18:53:53 -0800
Subject: $$Excel-Macros$$ Re: Excel Macros Free PDF
From:
Hi Keith,
You can force the upper-case by using the 'UCase' VBA function on the 'Target'
You can then remove the lower-case Cases.
Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target,
Range(I9:AM58,I66:AM115,I124:AM173,I182:AM231,I240:AM289))
If Not I Is Nothing
Hi Jen,
Sample data has no duplicates. Am I missing something?
Also, is the data allowed to be sorted?
Regards - Dave.
Date: Wed, 17 Feb 2010 23:12:00 +0300
Subject: $$Excel-Macros$$ Help: How to remove duplicate rows
From: janetdicks...@gmail.com
To: excel-macros@googlegroups.com
Hello Fans..
Hi,
Not sure if sumproduct is faster than sumifs.
But it may be that much of your data is historical (past months or years) and
doesn't update any more.
All non-updating cells that contain formulas, could be replaced with just their
numerical data.
Since your workbook is large, you could write
Hi,
And just to add that you will need XL2007, since earlier versions don't have
enough rows for your requirements (about 180,000)
Regards - Dave.
Date: Thu, 4 Mar 2010 06:16:37 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ Clever way to transpose required
To:
Hi Sandeep,
Sample workbook or copy of code please.
Regards - Dave.
Date: Fri, 5 Mar 2010 02:06:22 +0530
Subject: $$Excel-Macros$$ Duplicate Values
From: sand...@gmail.com
To: excel-macros@googlegroups.com
Hi ,
I am trying to find the duplicate values from two different sheets
I
Hi
The attached has a macro for saving 10 books according to names in the list on
Sheet1.
The Workbooks are saved to the default location, but that can be changed.
Not sure if it's what you want, but maybe it's a start.
Regards - Dave.
Date: Thu, 11 Mar 2010 02:11:47 -0800
Subject: Re:
Hi,
Each cell has to be locked for protection to have any effect.
Select one of the cells that is disobediently accepting data
Format, Cells, Protection tab, click 'Locked'
Do this for each cell you want to lock, or you can select lots of cells in the
ordinary way, or with the Ctrl key, and
Hi,
I suggest you do all this with the macro recorder turned on, then use the
result as a starting point for your macro. You may even find that it needs no
changing at all.
Regards - Dave.
Date: Wed, 17 Mar 2010 16:24:44 +0530
Subject: $$Excel-Macros$$ Help urgently required..
From:
protection.
On Mar 17, 12:57 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
Each cell has to be locked for protection to have any effect.
Select one of the cells that is disobediently accepting data
Format, Cells, Protection tab, click 'Locked'
Do this for each cell you
Hi,
Use the following code line early in your macro:
A = Application.Caller
A will be the name of the button that called the macro.
You can call your buttons by special names, or use a Case statement to sort
through all the buttons you have.
Or you can get VBA to read the text on the button
Hi Andy,
Not quite sure what date fields you want to match to what.
Is it possible to attach the worbook?
Regards - Dave.
Hey all,
I'm trying to cut down on loading times by converting a load of
formulae to code.
I'm stuck on this particular formula -
Hi,
If you have a formula in a cell, it is never considered blank, even if the
formula returns an apparent blank.
You will often get this with 'IF' formulas: eg IF(A20=5,Bing,)
If A20 does not equal 5, then the formula returns a .
But XL sees this as a zero-length string, not a blank cell.
Hi Harpreet,
I think xl does always use the space (2) notation for copied sheets.
However, I don't think it's necessary to call it. When a new sheet is created
in this way, I think it is always the active sheet.
So instead of:
Sheets(Sample (2)).Select
Sheets(Sample (2)).Name = TextBox1
Hi,
Something like:
If Not IsErr(Range(found)) Then
Range(H2:H5).Value = found
Else: Range(H2:H5).ClearContents
End If
Regards - Dave.
Date: Tue, 23 Mar 2010 07:21:21 -0700
Subject: Re: $$Excel-Macros$$ VBA VLookup dates
From: andyr...@hotmail.co.uk
To:
to If Not IsError(found) Then or any variation doesn't seem
to help either.
On Mar 24, 12:09 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
Something like:
If Not IsErr(Range(found)) Then
Range(H2:H5).Value = found
Else: Range(H2:H5).ClearContents
End If
Regards
the line to If Not IsError(found) Then or any variation doesn't seem
to help either.
On Mar 24, 12:09 am, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
Something like:
If Not IsErr(Range(found)) Then
Range(H2:H5).Value = found
Else: Range(H2:H5).ClearContents
End
Hi,
Yes, it works; but it seems much longer than necessary.
I think =MATCH(6,ATM!$C$3:$AJ$3,0) would work just as well.
As to the Index part of the original function:
When the Index function has a zero as it's second arguemnt, it reurns the
entire array - in our case, of True's and False's.
Hi,
I can only get you part of the way at the moment.
The following formula removes all leading non-numeric characters.
ie: jdsdnc2123asdd will become 2123asdd
=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A10123456789)),100)
(assuming jdsdnc2123asdd is in A1)
See if someone else can help
Hi,
I've thought about it some more and have come up with a rediculously long
formula that does the job.
@googlegroups.com
found one more method.
PFA file
Thanks Regards,
Harmeet Singh
Sent via BlackBerry Wireless
On Wed, Mar 31, 2010 at 5:29 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi,
I've thought about it some more and have come up with a rediculously long
formula that does
Hi,
You could try using the formula provided for finding the numbers only, then use
that result with the SUBSTITUTE function substituting the numbers with
Regards - Dave.
Date: Wed, 31 Mar 2010 11:03:55 +0530
Subject: Re: $$Excel-Macros$$ Tip of The day:Extract Number FROM Alphanumeric
Hi,
It seems that Sumproduct doesn't work as expected in VBA.
I have found that I need to use 'Evaluate'
So, if your worksheet formula was, for example:
SUMPRODUCT((A2:A200=Yes)*(B2:B200))
Then the VBA equivalent would be:
YourVariable = Evaluate(SUMPRODUCT((A2:A20=Yes)*(B2:B20)))
Note that if
Hi Ayush,
I also appreciate this group. I have learnt much, especially from seeing how
others solve problems. There's often many ways of doing something in XL, and
it's good to understand different thought processes.
And keep upthe spam ban. One of God's gifts to the world is people who give
101 - 200 of 444 matches
Mail list logo
