Use it with IF and Len()
Rajan,
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Thu/2012 02:51
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Could this loop be replaced by some range
method
If you want to know the column count of array you can use this
Sub ColumnCountofArray()
Dim strArr()
strArr = Range("A1:D10")
MsgBox "Column of StrArr =" & UBound(strArr, 2)
End Sub
http://excelpoweruser.blogspot.in/2012/03/column-count-of-array.html
Rajan
From: exce
If this was a compiled language I would say compiler error and could
probably get
around it by just sticking some dummy statement in front of it. Which
is essentially
what you did with the double transpose.
On Wed, Apr 11, 2012 at 5:53 PM, Asa Rossoff wrote:
> To retrieve a multi-column array I
Agree row and column is not a worksheet concept.
But it is as you say a function of the way arrays are stored in memory.
And they might as well make it match the way excel is used.
It also defines the most efficient way to nest loops in multidimension arrays.
Redim preserve does not say you can onl
Yeah that 'splains it
On Wed, Apr 11, 2012 at 8:38 PM, Asa Rossoff wrote:
> See if
> https://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
> makes clear how to enter an array formula that returns an array of results.
>
>
>
>
>
> -Original Message--
> So if redim preserve will only let you extend the column value which
> I do not get as that is almost never what anyone would want, can you
> transpose the array, do the redim, then transpose back?
The concept of row and column is a worksheet concept. In VBA an array
doesn't have a "physical ori
Well Holmes you've done it again. Funny I tried to use round on a
range in the worksheet too I could not get it to work.
Putting the round into a vacant cell of course would not work and I
could not figure out how to have it operate on
the existing cells in place.
On Wed, Apr 11, 2012 at 7:13 PM,
Well you completely lost me. I do not see the reason for and output
and input array.
I also see a redim on a range and I thought they only worked on arrays.
Any I expected to do something much simpler (syntax may not be correct here)
dim outputarray(somerowsize, 8)
dim resultsrange as range
for
- I tried the formula on a worksheet range do double check it correctly
returned the desired array. it did.
- I assumed Excel had some kind of special handling of the formula given
that caused it to just return one element from the array...
- I tried to modify the formula to see if it avoided the s
Just curious how you ever even thought of trying the double transpose.
On Wed, Apr 11, 2012 at 6:51 PM, Asa Rossoff wrote:
>> Whatever would make you think of it that being the case?
> Sorry, makes me think of what being the case?
>
> -Original Message-
> From: excel-macros@googlegroups.c
> Whatever would make you think of it that being the case?
Sorry, makes me think of what being the case?
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Wednesday, April 11, 2012 6:18 PM
To: excel-macros@googlegr
As you suggested in another post, yes, if you transpose the array you should
have an array with rows and columns switched allowing you to redim the rows.
Another potential solution is to dimension your output array without the
preserve option, and just use the maximum number of rows (probably t
Whatever would make you think of it that being the case?
So if redim preserve will only let you extend the column value which
I do not get as that is almost never what anyone would want, can you
transpose the array, do the redim, then transpose back?
On Wed, Apr 11, 2012 at 6:10 PM, Asa Rossoff
rounding a range results in an array. Evaluate evaluates array formulas and
returns array results. So, the fact that it didn't with the simple ROUND
formula is strange. transpose switches rows for columns, and a second
transpose switches them back. So you get the original array. No idea why
th
Ok that does appear to work, but I don't get it. How does nested
transposes function
to make this work correctly? It would appear to the novice that all
you did was transpose
it then transpose it back then whatever...
Is transposing an array the answer to my other new post about redim preserve?
To retrieve a multi-column array I resorted to
"transpose(transpose(round(a1:b10,2)))" form.
From: Asa Rossoff [mailto:a...@lovetour.info]
Sent: Wednesday, April 11, 2012 5:18 PM
To: 'excel-macros@googlegroups.com'
Subject: RE: $$Excel-Macros$$ Argh. Previously posted evaluate function
replaci
tmprange.Value2 = .Evaluate("transpose(transpose(round(" & tmprange.Address
& ",2)))")
should work for a multi-column range.
The performance of Value2 depends on whether you're retrieving or assigning
values to/from the worksheet, and what type of data and formats are applied
on the worksheet. I
Running your version rounded the first column correctly, but then
copied that column
into the other 4 columns. So it would seem unless there is a better
way to use
index I would have to set a separate range for each column and then evaluate
each range.
On Wed, Apr 11, 2012 at 5:38 PM, Domain Ad
I thought is might be that ROUND could only work on one value.
Blaming yourself when I did not notice that my entire range was being
clobbered is magnanimous of you.
One thing you said is incorrect. The range is not just one column (is
that the problem).
It is 5 columns. I read the help on index
Hi Howard,
Seems like an Excel bug.. Sorry for not testing the suggestion. Evaluate
may not be fully documented for a reason. However, it is a great useful
function, just if you aren't getting the result you expect, you may have to
look for a workaround. I just posted a query in a developers fo
I can make it work like this operating on one cell at a time (and the
documentation would seem to indicate
you can not use a multi cell range) but the idea was to get rid of
loops, and this is very very slow.
With Sheets(RawData)
Set tmprange = .Range(.Cells(2, 3), .Cells(stoprawdata,
Hi Ratedr,
Almost all automation requires a macro to implement (I say almost because
some features like conditional formatting, data validation, and comments
that appear when the mouse hovers over a cell could be looked at as types of
automation).
So this will will likely require a small macro.
I used the evaluate function below to replace the commented out loop
portion for better performance in rounding off all the cells in a range.
It was certainly fast and it performed the round function correctly but it
also replaced every single cell in the range with the value of the first
cell (
Actually it does not work. I was not paying attention. It rounded
everything off, but only because it rounded
off the first cell in the range and then somehow set every other cell
in the range to that same value. So my
entire range has one identical value.
On Wed, Apr 11, 2012 at 12:45 AM, Raja
The reason you can't call a method (sub or function member of a class)
directly is because the object defined by the class doesn't exist until you
create it with the NEW keyword (or CreateObject - but usually you should use
NEW).
It doesn't exist for a reason - each time you use the NEW keyword
I want to use an array to store values as more efficient that doing it cell
by cell,
then copy the entire array into the sheet range when done. But the size of
the
array not know initially. I do know that number of columns, but not the
number
of rows. So I will have to do some redim preserv
See the attached file
Try this:
Sub ExtractData()
Dim rngRange As Range
Dim rngCell As Range
Dim rngUnionAs Range
Dim dtStart As Date
Dim dtEnd As Date
Dim rngResult As Range
Set rngUnion = Range("A1:B1")
Set rngRange = Range("A1").Cur
Hi,
Please find the attached sheet.
Jaysheel.
On Wed, Apr 11, 2012 at 11:48 PM, Rajan_Verma wrote:
> Please attached file
>
> ** **
>
> Rajan.
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *karunanithi ramaswamy
> *Sent:* Apr
Please attached file
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of karunanithi ramaswamy
Sent: Apr/Wed/2012 11:17
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ $$excel-macro$$HOW TO EXTRACT DATA BETWEEN TWO
DATES
sir,
I h
Hi Lalit,
Welcome to the group, hope you will help to the peoples through your
expertise and get help from more expert here.
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of excelGeek
Sent: Apr/Wed/2012 11:07
To: excel-macros@googlegroups.com
S
sir,
I have list of staff with dates of retirement. I want to extract the staff
who retire between 5/1/2012 and 5/31/2012.
I want the vba code to do this.
thank u in advance.
Karunanithi R
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor
--
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, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thre
Please attached sample file
Rajan.
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Ratedr
Sent: Apr/Wed/2012 08:37
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ excel to open up another excel file?
Is there a fomula or a macro or som
Is there a fomula or a macro or something, within excel, that will allow it
to open up and print another excel file?
What Im saying is, for example:
I am making sprockets. I print out an order sheet for 100 sprockets. I
have a spreadsheet open stating 100 sprockets. Each time 1 sprocket is no
Yes, I moved it to another module and I believe I had an older copy of that
code in a different module as well and that was a problem too.
I've been trying to understand classes (I know they create objects) and
their use, thats why I originally used the "class module" though.
On Tuesday, April
Hi,
You can check out the below links for examples
http://office.microsoft.com/en-gb/excel-help/create-your-own-formula-in-a-pivottable-or-pivotchart-report-HP005199482.aspx
http://www.contextures.com/excel-pivot-table-calculated-field.html
On Wed, Apr 11, 2012 at 3:32 PM, Sanjib Chatterjee <
cha
Dear Shaik Waheed,
I am unable to put any formula in the pivot table.
Please help.
Thanking you in advance
Regards
Sanjib
- Show quoted text -
On Wed, Apr 11, 2012 at 3:13 PM, Shaik Waheed wrote:
> One more thing, i have observed that the amount total is not matching for
> this u need to e
Dear Shaik Waheed,
I am unable to put any formula in the pivot table.
Please help.
Thanking you in advance
Regards
Sanjib
On Wed, Apr 11, 2012 at 3:13 PM, Shaik Waheed wrote:
> One more thing, i have observed that the amount total is not matching for
> this u need to enter the "S" amount
One more thing, i have observed that the amount total is not matching for
this u need to enter the "S" amount in positive only...the formula then
calcuates.
"
On Wed, Apr 11, 2012 at 3:10 PM, Shaik Waheed wrote:
> Sanjib,
>
> Click on the pivot, on the toolbar, click on options --- >Formulas ---
Sanjib,
Click on the pivot, on the toolbar, click on options --- >Formulas --->
list of formulas ---> there u can see i have added a formual "Net Quantity"
= B- S.
To add a formula u need to click on the items in the pivot
-->Toolbar(above) --> formulas--> Calculate Item---> u can enter the
requi
yes, This is is excetly what I am looking for.
would you explain please.
regards,
sanjib
On Wed, Apr 11, 2012 at 12:45 PM, Shaik Waheed wrote:
> Hi Sanjib,
>
> PFA check sheet 2. Hope this helps
>
> On Wed, Apr 11, 2012 at 10:54 AM, Sanjib Chatterjee <
> chatterjee.kolk...@gmail.com> wrote:
>
Yes you were right, But now you also believe that it is most efficient way
to complete task and avoid loop. :)
Thanks
Rajan.
-Original Message-
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Domain Admin
Sent: Apr/Wed/2012 02:36
To: excel-macros@go
42 matches
Mail list logo
