Hi,
Just formatting a cell as number after the cell has received data, does not
change the format of the data.
If all your data is numeric, then follow these steps to actually change your
data to numbers:
1. Select Range A71:A1000
2. Format as number.
3. Enter 1 into an unused cell, then
not be a problem in the
future.
Thank you kindly once again!
On Oct 23, 5:28 pm, Dave Bonallack [EMAIL PROTECTED] wrote:
Hi,
Just formatting a cell as number after the cell has received data, does not
change the format of the data.
If all your data is numeric, then follow
Hi Dean,
You can probably use SUMPRODUCT, but if you could attach your spreadsheet, or a
sample thereof, it'd be easier.
Regards - Dave. Date: Mon, 17 Nov 2008 18:14:14 -0800 Subject:
$$Excel-Macros$$ counting values From: [EMAIL PROTECTED] To:
excel-macros@googlegroups.com I have a list of
Hi Rob,
Yeah, sorry about that. Countif is pretty slow when there's lots of data. By
the way, a macro will be slower, but has the distinct advantage that you can
run it only when convenient.
Just looking at your code, I see that you are using the Cells thingy.
When using Cells, you cant refer
Hi Steve,
Dim myFileName As Variant
myFileName = Application.GetOpenFilename(filefilter:=Text Files, *.txt)
If myFileName = False Then Exit Sub 'user hits cancel
With ActiveSheet
With .QueryTables.Add(Connection:=TEXT; myFileName,
Destination:=.Range(AA2))
Hi,
Please check attached for your solution.
Regards - Dave.
Date: Tue, 3 Feb 2009 17:39:40 +0300Subject: $$Excel-Macros$$ Help-Excel files
formulaFrom: rabinthapa2...@gmail.comto: excel-macros@googlegroups.com
Please help me on attach files.-- Regards,Rabindra Thapa¨`·.·´¨) Always
Hi,
If you have xl2007, you can use the new built-in function: IFERR.
Dave.
Date: Wed, 25 Feb 2009 09:16:37 -0800
Subject: $$Excel-Macros$$ How to avoid NA# or remove NA# when there is no
value found by vlookup.
From: prabhat.shrivasta...@gmail.com
To: excel-macros@googlegroups.com
CC:
Hi Wilz
I think this would be easier done with conditional formatting. Would it be
acceptable if the cell colour in Column 3 were red when the conditions weren't
met?
Let's say your data is in Columns A, B and C, starting in Row 2.
In the first row of Column 3, put the following into the
Hi,
This is the code I use for this.
Suppose you are looking for the next available row in column A
A = 1
Do Until Cells(A, 1) =
A = A + 1
Loop
When this section finishes, the next available cell: Cells(A, 1)
If your data is contiguous, you can use:
A = WorksheetFunction.CountA(A:A) + 1
Hi EU777,
As far as I know, this sort of formatting is only available in XL2007.
Regards - Dave
Date: Thu, 12 Mar 2009 04:51:00 -0700
Subject: $$Excel-Macros$$ COLOR CELLS BY % PERCENTAGE 33% Color one cell 33%
, another cell 33%, another cell 34%
From: lax_...@comcast.net
To:
Hi xmux,
This is the main weakness of data validation. The only way around it is to
check the entry using a VBA Change event code.
Regards - Dave.
_
View photos of singles in your area. Click Here
Hi,
You need to use an input box, not a message box.
Instead of: MsgBox (Select Source sheet against which data will be checked)
Use: Ans = Inputbox(Select Source sheet against which data will be checked)
The Ans variable will contain whatever the user inputs.
Remember that when asking for
Hi Joe,
The reason you get the flood of emails not solely pertaining to yourself is so
that perhaps you can help others with their problems, and not simply receive
solutions to your own. This is how the group works - everyone pitching in.
Regards - Dave.
From: j...@it4u.co.uk
To:
Hi,
If your 2 columns are A and B, try this:
=SUMPRODUCT(--(LEFT(A2:A20,3)=Tot)*(B2:B20))
Change the ranges to suit your data.
Regards - Dave.
_
Find out what’s new with your friends Download the new Windows Live Messenger
Hi Hamster,
If you go to the Workbook window of the VBA, where your Workbook Open macros
live, set the left drop-down to Workbook, then in the right drop-down, select:
BeforeSave.
You will se a blank sub like this:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Hi,
I'm not sure if this would work with forms, but when I put multiple text boxes
on a worksheet, I can change them all with the following code:
ActiveSheet.Shapes.Range(Array(Text Box 1, Text Box 2, Text Box
3)).Select
Selection.ShapeRange.Height = 84.75
Hi Steve,
Try this code. It assumes data is in Columns A and B, as stated, and that the
data starts in Row 2.
Sub Steve()
Application.ScreenUpdating = False
A = 2
B = 2
C = 2
For i = 1 To 100
Do Until Cells(A + 1, 1)
Cells(B, 2) =
Hi Steve,
I need an equation that will count the number of records with a name
of Jack and a state of Michigan and filter that count to a range. I
will drop the count for each range in a different cell so the equation
would be a static age bracket.
Sorry, but I can't understand the part I
Hi Mudassar,
Thankyou for posting the email workbook.
Could you please tell us the VBA password so we can see how it works?
Regards - Dave.
From: johnplaye...@gmail.com
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: Macros in Outlook
Date: Wed, 8 Apr 2009 11:05:08 +0500
_
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 unknowns. There are things we don't know we don't know.
From: Dave Bonallack davebonall...@hotmail.com
From:
excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf
Of Dave Bonallack
Sent: Thursday, April 09, 2009
7:33 AM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re:
Macros in Outlook
Thanks.
Date: Wed, 8 Apr 2009 18:42:25
-0700
From
Hi,
A quick way to reveal which cells have formulas:
Ctrl+`
That's Ctrl plus the key above the Tab key.
Regards - Dave.
Date: Mon, 13 Apr 2009 11:15:32 -0700
Subject: $$Excel-Macros$$ color code excel cell if a cell has a formula
From: ned...@yahoo.com
To: excel-macros@googlegroups.com
on special tab and again click on
formula's tab, all cells whic contain formula's will get highlighted, you can
now select the fill color option and colour the cells with your favourite
colour.
Regards,
Shay
On Tue, Apr 14, 2009 at 10:24 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi
Hi ajxecc,
Try this code:
Sub mergedetect()
Dim c As Range
For Each c In Range(A1:A10)
If c.MergeCells = True Then MsgBox c.Address is merged with another cell.
Next c
End Sub
Change the Range ref to suit your needs.
You can change the code after the Then keyword, to do anything you
Hi Surya,
As long as your data is in the format provided in your sample file, the
solution in the attached will be ok.
Regards - Dave.
Date: Wed, 15 Apr 2009 12:59:11 +0530
Subject: $$Excel-Macros$$ Color Conditional Formatting
From: suryaprasad...@gmail.com
To:
Hi goog,
Attach a sample workbook with 20 or so lines of data, with manually entered
results of what you want. We should be able to help you.
Regards - Dave.
Date: Mon, 20 Apr 2009 17:35:46 -0700
Subject: $$Excel-Macros$$ Need Help with autofill please !!!
From: amadugu...@gmail.com
To:
Hi Dustin,
Paste the following code into a module.
Sub InsertMissing()
Do Until ActiveCell.Value =
A = ActiveCell.Value
B = ActiveCell.Offset(1, 0).Value
If B - A 1 Then
ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=xlDown
Hi Dustin,
You have to use the 'Cells' thingy instaed of the 'Range' thingie. Then you can
use variables. But you have to use Column numbers, not Column letters, and you
have to put the Row first, then the Column, separated by a comma.
Example:
YourRow=5 'Row
YourCol=3'Column
Hi Paresh,
The syntax is failry simple for what you want to do, but perhaps it would be
easier done with a worksheet function.
However, if you want do it by macro, the below is an example:
Sub DoMath
Answer1 = 4 + 5
Answer2 = 4 * 5
Answer3 = 4 - 5
Answer4 = 4 / 5
End Sub
'Answer1' , 'Answer2'
Hi,
The following will select the last data column, Row 1:
Cells(1, Columns.Count).End(xlToLeft).Select
The following assigns the last data column number to the variable 'A'
A = Cells(1, Columns.Count).End(xlToLeft).Column
The following will select the last data row, Col A:
Hi Grunta,
This can't be done with worksheet formulas, as they will be re-calculated
everytime something changes, as you have discovered.
You will need to use the RND function in VBA to put a value in a cell. Since
this is done only once, the value won't change when the sheet re-calcs.
Hi Ahmed
An IF statement has the following:
Firstly, the condition.
Secondly, the result if the condition is met.
Thirdly, the result if the condition is not met.
Each of the above is separated by commas, so it looks like this:
IF(Condition, Result if met, Result if not met)
In your
Hi Dan,
You could use VLOOKUP.
The syntax is VLOOKUP(Lookup_Value,Lookup_Table,Lookup_Column,Type)
The Lookup_Value is the reference of your drop-down cell - eg B2
The Lookup_Table is as below, but needs to be in 2 columns - eg Y2:Z14
Lookup_Cloumn would be 2
Type would be 0
Hope
:12 pm, Dave Bonallack davebonall...@hotmail.com wrote:
Hi, try this code somewhere in your sub:
With Range(A1:A1000)
D = Seed-BOD
Set C = .Find(D, LookIn:=xlFormulas)
If Not C Is Nothing Then MsgBox Sample is already here.
End With
If you are already using C and/or D
Hi,
If your 'Given Cell' is A1:
Select the row you want to format
In the Conditional Format window, select 'Formula is'
In the formula thingy, put:
=$A$1=Friday
Set the formatting you want.
Click ok.
Hope this helps.
Regards - Dave.
Date: Sun, 10 May 2009 19:43:56 -0700
Hi Sharma,
First you have to format all the necessary cells as 'Text', otherwise the 0
is lost.
Then try putting this into data validation (Custom, Formula) of A3:
=AND(ISNUMBER(VALUE(A3)),LEN(A3)=10,LEFT(A3,1)=0)
Copy validation to necessary cells.
Hope this helps.
Regards - Dave
Hi Habeeb,
Have a look at the attached, and see if it does what you need.
Regards - Dave.
From: habeebc...@hotmail.com
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ formula in macros
Date: Fri, 15 May 2009 04:34:26 +
Hi Guys,
I currently written a code were in
Hi,
If you don't need any merged cells in your sheet, try putting these lines at
the beginning of your present code:
Cells.Select
With Selection
.MergeCells = False
End With
Range(A1).Select
Regards - Dave.
Date: Thu, 21 May 2009 23:11:30 +0530
From:
Hi Venkat,
Sounds like a job for SUMPRODUCT.
We could probably do the needful if you attached a sample worksheet.
Regards - Dave.
Date: Wed, 27 May 2009 09:19:07 +0530
Subject: $$Excel-Macros$$ Formula needed
From: sudhakar...@gmail.com
To: excel-macros@googlegroups.com
Dear Friends,
I
.
Thanks and Regards
Atul Kesaria
On Wed, May 27, 2009 at 10:10 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Atul,
Have a look at the attached workbook, and see if it does what you want. I've
added a button to run the macro from the sheet, but you may want to run it some
other way
!!!
The macro is awesome.this is what i wanted.
Thanks and Regards
Atul Kesaria
On Wed, May 27, 2009 at 10:10 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Atul,
Have a look at the attached workbook, and see if it does what you want. I've
added a button
Hi Paul,
I think the op means that the text NO SALES can be in any cell somewhere on
the sheet - sheet name unknown, and not a sheet called NO SALES.
Hence his desire to look thru every cell on every sheet. (Cells.Select -
Selection.Find)
And that's a good tip about counting backwards. I've
Hi Dave,
Great name, by the way.
Have a look at the attached workbook and see if I have understood you
properly...
Regards - Dave.
Date: Wed, 27 May 2009 15:28:41 -0700
Subject: $$Excel-Macros$$ A Macro needed for the task beow
From: davidstev...@gmail.com
To:
Hi Vincent,
Try disabling the calculate thingy
Application.Calculation = xlManual
Look for change in $A$1
Application.Calculation = xlAutomatic
Regards - Dave.
Date: Fri, 29 May 2009 06:33:36 -0700
Subject: $$Excel-Macros$$ cells change triger macro problem
From: vincent2...@gmail.com
To:
Hi George,
Can't quite understand your request.
column F down untill we reach two empty cells in columns F and J
But your sample data doesn't have 2 empty cells in both F and J.
Perhaps you could attach a sample workbook with a bit more data - say 20 rows.
Regards - Dave.
Date: Fri, 29 May 2009
Hi TAlgo,
Have a look at the attached. I've included 2 different macros because I wasn't
sure of what you really need.
Hope this points you in the right direction.
Regards - Dave
Guys,
trying to create a macro which will copy column A to column B. This is
simple but only exception I m
Hi Robin,
I have tried to understand your needs, but cannot.
1. a macro which will read my file name
This we can do.
2. i want that my macto add a
column D and reads my saved file name and put a digit 25 in that D
coulmn in range d1 to d last (till column A, B, C, carries data) ??
This I
Hi sjsean,
Try the attached workbook.
Put the words No Sales (without the quotes) into any cell on one of the
sheets.
Note that sheet name and then run the macro.
That sheet should be deleted.
Hope this helps.
Regards - Dave.
From: sjsean sjsean95...@gmail.com
To: MS EXCEL AND VBA MACROS
and other files SENDING U the sample
file
Thanks,
Robin
On May 30, 6:37 pm, Dave Bonallack davebonall...@hotmail.com wrote:
Hi Robin,
I have tried to understand your needs, but cannot.
1. a macro which will read my file name
This we can do.
2. i want
Hi,
I assume you are referring to VBA.
VBA can't select a cell or range on a sheet which isn't active.
You have to activate the sheet, then select the range:
Sheets(A).Activate
Range(A1).Select
However, most of the time you don't have to select the cell or range you want
to work on.
For
Hi,
Take a look at the attached.
Regards - Dave.
Date: Wed, 3 Jun 2009 10:43:07 +0530
Subject: $$Excel-Macros$$ Help required
From: sachina...@gmail.com
To: excel-macros@googlegroups.com
Dear All,
Please help me on analysis of different senarion. details question and sheet
attached.
Hi,
Try this:
A = Cells(Rows.Count, 1).End(xlUp).Row + 1
This will give you the first blank row number
Regards - Dave.
Date: Thu, 4 Jun 2009 18:40:46 +0530
Subject: $$Excel-Macros$$ find next empty cell address
From: grli...@gmail.com
To: excel-macros@googlegroups.com
Dear All
my data started
Hi Mahesh,
One way is to select all cells in the column:
(I have assumed Col A, starting Row 2)
Select conditional format, formula is, then enter:
=COUNTIF($A$2:$A$1000, A2)1
Set the format you want - say red bold text.
Hit ok
All duplicates will be red bold.
Change the range and starting
Hi Lion123,
Have a look at the attached. I've used random numbers to test it.
Details on the sheet.
Hope this is sort of what you want.
Anyway, really interesting.
Regards - Dave.
I am trying to sort special numbers in a long list of telephone
numbers (7 digits). For example:
a.
Hi Fabio,
That's a very interesting approach to the problem, and a very good formula.
Regards - Dave.
From: flnle...@gmail.com
Date: Fri, 5 Jun 2009 09:29:46 -0300
Subject: $$Excel-Macros$$ Re: Phone Number Filtering
To: excel-macros@googlegroups.com
see atached file... I've created a name for
Hi Alokeswar,
Your attachment has the VBA protected with a password.
Regards - Dave.
Date: Fri, 5 Jun 2009 18:37:58 -0700
From: alokeshwar.tiw...@yahoo.com
Subject: $$Excel-Macros$$ Re: remove module
To: excel-macros@googlegroups.com
You can use SendKeys to protect and unprotect VBA project.
Hi Group,
Tip.
The following line of code line returns the first blank cell at the end of a
series of data, ignoring any blank cells within that series.
In this case, the series is in Col A.
I use it a lot when copying stuff onto the bottom of existing data.
This is not new, but might be useful
Hi Alokeswar,
Thanks for sharing that.
Regards - Dave.
Date: Sat, 6 Jun 2009 02:55:50 -0700
From: alokeshwar.tiw...@yahoo.com
Subject: $$Excel-Macros$$ Re: remove module - password is password
To: excel-macros@googlegroups.com
I apologize, please use following password:
password
guys do it? or you use another way to find blank cells?
Cheers
Yu
On Sat, Jun 6, 2009 at 10:12 AM, Dave Bonallack davebonall...@hotmail.com
wrote:
Hi Group,
Tip.
The following line of code line returns the first blank cell at the end of a
series of data, ignoring any blank cells within
Hi,
If the FIND thingy doesn't work with filter-hidden rows, try just looping
through each cell in the range you want:
Something like:
For each c in Range(A2:A1000)
If c.value = Your criteria here Then Msgbox Bing! It's here!
Next c
Replace the Message Box with whatever code you
Hi Harmeet,
I think this is a very good tip.
Regards - Dave.
Date: Tue, 9 Jun 2009 00:29:31 +0530
Subject: $$Excel-Macros$$ Re: BEST EXCEL TIP WEEK # 24 Submit TIP and Win
PRIZE
From: harmeet.hew...@gmail.com
To: excel-macros@googlegroups.com
HI All,
Name : Harmeet
Subject : Hidden
Hi,
Include this line in the code:
A = Range(D21).Value * Range(D20).Value
The variable 'A' will store the number you're looking for.
Regards - Dave.
Date: Mon, 15 Jun 2009 14:35:29 -0700
Subject: $$Excel-Macros$$ getting the value from a cell that has a forumla in
it
From:
Good choice Ayush. I also thought it was very good.
It's the Worksheet-Function equivalent of putting comments after an apostrophe
in VBA.
Regards - Dave.
Date: Wed, 17 Jun 2009 07:48:06 -0700
Subject: $$Excel-Macros$$ Best Excel Tip Award of Week # 24 goes to HARMEET
SINGH
From:
Hi Ruchi,
Not sure I understand all your needs.
Take a look at the attached and tell me if I'm on the right track
Regards - Dave.
Date: Wed, 17 Jun 2009 22:38:51 +0530
Subject: $$Excel-Macros$$ Re: Vlookup
From: ruchigab...@gmail.com
To: excel-macros@googlegroups.com
Hi
actually i
Hi,
This just means that the code has paused for some reason (perhaps because you
asked it to, or perhaps it has a mind of its own). You just need to click the
Reset button on the VBA toolbar. This will allow other code to run. Basically,
you can only run one code at a time. When a code is in
Hi Dilip,
When I open your workbook (with XL2000), it functions as you say it should.
However, when I type in '=+MROUND(A3,100)' to any cell, I get a #NAME? error.
I don't see 'MROUND' in the function list, or the use of the '+'
Can you tell me what's happening please?
Regards - Dave.
Date:
Hi Levi,
We need to know how this data range looks.
Any chance of attaching a sample workbook?
Regards - Dave.
Date: Wed, 24 Jun 2009 07:12:03 -0700
Subject: $$Excel-Macros$$ Parse through a data range
From: rlsmalls...@gmail.com
To: excel-macros@googlegroups.com
I am wanting to parse
Hi Dharmendra,
Have a look at the attachment, click the button and see if it does what you
want.
Have a look at the code. It's really just 1 line.
I've added the second line just to undo the auto Word Wrap feature.
Regards - Dave.
Date: Wed, 24 Jun 2009 23:43:02 +0530
Subject:
You're welcome!
Date: Thu, 25 Jun 2009 10:32:44 -0700
Subject: $$Excel-Macros$$ Re: how to extract a comment to a cell
From: dharmendrakumar...@gmail.com
To: excel-macros@googlegroups.com
Thanks a ton Dave..It's working fine..
Regards,
Dharmendra
On Jun 25, 8:21 pm, Dave
I concur
Regards - Dave.
Date: Wed, 1 Jul 2009 19:58:20 +0530
Subject: $$Excel-Macros$$ Re: BEST EXCEL TIPWEEK # 27Submit your TIP here
only
From: vishvesh.chau...@gmail.com
To: excel-macros@googlegroups.com
Really good bakul. I'm using excel since started using computers but its new to
Hi Ashutosh,
Absolute refrencing won't help you here.
If you have control of the spread-sheet, and you can easily re-structure it,
change the vertical list to a horizontal one.
If you can't do that, select the refrence list, then copy. find an unused pert
of the sheet. Click in a cell, then
by =$A1.
Best Regards,
Sanjay
--- On Thu, 2/7/09, Dave Bonallack davebonall...@hotmail.com wrote:
From: Dave Bonallack davebonall...@hotmail.com
Subject: $$Excel-Macros$$ Re: Help on copying formula.
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Date: Thursday, 2
Hi Shivashankar.C.Katageri.
Have a look at the attached.
Select B1 then copy across.
Regards - Dave.
Date: Thu, 2 Jul 2009 13:04:10 +0530
Subject: $$Excel-Macros$$ help required
From: shankarkatag...@gmail.com
To: excel-macros@googlegroups.com
Dear all,
Any one can help w.r.t copy from
@googlegroups.com
Dave,
That's exactly how I'm doing it now. I'm just wondering if each cell
could call a sub that would do the formatting on the fly and not hard
code it with each sheet. If there would be any advantage if it could
be done.
On Jul 1, 10:29 pm, Dave Bonallack davebonall
Hi Steve,
Have a look at the attached.
Regards - Dave.
Date: Fri, 3 Jul 2009 02:12:06 -0700
Subject: $$Excel-Macros$$ Simple VBA Data validation query
From: stevedha...@googlemail.com
To: excel-macros@googlegroups.com
Hi, Hopoing someone will be kind enough to help me with this simple
Hi Dilip,
Could you please tell me how you did the calendar thing?
Your sample workbook gives no explanations, and I can't figure it out by the
code.
Regards - Dave.
Date: Sat, 4 Jul 2009 00:22:25 +0530
Subject: $$Excel-Macros$$ Re: calendar pop-up
From: dilipan...@gmail.com
To:
Hi Jessie,
If you attach a sample workbook, we will be able to help you better.
I use this method a lot, but I need to know how your data is laid out.
Regards - Dave.
Date: Sat, 4 Jul 2009 04:57:42 -0700
Subject: $$Excel-Macros$$ FIND COMMAND IN EXCEL VBA
From:
Hi,
Assuming accno is a named range, try one of the following:
WorksheetFunction.VLookup((accno), Range(q4:w570), 5, False)
or
WorksheetFunction.VLookup(Range(accno), Range(q4:w570), 5, False)
or
WorksheetFunction.VLookup(Range(accno), Range(q4:w570), 5, False)
Regards - Dave.
Date: Sun, 5 Jul
Hi,
That could be done by saving A1 to a variable, then clearing the sheet, then
re-populate A1.
Something like:
A = Range(A1).Value
ActiveSheet.Cells.ClearContents
Range(A1) = A
Regards - Dave.
Date: Tue, 14 Jul 2009 12:28:09 +0530
Subject: $$Excel-Macros$$ 1Hardcode a cell
Hi,
Try the following:
=WEEKNUM(TODAY())
Regards - Dave.
Date: Tue, 14 Jul 2009 19:57:18 -0700
Subject: $$Excel-Macros$$ how to get the week no in a function
From: subbu1...@gmail.com
To: excel-macros@googlegroups.com
hi
can you pls tell me the function to get the week no of a
Hi,
Try putting the following code in a module, then Run:
Sub ChangeToValue()
A = 2
Do Until Cells(A, 1) =
Cells(A, 1) = Val(Cells(A, 1))
A = A + 1
Loop
End Sub
Regards - Dave
Date: Thu, 16 Jul 2009 23:17:46 -0700
Subject: $$Excel-Macros$$ Value all the Selection
Hi,
I pasted your code into my XL2000. I don't get any error when inserting rows.
Dave.
Date: Fri, 17 Jul 2009 12:36:21 -0700
Subject: $$Excel-Macros$$ DateStamp Macro Debug Help
From: pogs...@gmail.com
To: excel-macros@googlegroups.com
Hey all,
I found a very simple macro to track
Hi Dev,
Your macro is more complex than necessary.
You don't have to unhide sheets, or select ranges to work on them.
The following 2 lines of code should do what you need.
Sub DoWhatDevNeeds()
Sheets(Dump).Cells.ClearContents
Sheets(Formula).Columns(A:B).ClearContents
End Sub
Hope this
Hi,
If Pinky's data is just like you say, have a look at the attached.
You can copy down to accommodate more data.
However, if the sheet has a lot of data, ie thousands of lines, the attached
worksheet function solution may take too long, and a VBA solution would be
better.
Regards - Dave.
the other and in one
whole cell.
--- On Tue, 7/28/09, Dave Bonallack davebonall...@hotmail.com wrote:
From: Dave Bonallack davebonall...@hotmail.com
Subject: $$Excel-Macros$$ Re: Segregation of data on a button click in excel
To: excel-macros@googlegroups.com excel-macros@googlegroups.com
Date
Hi,
I have also come across this problem. I have not found a way to search a sheet
that is not selected.
The work-around I use is as follows:
Application.ScreenUpdating = False
A = ActiveSheet.Name
Your code here, including the Sheet.Select line
Sheets(A).Select
Application.ScreenUpdating =
Hi Wes,
Try formatting your data as text, then use the following data validation:
=AND(LEN(A1)=7,ISERROR(VALUE(A1))=FALSE)
Regards - Dave.
Date: Mon, 3 Aug 2009 18:11:25 -0700
Subject: $$Excel-Macros$$ Data Validation to only allow 7 digit numbers in a
entire column
From:
Hi,
Try this formula in B2, and copy down.
=LEFT(A2,LEN(A2)-1)*-1
You will have to format the cell to Number, 2 decimal places.
Regards - Dave.
Date: Tue, 4 Aug 2009 14:38:34 +0530
Subject: $$Excel-Macros$$ Formula-Replacing Minus Symbol
From: puttamada...@gmail.com
To:
Hi,
Highlight the entire column - lets suppose it's column A.
Then put the following into the Formula is part in the Conditional Format
dialog box:
=LEN(A1)10
Set the desired format.
Regards - Dave.
Date: Sat, 8 Aug 2009 08:12:55 -0700
Subject: $$Excel-Macros$$ Simple question about Format
Hi,
Paste this into D2, then copy down.
=IF(B2-A260,Above 60 days,IF(B2-A230,31-60 days,0-15 days))
Regards - Dave.
Date: Sun, 9 Aug 2009 15:02:05 +0530
Subject: $$Excel-Macros$$ Calculate Range of Days
From: mahes...@gmail.com
To: excel-macros@googlegroups.com
Dear All
i want to
Hi,
I think you'll have to use VBA.
Have a look at the attachment. Select the cell you want to change, then press
Ctrl+q
However. the cell is then treated as text, and cannot be used in calculations.
Is this what you need?
Regards - Dave.
From: manoj...@ocimumbio.com
To:
insert equal macro.
Sub InsertEquals()
Dim cel As Range
With Selection
For Each cel In Selection
cel.Value = = cel.Value
Next
End With
End Sub
From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On
Behalf Of Dave Bonallack
Sent: Friday, August 14, 2009 7
Hi,
Have a look at the attached.
I've used SUMIF. But I don't know why you don't want to use SUM. It works just
as well, and is simpler. They are the results in red.
Regards - Dave
Date: Sat, 15 Aug 2009 17:21:35 +0530
Subject: $$Excel-Macros$$ Fwd: about to remove #VALUE! error
From:
Hi Epi,
One way is to use the Union thingy
Union(Range(Cells(1, 1), Cells(2, 2)), Range(Cells(3, 3), Cells(4, 4))).Name =
RangeName
Regards - Dave
Date: Wed, 26 Aug 2009 18:14:05 -0700
Subject: $$Excel-Macros$$ Combine multiple ranges using R1C1
From: epi_cen...@hotmail.com
To:
Hi Tomy,
I think the problem is that you have the macro 'Selecting' the range which is
xlveryhidden.
Such a range can't be seleceted, but XL doesn't have to select a cell to work
on it.
Try the following code lines instead of what you have.
Sub Move_Data()
'
' Move_Data Macro
' Macro
Hi Mark,
Your description is very detailed, but I still get lost in it. Perhaps you
could attach the file. Fudge the data if it's sensitive.
Regards - Dave.
Date: Mon, 7 Sep 2009 06:37:32 -0700
Subject: $$Excel-Macros$$ Too many arguments for this function help with
nested IF statements
Hi,
I haven't tested this, but I think you need:
Range(data) = =vlookup(R[-1]C,[ CarrierGrid ]OAK!L:R,7,False)
ie, use square brackets instead of '
Regards - Dave
Date: Tue, 8 Sep 2009 07:26:00 -0700
Subject: $$Excel-Macros$$ Variable Workbook name used in range formula
From:
Hi,
Can't you just set calculations to manual while you do the Goal Seek?
Dave.
Date: Fri, 11 Sep 2009 03:33:04 -0700
Subject: $$Excel-Macros$$ VBA - Access multiple Excel instances
From: paul...@gmail.com
To: excel-macros@googlegroups.com
Hi,
I have a very large large spreadsheet
if it is possible and how to
manage multiple instances of Excel from within one Sub?
Thanks
Paul
On Sep 11, 2:39 pm, Dave Bonallack davebonall...@hotmail.com wrote:
Hi,
Can't you just set calculations to manual while you do the Goal Seek?
Dave.
Date: Fri, 11 Sep 2009 03:33:04
Hi Pooja
Not possible in 2003 usinf formulas. Have to use VBA.
And if the cells are colored as a result of conditional formatting, it's even
trickier.
Regards - Dave.
Date: Tue, 15 Sep 2009 11:15:43 +0530
Subject: $$Excel-Macros$$ Count of Colored Cells
From: vatspoojav...@gmail.com
To:
1 - 100 of 444 matches
Mail list logo
