How do I return which row the active cell is in?

Before I start, thanks to all the people who've already helped me on
this board. Solely because of you, I've managed to complete 10% of a
simple but large project (2 applications so far). I've hunted the
boards now for a couple of hours and haven't found the answer to a very
simple issue. What I want to do is this:

I have a worksheet with data already on it. I want to cut and paste
data from another worksheet. I need to identify the first empty row at
the end of the data and return its row number. I tried the following.

Sub test()
    Dim lRowNum As Long

    Range("A65536").End(xlUp).Offset(1, 0).Activate
    lRowNum = ActiveCell.Row
    MsgBox "lRowNum"
End Sub

MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
I get my row number?

Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
smell. Why didn't something crash burn and die at lRowNum =
ActiveCell.Row?

Thanks now for any replies.

Yours, again, in frustrated confusion,

Terry R.

0
9/21/2005 7:27:09 AM
excel 39879 articles. 2 followers. Follow

20 Replies
672 Views

Similar Articles

[PageSpeed] 28

"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message 
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>    Dim lRowNum As Long
>
>    Range("A65536").End(xlUp).Offset(1, 0).Activate
>    lRowNum = ActiveCell.Row
>    MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.

Try MsgBox ActiveCell.Address

Bruno


0
9/21/2005 9:29:15 AM
"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message 
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>    Dim lRowNum As Long
>
>    Range("A65536").End(xlUp).Offset(1, 0).Activate
>    lRowNum = ActiveCell.Row
>    MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.

Forget my previous message, I misunderstood your question.
You write MsgBox "lRowNumber"; why those ""?
MsgBox lRowNum should work, or simply MsgBox ActiveCell.Row.

Bruno



0
9/21/2005 9:34:58 AM
You are displaying a text string, not the variable lRowNum. It displays that
text for me, not OK.

You don't need to activate the last row, just use

    Dim lRowNum As Long

    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    MsgBox lRowNum


-- 
 HTH

Bob Phillips

"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>     Dim lRowNum As Long
>
>     Range("A65536").End(xlUp).Offset(1, 0).Activate
>     lRowNum = ActiveCell.Row
>     MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.
>


0
bob.phillips1 (6510)
9/21/2005 9:52:28 AM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:OERj1IpvFHA.1256@TK2MSFTNGP09.phx.gbl...

> You are displaying a text string, not the variable lRowNum. It displays 
> that
> text for me, not OK.
>
> You don't need to activate the last row, just use
>
>    Dim lRowNum As Long
>
>    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
>    MsgBox lRowNum

You don't even need three lines of code:

MsgBox Range("A" & Rows.Count).End(xlUp) + 1

Bruno 


0
9/21/2005 12:21:29 PM
"Cloudfall" <SydneyCloudfall@hotmail.com> wrote in message
news:1127287629.177074.28010@g14g2000cwa.googlegroups.com...
> Before I start, thanks to all the people who've already helped me on
> this board. Solely because of you, I've managed to complete 10% of a
> simple but large project (2 applications so far). I've hunted the
> boards now for a couple of hours and haven't found the answer to a very
> simple issue. What I want to do is this:
>
> I have a worksheet with data already on it. I want to cut and paste
> data from another worksheet. I need to identify the first empty row at
> the end of the data and return its row number. I tried the following.
>
> Sub test()
>     Dim lRowNum As Long
>
>     Range("A65536").End(xlUp).Offset(1, 0).Activate
>     lRowNum = ActiveCell.Row
>     MsgBox "lRowNum"
> End Sub
>
> MsgBox unhelpfully returned "OK" (??!!). What does this mean and how do
> I get my row number?
>
> Since when is "OK" a Long? "OK" has more of a Boolean "True" taste and
> smell. Why didn't something crash burn and die at lRowNum =
> ActiveCell.Row?
>
> Thanks now for any replies.
>
> Yours, again, in frustrated confusion,
>
> Terry R.
>

your close, but change to:  IRowNum = Range("A65536").End(xlUp).Offset(1,
0).Row




0
Jefgorbach (42)
9/21/2005 12:23:49 PM
Maybe...

MsgBox Range("A" & Rows.Count).End(xlUp).row + 1



Bruno Campanini wrote:
> 
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OERj1IpvFHA.1256@TK2MSFTNGP09.phx.gbl...
> 
> > You are displaying a text string, not the variable lRowNum. It displays
> > that
> > text for me, not OK.
> >
> > You don't need to activate the last row, just use
> >
> >    Dim lRowNum As Long
> >
> >    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >    MsgBox lRowNum
> 
> You don't even need three lines of code:
> 
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
> 
> Bruno

-- 

Dave Peterson
0
petersod (12005)
9/21/2005 12:25:40 PM
"Jef Gorbach" <Jefgorbach@aol.com> wrote in message 
news:u3zildqvFHA.2008@TK2MSFTNGP10.phx.gbl...

> your close, but change to:  IRowNum = Range("A65536").End(xlUp).Offset(1,
> 0).Row

You'r right, the shorter:
MsgBox [A65536].End(xlUp) + 1

Bruno 


0
9/21/2005 12:32:08 PM
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:43315144.ABFC777@verizonXSPAM.net...
> Maybe...
>
> MsgBox Range("A" & Rows.Count).End(xlUp).row + 1

If you prefer, but it also works without .Row:
MsgBox Range("A" & Rows.Count).End(xlUp) + 1

Bruno 


0
9/21/2005 12:35:23 PM
It depends on what's in that cell.

I'm betting you just put 1, 2, 3, .... in your test data.

Bruno Campanini wrote:
> 
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43315144.ABFC777@verizonXSPAM.net...
> > Maybe...
> >
> > MsgBox Range("A" & Rows.Count).End(xlUp).row + 1
> 
> If you prefer, but it also works without .Row:
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
> 
> Bruno

-- 

Dave Peterson
0
petersod (12005)
9/21/2005 12:51:45 PM
Bruno,

Your way is wrong.  Dave's way is correct, and not just "a preference". The default property of a 
range object is .Value, so your code will return the value of the last cell + 1.  If that value is a 
number, you will get that number +1, not the row number +1.  If the value is a string, you will get 
an error.

Since you want the row number, use .Row

HTH,
Bernie
MS Excel MVP


"Bruno Campanini" <bruno.campanini@tin.it> wrote in message 
news:uA93xjqvFHA.2292@TK2MSFTNGP12.phx.gbl...
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
> news:43315144.ABFC777@verizonXSPAM.net...
>> Maybe...
>>
>> MsgBox Range("A" & Rows.Count).End(xlUp).row + 1
>
> If you prefer, but it also works without .Row:
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno
> 


0
Bernie
9/21/2005 12:52:03 PM
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message 
news:uOFUEtqvFHA.3500@TK2MSFTNGP09.phx.gbl...
> Bruno,
>
> Your way is wrong.  Dave's way is correct, and not just "a preference". 
> The default property of a range object is .Value, so your code will return 
> the value of the last cell + 1.  If that value is a number, you will get 
> that number +1, not the row number +1.  If the value is a string, you will 
> get an error.
>
> Since you want the row number, use .Row

You are perfectly right!
I tested in a column terminating with the corresponding row number...

Arghhhh

Bruno 


0
9/21/2005 2:05:33 PM
You should certainly not omit the

    Dim lRowNum As Long

very poor coding practice

-- 
 HTH

Bob Phillips

"Bruno Campanini" <bruno.campanini@tin.it> wrote in message
news:OP$FCcqvFHA.2960@tk2msftngp13.phx.gbl...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OERj1IpvFHA.1256@TK2MSFTNGP09.phx.gbl...
>
> > You are displaying a text string, not the variable lRowNum. It displays
> > that
> > text for me, not OK.
> >
> > You don't need to activate the last row, just use
> >
> >    Dim lRowNum As Long
> >
> >    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
> >    MsgBox lRowNum
>
> You don't even need three lines of code:
>
> MsgBox Range("A" & Rows.Count).End(xlUp) + 1
>
> Bruno
>
>


0
bob.phillips1 (6510)
9/21/2005 3:21:46 PM
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:%23pLI3AsvFHA.3864@TK2MSFTNGP12.phx.gbl...

> You should certainly not omit the
>
>    Dim lRowNum As Long
>
> very poor coding practice

I've NEVER used lRowNum on my examples,
only MsgBox Range...
Or, if I did, it was only with reference to Terry's code.

Is this a good code:
----------------------------
Dim lRowNum As Long

    lRowNum = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
    MsgBox lRowNum
------------------------------

compared with this one?
-----------------------------
MsgBox Range("A" & Rows.Count).End(xlUp).Row + 1
-----------------------------

Your code reveals a complete omologation to the
mass of fantasyless programmers:
Blank lines everywhere.
Indentention when there is no need for indent.
Code redundancy (Offset can be replaced by +1 at
the end of line)

And - I suppose -
Dim ... as Variant
Option Base 0
Range("A1").Value
Application.WorksheetFunction
etc.

Art of communication has much more to deal with
quality than with quantity.
Software vendors don't.

Well Bob, since now on I'd like to be very sympathetic with you.
Hopefully, would you be the same?

Bruno 


0
9/21/2005 4:21:31 PM
Bruno,

Thank you. Both worked. Have a good one.

Terry R.

0
9/21/2005 11:58:37 PM
Bob,

Thank you for your response. I really like the way that you can return
the number of the row without actually having to go there. So I started
to experiment with your code and got a weird anomaly. I cut and pasted
your exact code into a small test subroutine.

I put the following data into a new worksheet:
The number 1 into cell A1, the number 2 into cell B2, and the number
three into cell C3.

When I ran the code "lRowNum = Range("A" &
Rows.Count).End(xlUp).Offset(1, 0).Row" for column A, I got the answer
"2", as you would expect. Similarly, for "lRowNum = Range("B" &
Rows.Count).End(xlUp).Offset(1, 0).Row I got 3, and for "lRowNum =
Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row" I got 4.

But when I ran "lRowNum = Range("D" & Rows.Count).End(xlUp).Offset(1,
0).Row", with nothing in column D, I got back 2. The same was true for
every other column with nothing in it.

Do you think this is because of the "Offset(1, 0)" factor? So, to use
this reliably, even for columns which have nothing in them, would you
require further code?

Still slightly confused, yours sincerely

Terry R.

0
9/22/2005 12:25:24 AM
Bob's code is like selecting the last cell in column D.  Then hitting the End
key and up arrow (to find that last used cell.  If column D is empty, end
followed by uparrow gets you to D1.  .offset(1,0) will drop you down 1.

You could use a minor variation.

Dim NextCell as Range
dim NextRow as long
with worksheets("sheet1")
  set Nextcell = .cells(.rows.count,"D").end(xlup)
  if isempty(Nextcell) then
      'do nothing, don't change anything
  else
     set nextcell = nextcell.offset(1,0)
  end if
  nextrow = nextcell.row  'if you need it
end with

=========
But now you may have another problem--what happens if there's data in
D65536????)

<vbg>
  

Cloudfall wrote:
> 
> Bob,
> 
> Thank you for your response. I really like the way that you can return
> the number of the row without actually having to go there. So I started
> to experiment with your code and got a weird anomaly. I cut and pasted
> your exact code into a small test subroutine.
> 
> I put the following data into a new worksheet:
> The number 1 into cell A1, the number 2 into cell B2, and the number
> three into cell C3.
> 
> When I ran the code "lRowNum = Range("A" &
> Rows.Count).End(xlUp).Offset(1, 0).Row" for column A, I got the answer
> "2", as you would expect. Similarly, for "lRowNum = Range("B" &
> Rows.Count).End(xlUp).Offset(1, 0).Row I got 3, and for "lRowNum =
> Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row" I got 4.
> 
> But when I ran "lRowNum = Range("D" & Rows.Count).End(xlUp).Offset(1,
> 0).Row", with nothing in column D, I got back 2. The same was true for
> every other column with nothing in it.
> 
> Do you think this is because of the "Offset(1, 0)" factor? So, to use
> this reliably, even for columns which have nothing in them, would you
> require further code?
> 
> Still slightly confused, yours sincerely
> 
> Terry R.

-- 

Dave Peterson
0
petersod (12005)
9/22/2005 12:38:07 AM
Dave,

It worked. Thank you for the warning re data in D65536. You once gave
me a warning in another post:
"If you have formulas in the range, this will keep them as
formulas--your code:
selection.value = selection.value
would cause damage if you're not careful."
Sure enough, I wound up doing that on one occasion. As I was about to
post another question, your warning came back to me and I came up with
a workaround.

Thanks for your help,

Terry R.

0
9/22/2005 1:44:37 AM
Jef,

Thanks, this works great.

Terry R.

0
9/22/2005 1:51:41 AM
Bruno,

This takes the value in the last cell and adds "1" to it.

Thank you for your response,

Terry R.

0
9/22/2005 1:52:08 AM
Glad you got things working.

Cloudfall wrote:
> 
> Dave,
> 
> It worked. Thank you for the warning re data in D65536. You once gave
> me a warning in another post:
> "If you have formulas in the range, this will keep them as
> formulas--your code:
> selection.value = selection.value
> would cause damage if you're not careful."
> Sure enough, I wound up doing that on one occasion. As I was about to
> post another question, your warning came back to me and I came up with
> a workaround.
> 
> Thanks for your help,
> 
> Terry R.

-- 

Dave Peterson
0
petersod (12005)
9/22/2005 2:06:50 AM
Reply:

Similar Artilces:

Directing the output of one cell into another cell
I have an IF statement that works exactly the way I want and results in a TRUE/FALSE. The problem is, I want that true/false result to be directed to another cell without having to make that cell have a formula in it like =A13 (if A13 is where I have the IF formula). Essentially, I want the target cell to be able to accept data from a couple of sources, all being true/false tests, and not have a formula in it. Does that make sense? Something like: IF B21=TRUE then A13=TRUE, which is easy in VBA, but seems I can't get Excel to do the same. Thanks Conf. "Confused" <no...

Creating a shortcut to activate Compact Messages
I'd like to create a shortcut on the desktop so I can run Compact Messages without having to run Outlook Express first. -- Nongam AFAIK, OE must be open to compact. If you compact as follows, there is no outside interference. Click on Outlook Express at the top of the folder tree so no folders are open. Then: File | Work Offline (or double click Working Online in the Status Bar). File | Folder | Compact all folders. Don't touch anything until the compacting is completed. Compact Your OE Folders: http://www.insideoe.com/files/maintain.htm#compact -- ...

Mouse continues to select after clicking on a cell in excel.
I have read numerous posts detailing this issue and all the replies have been unhelpful. So hopefully, I can get some actual help for this issue. This issue seems to happen randomly, and effects one out of 10 similar machines with the same setup. All the machines are running windows 2000 (fully patched to the latest service/security level), running Office XP, also fully patched to the latest service/securit level. The problem is, a cell gets selected and the mouse continues to select cells even thou the mouse button is not pressed, basically disabling excel. The only way to stop thi...

Stopping free text entry in validation cell
Hi, I have an xls that I have set up with validation lists in some cells. I need to stop users from ignoring the options in the list and just typing whatever the want in the cell. Anyone help? I am sure it is easy, but can't see how to do it. Thnx, Smf If you are using a list then that should be enough unless they copy and paste into the validation cell -- Regards, Peo Sjoblom "smf" <smf@discussions.microsoft.com> wrote in message news:448D4166-A2C9-4C25-852F-7B045DF19F98@microsoft.com... > Hi, > > I have an xls that I have set up with validation lists...

Retrieve last non zero value in a range of cells
Hi How do I extract the last non zero value in a range of 5 Cells for Example: A1=95 B1=93 C1=98 D1=0 E1=0 From the example above I would require to choose the value 98 from the Range declared. Some weeks it is possible for all the values to be >0 so therefore I would need the Value in E1 other weeks it may be B1 Would it be possible to return the value I require into Cell G1 in the example above? Thanks Peter Entered in G1 =LOOKUP(2,1/(A1:E1<>0),A1:E1) Gord Dibben MS Excel MVP On Wed, 8 Jun 2011 11:55:19 -0700 (PDT), Pete <fell-walker@hotmail.co.uk> wrote: >...

Row headings
I have about six groups of statistics that in each one I would like to reset the row heading back to 1 eg.. 1-32 for each group. Thank you, jack ...

Insert row(s) with vba
I'd appreciate some help to determine if I'm attempting the impossible... We have a workbook to generate proposals. Each proposal contains numerous sections. Each section has many line items. The first item in section 1 is numbered 01-001, the next 01-002 etc. The 01 is the section number, the 001 is the item number. Iuse the formula:- =CONCATENATE((TEXT($A$2,"00")),"-",(TEXT((RIGHT(OFFSET(A5,-1,0),3)+1),"000"))) to automatically generate the item number row by row. The value in $A$2 is the section number. This way I can use vba to insert a ro...

patch for promoting emails to CRM activities
Hi all, On this newsgroup I've read that there is supposted to be a patch that fixes the problem that often occurs when trying to promote an email to an CRM activity (in SFO). The bug is that the used emailadresses although previously entered in MS CRM are not recognized inmediately. Does anyone know how we can obtain this patch? Or if the patch is just a rumor, what is the alternative solution for this bug? Thank you for replying, Basman looks like KB888006 & KB840058 are relivant if you are talking about v1.2 "Basman" wrote: > Hi all, > > On this newsgrou...

Hiding rows doubles workbook size on disc
Hi all, I have a macro workbook that only uses about 200 rows, but has a lot of formulae... On disc it is 1045 KB. When I hide all the rows between row 200 and the last row the size on disc goes up to 2045KB Is there any way to get rid of those rows (so the user just sees a grey area, non-clickable) without doubling the size of the workjbook on disc...? It's Excel 2000, on Windows 2000 Pro (Windows 2000 Server network...) thanks Philip Philip I have Excel 2002 and I don't get the size increase that you do. I think you are doing something to make Excel think the file ...

Excel 2003 - VBA
Hi; I would like to do a lookup with the intent of determining the row of the match. I have several years of data in a flat file and am interested in doing a vertical lookup confined to a months worth of data. To do this I need to know what row the month starts and what row it ends. Thanks, Craig Please don't do that. Use Pivot tables. Pivot tables can automatically group data in months. Pivot tables can read flat files. Pivot tables are a teeny weeny bit difficult to master. Once you have cracked the idea, you will save yourself years of writing (silly) lookups. Zumble. "...

what is the format for an input cell?
I have Office - Student and Teacher Edition 2003 I am having trouble making a table ... I try to make a Data Table and try to define $E$3:$G$16 as the range in the row input cell area so I can make this a one-input data table and then I enter C$9$ for the column cell input area a pop up pops up saying that the input (row) cell reference is invalid I hope this not a bug that can only be fixed if you have the full version of the software any clues? it should be $C$9 Pumaman <Pumaman@discussions.microsoft.com> wrote in message news:D68327C9-5237-4353-8474-890677696F9...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...

must press enter 2x to move cell focus
I now have to hit enter or tab twice after editing cell data to get cursor to move to next cell, but only on existing workbooks. New workbooks stil moves after first enter. Move after entry box is checked, tried unchecking, reopen excel, recheck, reopen excel, not fixing it. Excel 2003 on Win XP Pro, AV files current and running. Don't think I changed anything lately. Have compared settings, can't find any differences. Searched KB and newgroup posts but if answer is there I'm not searching on correct words... ...

How copy a cell with mixed text (regular, bold, italic) with a fo.
If I copy a cell with mixed text (regular, bold, italic) into a different cell it shows exactly same mixed text. However, if I copy it with a formula "=A1" then it shows regular text only. How do I change that? You can't do it with a formula - you would need a macro. Pete On Nov 25, 10:40=A0am, Burkhard <Burkh...@discussions.microsoft.com> wrote: > If I copy a cell with mixed text (regular, bold, italic) into a different > cell it shows exactly same mixed text. > However, if I copy it with a formula "=3DA1" then it shows regular text o= ...

Tag some cells
Hello, I have no idea if this is possible. What I want to do is to be able to tag some cells in a column and do a SUM on those tagged cells only. How can I do this? For tagging I was thinking of changing the background color of the cells to yellow for example. That's what I prefer. Then I thought of using SUMIF but how can I check the background color of a cell in the criteria? Thanks There is an example of using SUMPRODUCT and UDFs to count coloured cells at http://www.xldynamic.com/source/xld.ColourCounter.html The biggest problem is that changing a colour does not generate a w...

How to find a result in an array in the same row as a minimum resu
I have temperature data for many years and am trying to find the minimum temperature for a given day. I can find the min temperature on a given day with this: {=MIN(IF(TEXT($A$7:$A$2001,"mmm,d")=X127,$D$7:$D$2001))} where X127 has the day of the month I'm searching for and the low temperatures are in column "D". But now I want the day it occurred which is in column "A" of the same row as the min temp. How do I get that? I thought this would work; {=ADDRESS(ROWS(D7:D2001=Z127),1)} but that gives me an erroneous address. Another question: If ...

Vlookup but two rows below
Hi, I have a requirement to extract some data which under normal circumstances would be ease as the formula =VLOOKUP(C2,$R$12:$T$1010,3,0) would return the correct result if R12 contained 301 and T12 contained XYZ i.e. XYZ would be returned. However, I want to return the contents of cell T14 which is always 2 rows below the match in column R. Appreciate any help. Ta, Rob ...

Row height won't adjust to fit all the text
When I add long text to a cell the row doesn't grow and I have to manually adjust the size of the row to see all the text. Is there a way for the row to just keep enlarging so I can see and print everything? Hi if you exceed 1024 characters you have to enter manual linebreaks with ALT+ENTER -- Regards Frank Kabel Frankfurt, Germany "CMJ" <CMJ@discussions.microsoft.com> schrieb im Newsbeitrag news:E6F22C23-F92F-47EC-B517-AE76723EB851@microsoft.com... > When I add long text to a cell the row doesn't grow and I have to manually > adjust the size of the row to s...

How can a single cell be incremented? i.e. N=N+1
I am able to move a value of 1 to a cell with in a macro. I want to be able to add a value of 1 to a cell with in a macro. Each time the cell is referenced, I wish to increment the cell by 1. I have not been able to increment the cell without getting a circular reference in my attempts to incremetn the cell. The process I use is sorting a table and wanting to increment the top cell so that the least used entries will be sorted to the top each time the macro is called. what does your macro look like? "Remel" wrote: > I am able to move a value of 1 to a cell with in a m...

Excel 2002: Can I not overwriting non blank destination cells ?
Hi, I understand that Copy > Paste Special > Skip Blanks allows copied blanks cells not to overwrite non blank cells in the destination column. How about the other way round ? i.e. to paste only the copied cells (blank or non blank cel)l if the destination cells is blank only i.e not to overwrites any destination cels if it is not a balnk cells. Thanks Low ...

Internet Explorer 8 slow with Active Directory Federation Services
I have the ADFS scenarios depicted on http://technet.microsoft.com/en-us/library/cc779508(WS.10).aspx in the two sections both titled "Authenticating the user". When it comes to the steps 3 and 6 in the picture of the first "Authenticating the user" section and steps 5 and 7 in the picture of the second "Authenticating the user" section Internet explorer 8 causes 100% CPU load on one thread. The time it takes is proportional to the single thread processing power of the client CPU. On "old" clients it lasts so long that the requests fail...

Copying formatting from a cell whose location I have calculated.
Excel 2003 in XP: I want to copy the formatting of one of 12 cells whose location I have calculated. I could use the usual Conditional Formatting, but that only gives 3 options. I need 12. to wit: I want to copy the formatting of a cell in a row above the cell to be formatted. In a cell to the left I have calculated how many columns of offset to the desired format to be copied. The cell format I want to use is in a row above my cell, and offset by 0 to 11 cells. Any ideas? - Thanks! -- sdm From your description it appears you are only copying the format of a few cells, have you ...

Combine 2 cells into 1
I have 2 sheets in Excel 2000. One sheet has a column for first name and a column for last name. The other sheet has a column in the format "last name, first name". I need to move all of the names from the sheet that has them in separate columns to the sheet with the "last name, first name" format. What is the easiest way to do this? There are hundreds of names so I'd rather not have to manually put them in. I figure Excel has to have some sort of function for this. Thanks, Brian Mosher ...

How many words or characters maximum in a cell?
Hi I am having trouble with entering all of my text into a cell. It does not show everything. I am using Excel to write my daily activities at work and need a lot of space! I'm using Excel 2000 on Windows 2000. Any advice would be appreciated. Thanks! Take a look at "Specifications" in XL Help. You'll find that XL cells can contain 32767 characters, of which the first 1024 will be displayed in the cell or printed. You can work around this limit if you manually enter linefeeds (Alt-Enter) at least every 1024 characters. In article <C9144458-3061-45FE-ABFF-50E64C...

Determine if 2 cells share a common word
I have a list with two text columns. I want to identify the rows where the cells share a common word. For example, Fujitsu Consulting in col A and Software Engineer in col B would not share a common word but Starbucks in col A and Starbucks Barista in col B would share a common word. Sub Highlight_Word() Dim rng As Range Dim Cell As Range Dim start_str As Integer myword = InputBox("Enter the search string ") Mylen = Len(myword) Set rng = Selection For Each Cell In rng ' start_str = InStr(cell.Value, myword) '(case sensitiv...