Oops
The below should read:
Is A5 less than or equal to T5? - No - Go to the next statement
Is A5 less than or equal to T6? - No - Go to the next statement
Is A5 less than or equal to T7? - Yes - Stop. Answer: S7
Dave.
From: davebonall...@hotmail.com
To: excel-macros@googlegroups.com
Su
Hi,
Try this:
=IF(A5<=$T$5,$S$5,IF(A5<=$T$6,$S$6,IF(A5<=$T$7,$S$7,IF(A5<=$T$8,$S$8,IF(A5<=$T$9,$S$9,IF(A5<=$T$10,$S$10,""))
The multiple IF statement stops as soon as it finds a TRUE. So you don't need
to use AND statements to create 'windows'
The above asks:
Is A5 greater or equal to T5?
Hi Haseeb,
I of course haven't seen every answer to every question on this forum, but your
formula solution to finding the number of groups is one of the best I've seen.
Regards - Dave.
Date: Thu, 21 Jul 2011 09:11:12 -0700
From: haseeb.avarak...@gmail.com
To: excel-macros@googlegroups.com
Subje
Hi Haseeb,
A sort of masking. Simple and excellent!
However, if a 'Yes' appears in the last column, the count is incorrect by 1.
You could possibly include an If statement to test for this.
Or you could modify the formula slightly:
=SUMPRODUCT(--(A2:T2="Yes"),--(B2:U2<>"Yes"))
This works, but requ
Hi Azeema,
Have a look at the attached to see if it meets your needs. The formula is
really cumbersome, but it's all I can think of at the moment.
Regards - Dave.
From: aze...@gmail.com
Date: Tue, 19 Jul 2011 23:44:14 -1000
Subject: $$Excel-Macros$$ adding adjacent (contagious) cells only
To: ex
Hi,
Fractions of a month are a bit subjective. Fractions of WHICH month, needs to
be decided. Fractions of a 28-day month? 29- 30- or 31- day month? Or maybe a
standardized 30.4375-day month?
Regards - Dave
On 12/07/2011, at 8:55 PM, Richard wrote:
> Hi All,
> I would appreciate a vba function
Hi,
Here is another option. It is slightly shorter, and doesn't produce a #VALUE!
error when the target cell does not contain the expected data.
=MID(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),11)
If your the 11 digit number always starts with a 1 or a 3, as it does in your
sample, then
Hi Ashish,
Exactly what I wanted. Didn't know about the .TextFrame part.
Thanks!
Regards - Dave.
Date: Thu, 9 Jun 2011 11:10:06 +0530
Subject: Re: $$Excel-Macros$$ Application.Caller
From: koul.ash...@gmail.com
To: excel-macros@googlegroups.com
below code will help you in extracting the tex
You can also format the cell
Regards - Dave
On 09/05/2011, at 9:17 AM, "STDEV(i)" wrote:
> Unfortunately Month Function returns Month Index ( 1 to 12 ) not Month NAME
> Assuming A1 containts date data : 12/31/2011
> Formula in B1 =MONTH(A1) returns : 12 not "December"
>
> In vba y
Hi Pravin,
You could try using the INDIRECT function
VLOOKUP(H17,INDIRECT("[SC231WK"&A1&"34.xls]Sheet1!$I$5:$K$20"),H$15,0)
Change the A1 ref to the cell that holds the book number you want
Please note that I haven't tested this. I haven't tried VLOOKUP with INDIRECT
before.
Regards - Dave
Da
Hi,
One way to do it would be:
-Find the last cell in your data (eg A1000)
-Multiply that cell's row number by 2 (eg 2000)
-Paste that cell's data into the cell with the doubled row number (eg A2000)
-Clear that cell (ie A1000)
-Decrement the row by 1 (ie A999)
-Loop to step 2
Regards - Dave.
Hi,
Without your file, I can't test this, but the XL did accept the following as a
valid formula:
=SUMIFS('1-Diary'!$H$9:$H9954,'1-Diary'!$B$9:$B9954," Date: Tue, 3 May 2011 05:47:53 -0700
> Subject: $$Excel-Macros$$ SUMIFS
> From: gmccaff...@acutus.co.uk
> To: excel-macros@googlegroups.com
>
You're welcome.
Regards - Dave
> Date: Mon, 25 Apr 2011 17:29:30 -0700
> Subject: Re: $$Excel-Macros$$ macro to add fill color to a column of selected
> range
> From: cje...@yahoo.com
> To: excel-macros@googlegroups.com
>
>
> That's excellent Dave. Exactly what I was looking for.
> Thanks!!
>
ds
Yogesh Gohil
On Sat, Apr 23, 2011 at 6:44 PM, Dave Bonallack
wrote:
Hi Yogesh,
At first glance, I'd say that you just have too many formulas in the workbook,
and that most of the calculations should probably be done by VBA rather than
worksheet functions. There appear to be some
Hi,
Something like:
Dim c as Range
For each c in Selection
If c.Column = 6 then c.Interior.Colorindex = 3
Next c
Note: Colorindex 6 will give you red. Change the number to suit.
Regards - Dave.
> Date: Sat, 23 Apr 2011 14:17:08 -0700
> Subject: $$Excel-Macros$$ macro to add fill color to
Hi Yogesh,
At first glance, I'd say that you just have too many formulas in the workbook,
and that most of the calculations should probably be done by VBA rather than
worksheet functions. There appear to be some UDF's, but the VBA is protected,
so I can't examine them, or make any decision on h
Hi Tom,
Problem is, when you use copy/paste in a macro, it uses the same clip board as
any other application that's running. The solution is probably to eliminate
copy/paste from your macro. It's a very inefficient way to run, and there's
nearly always a better and much quicker way to do the sam
Hi Jorge,
Your workbook didn't contain any macros.
Regards - Dave
Date: Tue, 5 Apr 2011 23:40:56 +0100
Subject: $$Excel-Macros$$ copy macro error
From: leote.w...@gmail.com
To: excel-macros@googlegroups.com
Hi, can someone please tell what i did wrong, this is my first macro all by
myself, b
Excellent formula!
Dave.
From: setiyowati.d...@gmail.com
Date: Sat, 26 Mar 2011 16:40:29 +0700
Subject: Re: $$Excel-Macros$$ discussexcel : Macro: Column Number to
Alphabetical reference
To: excel-macros@googlegroups.com
=SUBSTITUTE(ADDRESS(1,A1,4),1,"")
=SUBSTITUTE(ADDRESS(1,16384,4
Hi Akbar,
Have a look at the attached. Type "Akbar" anywhere on sheet 1
To do the vice-versa thing, enter similar code into the VBA Sheet 3 window.
Regards - Dave.
Date: Thu, 24 Mar 2011 11:36:15 +0500
Subject: $$Excel-Macros$$ How to prevent from duplication in one work book
From: sh.talal.ak
Hi Susan,
=MEDIAN(IF(F5:F17=1,H5:H17))
This is right, but it's an array formula, so you have to enter it with
Ctrl+Shift+Enter
When you do this, it gives the right answer.
Regards - Dave.
Date: Wed, 23 Mar 2011 14:39:36 -0700
Subject: $$Excel-Macros$$ Median If Formula
From: sunni...@gmail.co
Hi Sandra,
I may be a bit dim tonight, but it's not clear to me if the data sample you
provided is the format you want, or the format you currently have.
If this is the format you currently have, what format do you want?
If this is the format you want, what format do you currently have?
Regards -
Hi Santosh,
You have declared number variables as Integer, which have a maximum allowance
of (approx) +/- 32000.
Dim A As Integer
Dim B As Integer
Dim c As Integer
Try declaring them as Long
Dim A As Long
Dim B As Long
Dim c As Long
Regards - Dave.
Date: Sat, 19 Mar 2011 20:22:18 +0530
You're welcome
Dave
From: walpa...@hotmail.com
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ While Looping, looking for cells that start with
"D"
Date: Fri, 18 Mar 2011 15:55:08 -0600
Hi Dave,
Thank you for your response. Sounds like your suggestion should work just fin
0 32123
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Dave Bonallack
Sent: Friday, March 18, 2011 3:28 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Remove text from a string
Hi,
If your text is in A1, try this in B1:
=LEFT(A1,
Hi,
You can use an IF combined with LEFT, something like:
IF Left(A2,1)="D" Then (your process code here)
Regards - Dave.
> Date: Thu, 17 Mar 2011 11:39:35 -0700
> Subject: $$Excel-Macros$$ While Looping, looking for cells that start with "D"
> From: walpa...@hotmail.com
> To: excel-macros@googl
Hi,
If your text is in A1, try this in B1:
=LEFT(A1,SEARCH("~~",SUBSTITUTE(A1,"@","~~",LEN(A1)-LEN(SUBSTITUTE(A1,"@",""-1)
Regards - Dave
Date: Fri, 18 Mar 2011 13:46:12 +0530
Subject: $$Excel-Macros$$ Remove text from a string
From: deepakexce...@gmail.com
To: excel-macros@googlegroups.c
Hi Mrs Rum,
I'd probably just use my Office CD to repair or reinstal XL
Regards - Dave.
> Date: Thu, 17 Mar 2011 07:35:33 -0700
> Subject: $$Excel-Macros$$ Re: User-defined type not defined - error
> From: mrs...@gmail.com
> To: excel-macros@googlegroups.com
>
> Please? Anyone? I can't even jus
Hi,
After deciding that this was impossible, an idea came to me while I was in the
shower.
See the attached. B2 and B3 have data, so C2 and C3 should show the data entry
date of 16 March 2011, no matter when the file is viewed.
If you enter data into B4, the current date should appear in C4, and
Hi Hemant,
I don't think there's a non-macro solution for this.
Regards - Dave.
From: coolh...@gmail.com
Date: Sun, 13 Mar 2011 23:36:21 +0530
Subject: Re: $$Excel-Macros$$ formula to add date in next column
To: excel-macros@googlegroups.com
Hey STDEV(i),
Thanks a lot but i dont want macro,
10:12 PM, Karla Haman wrote:
I will give this a shot. Thank you so much.
Sent from my iPhone
On Mar 10, 2011, at 9:20 PM, Dave Bonallack wrote:
Hi,
One approach is to insert a new blank sheet, call it "END" and move it to
become the last sheet in the workbook.
Whenever ad
Hi,
One approach is to insert a new blank sheet, call it "END" and move it to
become the last sheet in the workbook.
Whenever adding new sheets, make sure they sit before the "END" sheet.
If your SUM formula was, for example:
=SUM(Sheet2:Sheet8!A1)
change it to:
=SUM(Sheet2:END!A1)
All sheets bet
Sumit Vyas
On Tue, Mar 8, 2011 at 7:07 PM, Paul Schreiner wrote:
Dave,
Hey! I'm an ENGINEER, not an ENGLISH major!
Or a veterinarian for that matter!
What kind of dog would jump over a fox anyway...
Always seemed (seems?) suspicious (spurious?), if you ask me...
lol.
Paul
From:
Hi Paul,
Completely OT, but it has to be "jumps", not "jumped", otherwise there's no "s"
in the sentence. -:)
Dave.
Date: Mon, 7 Mar 2011 06:13:04 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ ( ) for Array Must???
To: excel-macros@googlegroups.com
It really depends o
Hi Ayush,
Perhaps you could play with the word 'Accelerate'
Some suggestions would be:
Excelerated Learning
Excelerated Solutions
Excelerating.com
Exlceleration.com
Excelerate.com
Regards - Dave.
From: jainayus...@gmail.com
Date: Mon, 28 Feb 2011 11:07:31 +0530
Subject: Re: $$Excel-Macros
Hi,
The logic appears to be a bit weird:
If Not IsEmpty(rCell) Or HasDependents(rCell) = True Then
Range(rCell.Address).Select
Exit Sub
End If
If the first cell in the selected range is not empty, or has dependents, then
that cell is selected, and the sub exits.
What do you actually want the ma
I think someone is using my name in vain - unless there are two Dave's in the
world - and I don't know how that could be...
Dave.
Date: Thu, 17 Feb 2011 09:15:24 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ Import .csv & match records
To: excel-macros@googlegroups.com
Hi,
Sorry - didn't see your attachment.
Link your button to the following macro, which can just live in a module:
Sub refreshRange()
Range("B40:B54").Calculate
End Sub
Alternatively, you could do away with the button and have the macro fire
whenever you change B39. This macro would live in the V
Hi Aduh,
I assume that you have the Calculation set to Manual.
One way would be to use a selection change event that detects if the selected
cell has a formula in it, then calculates just that cell.
The following code goes into the VBA sheet window:
Private Sub Worksheet_SelectionChange(ByVal T
7 sheets, but Sheet(7) doesn't exist.
You mean that Sheet7 doesn't exist... Sheets(7) is the 7th element of the Sheets
ARRAY, which DOES exist.
Does that help? or was it so "wordy" that you fell asleep midway and woke up
with a keyboard imprint on your right cheek?
Paul
Hi Paul,
I have also noticed that after deleting and creating sheets, the Sheet numbers
are out of order, and can be missing altogether. ie in a workbook of 7 sheets,
one of them may be Sheet(11), while sheets 8 and 9 are not present at all.
So, my question is, why does this line of code (Sheets
trarray(5) = "over"
Strarray(6) = "the"
Strarray(7) = "lazy"
Strarray(8) = "dog"
and of course ubound(StrArray) gives the upper bound of the array (8).
I wrote a function LONG ago using this to accomplish what is now done with
txt-to-columns in E
Hi Paul,
I'm really interested in the part of the macro that I have highlighted below
(in case the highlighting doesn't travel well, I've marked each line with a *)
I've not seen the 'Split' function before. I looked it up in the Help, but
still couldn't make sense of it. If you have time, could
Hi,
If you want the same functionality without macros, you'll have to remove all
the buttons and replace them with cells containing hyperlinks. Let us know if
you need assistance with this.
Regards - Dave
Date: Fri, 11 Feb 2011 20:18:55 +0500
Subject: $$Excel-Macros$$ Button programming
From
wrote:
@ dave Try this
Wed, Feb 9, 2011 at 9:55 AM, Dave Bonallack wrote:
Hi Ashish,
Thanks for sending the file. Excellent. But for some reason, selecting a cell
with the left mouse button isn't recognised. I tried adding Rob's code, but it
didn't work prop
ssage box.
Regards - Dave
Date: Wed, 9 Feb 2011 09:03:03 +0530
Subject: Re: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office
2010 x64
From: koul.ash...@gmail.com
To: excel-macros@googlegroups.com
hi dave,
see if this helps
On Wed, Feb 9, 2011 at 5:52 AM, Dave Bonallac
Hi Rob,
I am interested in the trapping of a left mouse click, but couldn't understand
it from your posts. Could you please attach a workbook with working code?
Regards - Dave.
> Date: Tue, 8 Feb 2011 05:14:53 -0800
> Subject: $$Excel-Macros$$ Re: GetAsyncKeyState not working window 7 office
>
that the conversion can be done by formula.
> Regards
> Rajesh Kainikkara
> On 2/5/11, Dave Bonallack wrote:
> >
> > Hi Solomon,
> > Have a look at the attached. Most numbers-to-text conversions are done with
> > VBA, but this one is done with formulas and functions.
ion can be done by formula.
> Regards
> Rajesh Kainikkara
> On 2/5/11, Dave Bonallack wrote:
> >
> > Hi Solomon,
> > Have a look at the attached. Most numbers-to-text conversions are done with
> > VBA, but this one is done with formulas and functions. It uses a bunch of
Hi Solomon,
Have a look at the attached. Most numbers-to-text conversions are done with
VBA, but this one is done with formulas and functions. It uses a bunch of cells
to do its workings. In your case I have put those cells in the range A50:K62.
If you need that range for something else, then y
Hi,
I think that mm will return 02, while m will return 2
Regards - Dave
From: anil.bha...@tatacommunications.com
To: excel-macros@googlegroups.com
Date: Thu, 3 Feb 2011 14:02:16 +0530
Subject: RE: $$Excel-Macros$$ HOW TO CONVERT DATE IN TEXT WITH FOMULA
Hi Aamir,
Please see the differ
Hi,
IFERROR can be stacked, just like IF can be.
Try this formula in C4, then copy down:
=IFERROR(VLOOKUP(B4,$E$3:$F$3,2,0),IFERROR(VLOOKUP(B4,$H$3:$I$3,2,0),VLOOKUP(B4,$K$3:$L$3,2,0)))
Regards - Dave
Date: Tue, 25 Jan 2011 22:57:34 +0500
Subject: $$Excel-Macros$$ Lookup values in differen
Hi Manish,
Please attach the solution you have. Perhaps one of us may be able to speed it
up.
Regards - Dave
> Date: Tue, 18 Jan 2011 23:24:56 -0800
> Subject: Re: $$Excel-Macros$$ Re: Concatenate Value Problem
> From: pansari.man...@gmail.com
> To: excel-macros@googlegroups.com
>
> Any
:10 AM, ashish koul wrote:
check the attachment see if it helps you
On Tue, Jan 18, 2011 at 10:36 AM, Dave Bonallack
wrote:
Sorry Aamir, Can't understand the question. If your worksheet had a cell with
the desired answer in it (entered manually) this may help me understand your
re
Sorry Aamir, Can't understand the question. If your worksheet had a cell with
the desired answer in it (entered manually) this may help me understand your
request.
Regards - Dave.
From: aamirshahza...@gmail.com
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Query
Date: Mon, 17 J
ime
a2 has OUT, b2 has date, and c2 has time
On Jan 15, 12:49 am, Dave Bonallack wrote:
> Hi,
> You need to tell us how this data is entered.
> Is each line 1 cell, 2 cells, or 3 cells?
> Regards - Dave
>
>
>
>
>
>
>
> > Date: Fri, 14 Jan 2011 23:11:40
Hi,
You need to tell us how this data is entered.
Is each line 1 cell, 2 cells, or 3 cells?
Regards - Dave
> Date: Fri, 14 Jan 2011 23:11:40 -0800
> Subject: $$Excel-Macros$$ there has to be an easier way!
> From: gold.her...@gmail.com
> To: excel-macros@googlegroups.com
>
> Hi,
>
> my timecar
Hi David,
Sounds like your spreadsheets are using different calendars systems.
Excel can use the 1900 system or the 1904 system. 1900 is the default setting.
In XL2003, go to Tools, Options, Calculations Tab. There make sure that the
1904 thingy is unchecked.
Do the same for both workbooks.
In XL
ave,
Why am I getting #NUM! with the formula? (See Attached)
Thank you.
John
On Fri, Jan 14, 2011 at 10:35 AM, Dave Bonallack
wrote:
Hi,
I would like to submit the following formula as one I like.
It performs a case-sensitive Vlookup, and is non-array.
=LOOKUP(2,FIND(A1,Sheet2!A:A),S
Hi,
I would like to submit the following formula as one I like.
It performs a case-sensitive Vlookup, and is non-array.
=LOOKUP(2,FIND(A1,Sheet2!A:A),Sheet2!B:B)
A1 contains the lookup value
Sheet2!A:A is the lookup column
Sheet2!B:B is the return column
I hasten to add that this formula is
s,
Noorain Ansari
On Fri, Jan 14, 2011 at 1:34 AM, Dave Bonallack
wrote:
Hi,
The problem is not the sheet names, but the indirect refering to a date, which
is really just a date code, not a date as displayed.
Regards - Dave.
Date: Thu, 13 Jan 2011 23:08:24 -0800
Subject: Re: $$Excel-Mac
Hi,
The problem is not the sheet names, but the indirect refering to a date, which
is really just a date code, not a date as displayed.
Regards - Dave.
Date: Thu, 13 Jan 2011 23:08:24 -0800
Subject: Re: $$Excel-Macros$$ Formula of the week - share your best formula
From: noorain.ans...@gmail.
Hi,
Not sure if I'm missing the point here, but what about just typing the date
into your first cell, then press enter. From there, select your first cell and
drag the copy-down handle down as far as you want. XL defaults to incrementing
the date one day at a time.
Regards - Dave.
> Date: Wed
Hi Manish,
Place this code in the appropriate VBA sheet window
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 2 Then Cells(Target.Row, 3).ClearContents
End Sub
Hope this helps
Regards - Dave.
> Date: Wed, 12 Jan 2011 02:27:22 -0800
> Subject: $$Excel-M
Hi Ayush,
There seems to be some confusion. I thought the question related to our
favourite formula, not favourite function. Please clarify.
Regards - Dave.
From: rohan.j...@gmail.com
Date: Wed, 12 Jan 2011 16:01:19 +0530
Subject: Re: $$Excel-Macros$$ Formula of the week - share your best for
Hi,
If you are using xl2007 or more, you can use the IFERROR function.
In your case, I think the syntax would go something like this:
=IFERROR(INDIRECT(A1&"D$65")*1,"Linked Workbook not open!")
But I don't have XL2007 here to check it.
Hope this helps.
Regards - Dave.
> Date: Mon, 10 Jan 2011 0
Hi,
Have a look at the attached. I've started with just '1' in the first cell, then
a formula after that, copied down to about row 30. Copy it down as far as you
need. The appropriate sequential number will appear when you enter data into
Col B cells. It is also self-correcting if you delete da
Hi Kalyan,
The attached works, with the following limitations:
That the data in Col A is always the same (as in your sample data)
That any given BP Name is not repeated in more than 1 Depot (as in your sample
data)
If either of the above limitations are unacceptable, then it's back to the
drawin
Hi,
A UDF (User Defined Function) is like any other function. It can only return a
value. It cannot do anything else. To change the background colour of a cell,
use Conditional formatting or a regular macro.
Regards - Dave.
Date: Wed, 5 Jan 2011 06:50:53 +0530
Subject: Fwd: $$Excel-Macros
s 7
Regards - Dave.
Date: Wed, 29 Dec 2010 12:12:16 +0500
Subject: Re: $$Excel-Macros$$ how to get month Occurrence in no.
From: sajidmansooral...@gmail.com
To: excel-macros@googlegroups.com
Really Impressive!
Let me know how this formula works
On Tue, Dec 28, 2010 at 3:43 PM, Dave Bonallac
Hi,
To just do the 3 levels you asked about, try:
=IF(B16<500,3.5,IF(B16<1000,2.75,2.25))
Regards - Dave.
Date: Tue, 28 Dec 2010 20:18:30 +0530
Subject: Re: $$Excel-Macros$$ Nested IF functions?
From: dilipan...@gmail.com
To: excel-macros@googlegroups.com
Hi J D,
á
It would be really apprec
, STDEV :)
Best Regards,
DILIPandey
On Tue, Dec 28, 2010 at 4:13 PM, Dave Bonallack
wrote:
Hi,
A shorter version would be:
=MONTH(--(1&A1))
This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended
abbreviations (eg Sept) and also unabbreviated (eg June, November)
Reg
Hi,
A shorter version would be:
=MONTH(--(1&A1))
This works for 3-lettered abbreviatios (eg Mar, Oct) as well as extended
abbreviations (eg Sept) and also unabbreviated (eg June, November)
Regards - Dave.
Date: Tue, 28 Dec 2010 15:34:01 +0700
Subject: Re: $$Excel-Macros$$ how to get month Occ
Hi Susan,
Not sure if this is the problem, but I thought I'd mention it;
When using a sheet name, the syntax is:
Worksheets("Base Scenario") or just Sheets("Base Scenario")
But when using the sheet number, neither quotes, nor the word 'Sheet' are not
used inside the brackets.
So it just becomes:
to autofill upto the
adjacent column.
Thanks,
Vebhav Jain
On Mon, Dec 13, 2010 at 8:32 AM, Dave Bonallack
wrote:
Hi Vebhav,
You seem to be trying to autofill a range based on where the range currently
ends. Are you sure you don't want to fill Col 8 as far down as data in an
adjacent column
Hi Vebhav,
You seem to be trying to autofill a range based on where the range currently
ends. Are you sure you don't want to fill Col 8 as far down as data in an
adjacent column?
Regards - Dave.
Date: Mon, 13 Dec 2010 07:40:24 +0530
Subject: $$Excel-Macros$$ Error
From: vebhav.j...@gmail.com
t; The code is working well, Thank u very much
>
> Regards
> Rajesh kainikkara
>
>
>
> On 12/9/10, Dave Bonallack wrote:
> >
> > Hi,
> > Try this.
> >
> > Sub Macro6()
> > If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect El
Hi,
Try this.
Sub Macro6()
If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Else
ActiveSheet.Protect
End Sub
Regards - Dave.
> Date: Wed, 8 Dec 2010 20:49:58 +0530
> Subject: $$Excel-Macros$$ how to protect & unprotect the sheet with same
> button
> From: rajeshkainikk...@g
rstood my query.
On Wed, Dec 8, 2010 at 9:45 AM, Dave Bonallack
wrote:
Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you automate the pr
Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you automate the process.
Regards - Dave.
Date: Wed, 8 Dec 2010 06:51:01 +0530
Subject: Re: $$E
Hi Vebhav,
You say you need to change the highlighted code daily, but you don't say what
you change it to, or on what basis it is changed. We need to know this if we
have any chance of helping you automate the process.
Regards - Dave.
Date: Tue, 7 Dec 2010 22:33:00 +0530
Subject: $$Excel-Mac
s for the correction and sorry for being so dumb :D.
Warm Regards,
Harmeet Singh
IT Analyst
McKinsey & Company
http://www.facebook.com/Harmeeet
On Fri, Dec 3, 2010 at 1:16 PM, Dave Bonallack
wrote:
Hi,
Harmet, your formula is using relative values for the SUM(B2:B8) section, and
as a re
Hi,
Harmet, your formula is using relative values for the SUM(B2:B8) section, and
as a result, has created errors while copying down.
Formula in D2 should look like this:
=B2/SUM($B$2:$B$8)
Then copy down.
Regards - Dave.
From: harmeet.hew...@gmail.com
Date: Fri, 3 Dec 2010 11:36:59 +1100
Sub
so seems that you have done conditional formatting in the main table.
> > But how is that only the month which am sorting is showing red in
> > colour??You need to explain me this step too.
> > *
> > *Regards*
> > *Anindya
> >
> > *
> > On Mon, Nov 29, 201
ot for your response and your example, it
> works like a charme !
>
> I'm sorry about the month for responding to you !
>
> again : thanks a lot, it will a super example to learn more about
> timers !
>
> José
>
> On 11 oct, 11:29, Dave Bonallack wrote:
> > Hi
0 at 7:59 AM, Dave Bonallack
wrote:
Hi Girish,
in "USED VLOOKUP HERE" column i used Vlookup function taken table _array as
"SYSTEM DATA"
This is not a formula. I need the actual formula you used so I can see what you
are trying to achieve.
I don't mind persisting wi
ery huge
so i need any easy logical function having same answer as in Column "USED
VLOOKUP HERE"
hope u understand
Thanks in advance
Girish
On Tue, Nov 2, 2010 at 10:08 AM, Dave Bonallack
wrote:
Hi,
Your sample sheet gives insufficient and contradictory info.
Column B has a heade
Hi,
Your sample sheet gives insufficient and contradictory info.
Column B has a header which says "USED HERE LEFT FUNCTION" but there is no left
function used there - the data is just entered as text, and the length of text
varies, which means that the left function probably wasn't used.
Column
Hi,
The code line: Cells(Rows.Count, 1).End(xlUp).Row only finds the last cell in
Column A, which is not necessarily the last row of data. Column C (for example)
may have a lot more data in it than Column A.
What kind of trouble have you had with
ActiveCell.SpecialCells(xlLastCell).Select ?
Re
Hi,
You can test for a non-contiguous selection with the following line of code:
A = Selection.Areas.Count
Use this in an If statement - eg
If Selection.Areas.Count > 1 then msgbox "You can't use non-contiguous cells.":
Exit Sub
Hope this helps.
Regards - Dave.
> Date: Thu, 28 Oct 20
an you please give me your email id so that
> i can communicate directly? I have a long database now.
>
> On Oct 21, 6:10 pm, Dave Bonallack wrote:
> > Hi,
> > Have a look at the attached to see if it does what you need.
> > Regards - Dave.
> >
> >
> >
Hi,
Have a look at the attached to see if it does what you need.
Regards - Dave.
> Date: Wed, 20 Oct 2010 20:04:57 -0700
> Subject: $$Excel-Macros$$ Finding student whose marks are improving over
> three consecutive tests
> From: vaaibhavjhav...@gmail.com
> To: excel-macros@googlegroups.com
>
Hi Alfred,
Please don't apologise for your English. We are quite tolerant here.
Have a look at the attached. First click on the 'Subject' buttons. You will
notice that clicking any one of them puts a "Yes" in it's Row, and changes the
other Rows to "No".
Use these buttons to select which subj
Hi,
"For every unique value of col A - C and col F, then the number is increased by
1 from col G unless there is already a number for that combo in col H, then it
gets increased by 1 from that number in col H"
I think I understand the first part of the sentence, but the meaning of the
seco
And from me too, Ayush, congratulations! Very good service you provide here.
Lots of work and time behind the scenes. Thank-you.
Dave.
From: shubhangidesa...@gmail.com
Date: Sun, 3 Oct 2010 16:46:13 +0530
Subject: Re: $$Excel-Macros$$ Ayush Jain – Microsoft MVP 2010
To: excel-macros@googlegr
Hi,
A simpler alternative for your second requirement:
ActiveWindow.ScrollRow = 50
Regards - Dave.
> Date: Tue, 28 Sep 2010 16:08:14 -0700
> Subject: $$Excel-Macros$$ Visible Row Below Freeze Pane
> From: spa...@corbetteer.co.uk
> To: excel-macros@googlegroups.com
>
> With the top row
Hi,
"How would I prove row 32 is the first visible row through VBA"
A = Activewindow.VisibleRange.Row
"If i then wanted to make row 50 the first visible row, how could it be done
through VBA"
Range("A2").Select
ActiveWindow.SmallScroll Down:=48
There's probably a better way of doing t
)) Then
> ActiveSheet.Cells(R, "E").Value = Dict_E.Item(Datainx)
> ActiveSheet.Cells(R, "F").Value = Dict_F.Item(Datainx)
> Else
> Cells(R, "A").Select
> MsgBox "Missing data for row: " & R
ict_F.Item(Datainx)
> Else
> Cells(R, "A").Select
> MsgBox "Missing data for row: " & R
> End If
> Next R
>
> 'display processing time
> tstop = Timer
> TMin = 0
> T
A further question: Do you want the date in Col B to enter only when the
adjacent cell in Col A receives text for the first time, or any time the
adjacent Col A cell is changed?
Regards - Dave.
Date: Tue, 28 Sep 2010 23:40:09 +0530
Subject: $$Excel-Macros$$
From: sunscel...@gmail.com
To: e
1 - 100 of 561 matches
Mail list logo
