Hi Anil
Perhaps u can try this as well
Sub Mtest()
Dim rng As Range, c As Range
Dim i As Long
Set rng = Range(A2:A Cells(Rows.Count, 1).End(xlUp).Row)
For i = 65 To 90
For Each c In rng
If Not IsEmpty(c) Then
c.Replace Chr(43),
c.Replace Chr(i),
End
Dear friends can u please check the attached for the above subject.
Regards,
Muneer,
CC...
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem,
attachment??
On Sat, Apr 7, 2012 at 12:08 PM, Mohammed Muneer mmun...@ccc.com.qa wrote:
Dear friends can u please check the attached for the above subject.
** **
** **
Regards,
Muneer,
CC...
** **
** **
--
FORUM RULES (986+ members already BANNED for
=COUNTIF($A$2:$A$1393,A2)
or
=COUNTIF(A:A,A2) in a2 and copy down
On Sat, Apr 7, 2012 at 12:32 PM, Mohammed Muneer mmun...@ccc.com.qa wrote:
Please find the attachment.
** **
** **
Regards,
Muneer,
CC...
** **
** **
--
FORUM RULES (986+ members already
but this formula is counting 2 of unique numbers also.
Aamir Shahzad
On Sat, Apr 7, 2012 at 12:10 PM, The Viper viper@gmail.com wrote:
=COUNTIF($A$2:$A$1393,A2)
or
=COUNTIF(A:A,A2) in a2 and copy down
On Sat, Apr 7, 2012 at 12:32 PM, Mohammed Muneer mmun...@ccc.com.qawrote:
Do you want this :
=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF($A$2:A2,A2),)
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Mohammed Muneer
Sent: Apr/Sat/2012 12:33
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ count formula
A
Ojemeni Uzoma | Call 08060700717
-Original Message-
From: Aamir Shahzad aamirshahza...@gmail.com
Sender: excel-macros@googlegroups.com
Date: Sat, 7 Apr 2012 12:18:21
To: excel-macros@googlegroups.com
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ count formula
?
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Ojemeni Uzoma
Sent: Apr/Sat/2012 03:13
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ count formula problem...
A
Ojemeni Uzoma | Call 08060700717
_
From: Aamir
?
On Sat, Apr 7, 2012 at 9:48 AM, Rajan_Verma rajanverma1...@gmail.comwrote:
Do you need any assistance ??
** **
*From:* excel-macros@googlegroups.com [mailto:
excel-macros@googlegroups.com] *On Behalf Of *Atef Gobran
*Sent:* Apr/Sat/2012 06:35
*To:* excel-macros@googlegroups.com
Do you want to show this web page on group J ?
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Maries
Sent: Apr/Sat/2012 05:58
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$
?
On Sat, Apr 7, 2012 at 9:48 AM,
Charlie, Either of these test fine to remove ** or more
Sub DeleteFilteredRowsSAS()
With Range(B5:B Cells(Rows.Count, 2).End(xlUp).Row)
.AutoFilter field:=1, Criteria1:=~**
.SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter
End With
End Sub
Sub delrowsSAS()
Dim i As Long
For i =
Hello Experts,
Still i am not getting reply with the below request. Could you please
advice me the same.
Waiting for your reply.
Regards
Lokesh.M
On Wed, Apr 4, 2012 at 7:27 PM, Lokesh Loki lokeshsmg2...@gmail.com wrote:
Hi Experts,
May i know that is there any Excel Power point Vba
Hi Lokesh,
Are you asking for information about online classes?
If so, you might be interested in:
http://chandoo.org/wp/vba-classes/
http://academy.excelhero.com/excel-hero-academy-tuition/
http://www.ozgrid.com/Excel/free-training/basic-index.htm
Not strictly web-based:
Hi,
I want to reorder products by rank in a particular order. It works fine when
the rank is unique. But when there are two products of same rank then it does
not. Please help.
I reorder using index formula.
Thanks in advance,
Joseph
Sent on my BlackBerry® from Vodafone
--
FORUM RULES (986+
You can sort in descending order
Rajan.
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of joseph.cam...@gmail.com
Sent: Apr/Sat/2012 10:56
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Order by Rank
Hi,
I want to
Hi
Can yu pls attached sample worksheet ?
On Sat, Apr 7, 2012 at 10:55 PM, joseph.cam...@gmail.com wrote:
Hi,
I want to reorder products by rank in a particular order. It works fine
when the rank is unique. But when there are two products of same rank then
it does not. Please help.
I
Yes it is either ascending or descending. But if there are two ranks the same
for example 3 is twice then it does not work.
Sent on my BlackBerry® from Vodafone
-Original Message-
From: Rajan_Verma rajanverma1...@gmail.com
Sender: excel-macros@googlegroups.com
Date: Sat, 7 Apr 2012
Sigh, the things I know I don't know are increasing faster than the things
I know.
I get a 1004 error on the assignment to stoprowdata below. Sheets(RawData)
has been used successfully before so I do not think that is wrong. So I
assume the problem is
in the rest of the statement. But if so
Hi Group,
Attached is a sample excel tracker in which the leaves taken by every
employee is entered in a single sheet (Data).
Now this is data has to split-ted in to respective months with the count
type .
Some Sample data is entered for the month of April12 for easy reference.
--
Regards
Pivot table can be useful here.
See the attached sheet
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of santosh subudhi
Sent: Apr/Sun/2012 01:05
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Leave tracker
Hi Group,
Then we need to include more criteria to make the rank , can you attached a
file
Rajan
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of joseph.cam...@gmail.com
Sent: Apr/Sat/2012 11:14
To: excel-macros@googlegroups.com
Subject:
In this case it will be impossible for the match function to not find
a match but thanks. No idea on why the assignment
to stoprawdata fails?
On Sat, Apr 7, 2012 at 1:17 PM, Rajan_Verma rajanverma1...@gmail.com wrote:
If match function dost not find any value it return an error , to escape
Pivot will work for huge data also.. and if you are talking about formula
,with large data it can be cause to make your processing slow, macro can be
a option.
Rajan
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of santosh subudhi
Sent: Apr/Sun/2012 02:02
In this case match function will return a result if it value match in range,
but it will not return an error if values does not match in Range..
On Error is just to escape from error..
Rajan.
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
I am not worried about the match function not finding a match,
assuming the match line of code is written correctly. It is
impossible to not have a match.
If the code is incorrect anything can happen. But forget the match
function for now. Do you have any idea why this code gives error 1004
Hi,
PFA. Change A1. You will get answer for any months.
Regards,
MARIES.
On Sun, Apr 8, 2012 at 12:34 AM, Rajan_Verma rajanverma1...@gmail.comwrote:
Pivot will work for huge data also.. and if you are talking about formula
,with large data it can be cause to make your processing slow, macro
Hi Maries,
Thank you for quick resolution.
I have a small query I found { } brackets in your formula,can you just help
me in understanding when to use these brackets.
Regards
Santosh Kumar Subudhi
Hi,
PFA. Change A1. You will get answer for any months.
Regards,
MARIES.
On Sun, Apr 8, 2012
This statement is returning 99 though I deleted everthing below the
actual data to make sure no cells below it were used
stoprawdata = Sheets(RawData).UsedRange.Rows.Count - 1
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor
you should highlight the entire rows (and same for extra columns),
right-click, delete
the delete key won't cut it (clears the text and formulas but leaves
formats)
or I select those rows/columns, then Alt-E,D
if selecting some cells but not entire row/column, you can delete entire row
with
Did you delete rows using the row indicator at the left of the sheet?
Did you SAVE the file after so doing?
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: tangledweb
Sent: Saturday, April 07, 2012 4:24 PM
To: excel-macros@googlegroups.com
Subject:
Ok then I can not use this method at all becaues the columns have
formats set for the entire column (really not efficient for that to be
part of userange).
And I am guessing this statement that I thought was efficient is
actually clearing the entire spreadsheet and not jus the part with
data
Yes and no, but if formats count as part of usedrange then this method
will not work as the formats need to stay in the entire columns which
will have different numbers of rows filled at different times.
On Sat, Apr 7, 2012 at 2:34 PM, dguillett1 dguille...@gmail.com wrote:
Did you delete rows
I would probably design the project differently but
Put this is c2 and copy across and down
=SUMPRODUCT((Data!$A$2:$A$270=$A2)*(Data!$B$2:$B$270=$B2)*(Data!$C$2:$C$270=C$1))
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: santosh subudhi
Sent: Saturday, April 07,
It would seem that delete does remove the format, But when I delete
all extra rows, all extra columns, and save the file, I still get the
same 99 answer.
On Sat, Apr 7, 2012 at 2:42 PM, Domain Admin domainqu...@gmail.com wrote:
Yes and no, but if formats count as part of usedrange then this
If you mean { } this is because it is an array formula where you do NOT put
in the brackets. You save or change the formula using ctrl+shift+enter instead
of just enter.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: santosh subudhi
Sent: Saturday, April 07,
Provide a file with examples
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message-
From: Domain Admin
Sent: Saturday, April 07, 2012 3:42 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ runtime error on assignment trying to find
not well documented but entire row and column formats are only stored once
in your file and do not effect usedrange.
Yeah, your efficient statement will clear the used range of the spreadsheet
except for the first row. it will also clear the next row down from the
used range (which was already
I solved the problem of the runtime error. Now it is the problem in
the other thread where the returned value is the total row count of
the spreadsheet instead of just the part with data. Does format
setting in a cell really make that cell part of usedrange?
On Sat, Apr 7, 2012 at 2:53 PM,
I figured the extra row was cleared but that is not a problem.
It seems on testing that clearcontents is working fine leaving the
formats alone, It appears that delete is also leaving the formats
alone. But this still leaves me with 2 problems
1. why does usedrange seem to be returning the
p.s. other methods of identifying the real data besides .currentregion are:
1. Range.End(xlup) and range.end(xlleft) (lookup range.end in help) applied
to the last cell at the extent of the entire worksheet in a used column or
row. This method only considers that one column/row.
2.
integer type only goes to 32768 - use long
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of dguillett1
Sent: Saturday, April 07, 2012 2:53 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ runtime error on
I made that change which is how I got to the current problem of 99
Integer should have been plenty if the usedrange was returning the
actual used range.
On Sat, Apr 7, 2012 at 3:18 PM, Asa Rossoff a...@lovetour.info wrote:
integer type only goes to 32768 - use long
-Original
bardate is a column do you mean it's a range object?
then instead of
Sheets(RawData).BarDate
just use:
BarDate
For more help pleas post whole procedure
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent:
I can use a loop to find the end, but why is usedrange returning all
rows? That seems to be a fundamental issue.
On Sat, Apr 7, 2012 at 3:13 PM, Asa Rossoff a...@lovetour.info wrote:
p.s. other methods of identifying the real data besides .currentregion are:
1. Range.End(xlup) and
.UsedRange.Offset(-1)
What range would that be??
UsedRange always starts in A1 and end in the last lower right cell for which
Excel has data or formats.
Offset returns a range shifted by a certain number of rows and/or columns.
If UsedRange = [A1:C10]
UsedRange.Offset(-1) = [A0:C9]
Of course
UsedRange doesn't do that.
In debug pane type:
? usedrange.address
to verify what it returns.
Also, please post procedure or file, otherwise we have to make a lot of
guesses and assumptions
Asa
-Original Message-
From: excel-macros@googlegroups.com
No not a range object, just a reference. But here is the entire
program. I removed the currently unused portions and ignore Public
just have not bothered to change that yet.
' Column reference objects
Public BarDate As Double
Public ConDate As Double
Public Contango As Long
I fixed this. This thread should die now.
On Sat, Apr 7, 2012 at 3:28 PM, Asa Rossoff a...@lovetour.info wrote:
.UsedRange.Offset(-1)
What range would that be??
UsedRange always starts in A1 and end in the last lower right cell for which
Excel has data or formats.
Offset returns a range
usedrange does give back the expected range which leads me to believe
my use of .rows.count must be in error
On Sat, Apr 7, 2012 at 3:29 PM, Asa Rossoff a...@lovetour.info wrote:
UsedRange doesn't do that.
In debug pane type:
? usedrange.address
to verify what it returns.
Also, please
The term reference in Excel, when referring to a cell or range, means the
textual description of that cell or range (the Address property of a range
object returns the range reference). In VBA and other object oriented
languages, reference also refers to variables that refer to an object, but
Provide a file with examples
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message-
From: Domain Admin
Sent: Saturday, April 07, 2012 5:20 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ runtime error on assignment trying to
Thanks but this thread is solved/ended
On Sat, Apr 7, 2012 at 4:27 PM, dguillett1 dguille...@gmail.com wrote:
Provide a file with examples
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
-Original Message- From: Domain Admin
Sent: Saturday, April 07,
I read that after I posted. But doesn't that mean
Sheets(Results).Cells.Resize(-1).Offset(1).Delete
that you suggested would be deleting ever row in the sheet except the
header and not just the used range?
In any case I am still screwed on this returning 100
stoprawdata =
I'm looking for assistance in fixing a problem a user found.
I have a form created in Excel 2010. All cells on the form were locked -
then specific cells were unlocked. This allowed the users to only modify
the 'blank' fields in the form.
The worksheet was then protected using Protect Sheet
Well the usedrange problem is solved. I printed out just the cell
count of the used range and it indicated there was a column with data
somehow even though invisible.
So I deleted the column and that reduced the number. So I thought
there must still somehow be data in the empty rows so I did
I notice in all the other match functions they are inside a
With Sheets(somesheet)
so I tried putting sheets(rawdata).worksheetfunction ...
but still failed though I did not expect it to work since the match
function is trying to work across sheets. I hoped it might since
maybe it just needed
Hi,
you can use index with match or offset function, need file to elaborate.
On Sun, Apr 8, 2012 at 2:00 AM, Rajan_Verma rajanverma1...@gmail.comwrote:
Then we need to include more criteria to make the rank , can you attached
a
file
Rajan
-Original Message-
From:
Hi Howard,
With objectname
Only takes effect in specific instances of using a leading .. Nothing
else in the with block is effected. It's an anonymous (no variable name)
equivalent to:
Set MyObject = objectname
'and then placing MyObject before each of those naked periods you
would
Thanks for that. I will check it all out. I did create my own
equivalent and it works
contangoindex = 1
Do While Sheets(RawData).Cells(2, BarDate).Value
Sheets(ContangoSource).Cells(contangoindex, ConDate).Value
contangoindex = contangoindex + 1
Loop
On Sat, Apr 7, 2012
I just checked the help for WorksheetFunction.Match; the second argument is
specified as an array, not a range.
You may be running into an out of memory condition since Excel is probably
copying the range to an array in memory. Even if Excel is smart enough to
limit the array size to UsedRange
that you suggested would be deleting ever row in the sheet except
the header and not just the used range?
Correct. Each time you refer to UsedRange, Excel spends a moment figuring
out what range you've used. That takes a moment more than needed when you
don't care what you've used, you just
I typed this a couple hours ago, didn't send as I started replying to your
other messages --
Thanks :)
Double is a floating point binary, with an approximate decimal
representation. Although worksheetfunction methods actually return a
Double, since that is the only numeric representation used
I tried what you said. Is it trying to make an array out of a range,
or just restrict the range (looks more like the latter). Still got
the error Unable to get the match property of the worksheetfunction class.
I have verified all the individual components have correct values so
still seems like
Another one I started typing didn't finish and send.
(5) In your rawdata loop, there is no need for parenthesis around
the boolean expression. VBA will always evaluate the entire expression
between If/Then separately from the rest of the If/Then statement. I
haven't analyzed your loop too
Ranges and arrays are handled distinctly from each other, although often you
can get away with the same syntax in VBA.
I'd have to look for examples for you, not sure. Try search terms. If you
search for code online, you might find the wealth of VB6 code online useful
(although not so much in
This is interesting. It would seem to allow for the possibility of
filling in my entire results sheet as an array then dumping it to the
sheet all at once.
But probably not since there is no apriori way to know how many rows
there will be.
On Sat, Apr 7, 2012 at 8:56 PM, Asa Rossoff
I did not see those posts. I will look for them. Do not look for
examples. I can do that.
Just if there was a well known one.
On Sat, Apr 7, 2012 at 9:08 PM, Asa Rossoff a...@lovetour.info wrote:
Ranges and arrays are handled distinctly from each other, although often you
can get away with
You don't need to know in advance the size of your output array, you can use
ReDim Preserve to enlarge it as you go.
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Saturday, April 07, 2012 9:10 PM
To:
Hi,
As Don explained, I have used Array Formulas.
Check below link for more details,
http://www.excelfunctions.net/Excel-Array-Formulas.html
Regards,
MARIES.
On Sun, Apr 8, 2012 at 1:50 AM, dguillett1 dguille...@gmail.com wrote:
If you mean { } this is because it is an array formula
That gave me an idea but it did not work. I thought maybe it could
not find the match function because that meant it wanted one where the
second variable was a range instead of an array so I tried your
conversion. I may not have done it right of course. In the earlier
match functions is
It's a range, but if you pass that range reference to a function expecting
an array, VBA will create an array based on the range's Value property.
Another debugging tactic is to try your statement using literal values to
the extent possible to simplify the expression.
?
You have given me enough example to see the depth of your
understanding indicate if you don't know probably no one does .
contangoindex = WorksheetFunction.Match(Sheets(RawData).Cells(2,
BarDate).Value, Sheets(ContangoSource).Range(a2:a500), 0)
same error which is not surprising as I already
I tried this little variation:
Function matchtest() As Long
Dim contangoindex As Long
Dim CellsOfInterest As Variant
Dim InputRange As Range
Set InputRange = Application.Intersect(Sheet2.UsedRange,
Sheet2.Columns(1))
CellsOfInterest = InputRange.Value
' Determine where the
Now seriously take a break.
You're right :)
However, no, others often know things that stump me. And my knowledge is
superficial in some areas, and I make assumptions..
ttyl :)
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread
When you did not use the intersection was the error unable to find the
match function?
In any case
contangoindex = 1
Do While Sheets(RawData).Cells(2, BarDate).Value
Sheets(ContangoSource).Cells(contangoindex, ConDate).Value
contangoindex = contangoindex + 1
Loop
works, is
It was type mismatch
Oh, well. VBA is interpreted (when compiled it compiles to a bytecode, not
executable code) so as a matter of practice it's good to avoid loops when
there are good alternatives, but if the speed is not an issue in your case
whatever works.
-Original Message-
From:
Not sure I follow that. Unless it creates an indexed database that it
uses internally or creates hash tables or some other fast indexing
then the match function still has to
test all the entries until it finds a match. If I write it directly
in machine language that is still the case. That
77 matches
Mail list logo
