Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Onkey Or Keydown Withing Worksheet?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hi,

I have this code which puts a top border around specific cells.

Code:

 
Worksheets("Sheet1").Activate
  'Can't select unless the sheet is active

If ActiveCell.Value <> Selection.Offset(1, 0) Then

ActiveCell.Borders.Item(xlEdgeBottom).Weight = xlMedium

Selection.Offset(1, 0).Select

Else: Selection.Offset(1, 0).Select
End If



It works fine when attached to a button but I want it to be assigned to either 'OnEnter' or 'Keydown' within the worksheet

Any ideas?

Thanks

Alex

View Answers     

Similar Excel Tutorials

Import a Worksheet from One Workbook to Another in Excel
In Excel, you can quickly copy an entire worksheet from one workbook to another workbook.  This allows you to impor ...
Activate or Navigate to a Worksheet using Macros VBA in Excel
Make a particular worksheet visible using a macro in Excel. This is called activating a worksheet and is rather eas ...
Remove Gridlines from Only Part of a Worksheet in Excel
How to remove gridlines from a part of a worksheet in Excel instead of the entire worksheet or workbook. This is i ...
Hide or Unhide a Worksheet by Hand in Excel
I'll show you how to hide worksheets in Excel so that a user cannot see them but you can still access data on them ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Automatically Highlight the Active or Selected Cell
- This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that a
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics







Code:

Sub Package()
 
Dim CalculatedValue
Range("L2").Select
Do
    If ActiveCell.Value = "" Then
        CalculatedValue = Application.WorksheetFunction.RoundUp(ActiveCell.Offset(0, -1).Value / 1.5, 0) * 1.5
        ActiveCell.Value = IIf(CalculatedValue = 61.5 Or CalculatedValue = 63, 64.5, CalculatedValue)
    End If
    ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End sub



dear friends.above macro work fine for me.i need to add below code with this macro.pls help me to do that....

Code:

        ActiveCell.Value = IIf(CalculatedValue = 31.5 Or CalculatedValue = 33, 34.5, CalculatedValue)





I have the following formula:

Code:

="A"&($C$20)+1


I'd like to copy this formula down to several hundred rows, with the +1 being incremented by 1 on each row. For example...

Code:

="A"&($C$20)+2


Code:

="A"&($C$20)+3


Code:

="A"&($C$20)+4


Code:

="A"&($C$20)+5


Code:

="A"&($C$20)+6


Code:

="A"&($C$20)+7


and so on....

I've been unable to get the last digit to increment. Any help is greatly appreciated.


I have the following 2 formulas that I'm trying and all I get is #####. I only want to count cells that have an X in them.

Code:

=IF(activecell.address="x",COUNTA(V12:V5093),"")



and

Code:

=IF(activecell.address = "", "",COUNTA(U12:U5093))



When I use the following code, it works correctly, but it also counts all values and I only want to count X's.

Code:

=COUNTA(N12:N5093)



Thanks in advance for your help.


Hello all, Please Help!

I am trying to figure out how to use the code below to only show certain worksheets after the I Accept button is clicked instead of all worksheets in the workbook.

disclaimer Worksheet named Disclaimer:

Code:

Private Sub Acceptbtn_Click()
HideSheets xlSheetVisible
If Not objCurrent Is Nothing Then
objCurrent.Activate
objSelection.Select
Else
Sheet1.Activate
End If
End Sub


Module1:

Code:

Public objCurrent As Object
Public objSelection As Object

Sub HideSheets(varVisible As Variant)
Dim Sht As Worksheet
If varVisible = xlSheetVeryHidden Then
With Disclaimer
.Visible = xlSheetVisible
.Activate
End With
End If
For Each Sht In ThisWorkbook.Worksheets
If Sht.CodeName <> "Disclaimer" Then
Sht.Visible = varVisible
End If
Next Sht
End Sub


ThisWorkbook:

Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set objCurrent = ActiveSheet
Set objSelection = Selection
HideSheets xlSheetVeryHidden
End Sub





I use the following vlookup to show whether a team name is within a set range:


Code:

Command	CST1		CST2		CST3	
Team	EU1	√	EU7	√	FR6	√
	EU3	√	EU9	√	FR7	√
	EU4	√	EU10	X	FR8	√
	RA1	√	EU11	√	FR9	√
	RA2	√	EU12	X	FR10	√
	RA3	√	EU13	√	FR11	√
	DN1	√	EU14	X


The code to produce the above (where f8 = cell value to lookup in my sheet f8 = EU1 )
Code:

=IF(ISNA(VLOOKUP(F8,sectionnames,1,FALSE)),"X","√")


The range the vlookup looks within
Code:

sectionnames range:
RA3

RA2

RA1

FR9

FR8

FR7

FR6

etc..


This works fine unless someone adds a team name uncapitalised as FR9 below:

Code:

RA3

RA2

RA1

fr9

FR8

FR7

FR6


How can i get around this problem? Can i make the vlookup look for uncapitalised versions of the team name? or create a macro that capitalises all values within a column?

Thanks for any help


Dear all,


i have big macro with if statement and a variable that the position and selection of the active cell depends on. Everything is working perfectly and i want to add some extra conditions in order to select the position of the active cell because i have some rows which include 0 values.I should mention once again that the active cell's address depends on the result of the previous if statements and a variable so the cell that i need to check if its value = 0 has to be expressed with the active cell's position.
I also don't want to move active cell before i know the result. After i know if the result of the cell under the active

So what i want to say is:

Code:

If Cell(ActiveCell.Row +1, Active.Cell.Column) = 0 Then
   ActiveCell.Offset(1, 0).Range("A1").Select
ElseIf Cell(ActiveCell.Row +1, Active.Cell.Column+1) = 0 Then
   ActiveCell.Offset(1, 1).Range("A1").Select
ElseIf Cell(ActiveCell.Row +1, Active.Cell.Column-1) = 0 Then
  ActiveCell.Offset(1, -1).Range("A1").Select


So how can i write this Cell(ActiveCell.Row +1, Active.Cell.Column)?

Do you think you could help me??



Thank you in advance!!!


Guys and Gals,

Can I use the following function result and change it to text? Code:

=ADDRESS($C$23,2,,,"mysheet")


the result of that function, for me, is this: Code:

mysheet!$B$10


what I want to do with that literal outcome is plug into the next function, which is this: Code:

=MATCH($E$8,aboveResult:aboveResult)


So in the cell it looks like: Code:

=MATCH($E$8,$F$23:$F$24)


but I just get this as a result: Code:

#N/A


Is there a way to fix that problem? A text conversion function? I've looked through the help files but can't find what I need. thanks!


Hi All,

Hopefully this is a simple question. I have a cell - C80 - in which I have the formula:

Code:

=B72


I went to copy this down to C81 and it changes to:

Code:

=B73


I then added a $ sign by the row number so it now changes to:

Code:

=B$72


i.e. no change.

I want it to change to:

Code:

=C$72


Code:

=D$72


and so on.

Can anyone help?

Thanks
dvent


Code:

A1 "5"
B1 ">" or "<"
C1 "2"
D1 "=IF(A1&B1&C1,"True","False")"


...is not working, returning #VALUE! in D1. I notice that the evaluation appears to be (correctly) converting it to a string:

Code:

IF("5>2","True","False")


...is there any way I can have it evaluate properly? I am aware that I can do something like that shown below, but for obvious reasons am reluctant to do so!

Code:

=IF(B1=">",IF(A1>C1,"True","False"),IF(B1="<",IF(A1<C1,"True","False")))





Hi - I am trying to import data from an external XLS spreadsheet. The external sheet has four columns, an alphanumeric code, a description, and two prices (decimals).

When I configure and run the import, everything is imported except any codes which are not digits only.

I.e. the row;

Code:

1000012   Test record   0.12  0.15


is imported just fine, but;

Code:

10001AB   Second record   0.23  0.35


will only be imported as;

Code:

          Second record   0.23  0.35


I have tried all sorts of things to figure this out but am completely stuck.

Any ideas or suggestions?

Thanks in advance!


Hi all,

I have encountered a Run-Time '1004' error which says my selection is not valid on the Insert line(bolded) when my macro tries to insert a cut row into row 2.

Please help!!!

Code:

WS2.Range("H" & d).Rows.Offset(1, 0).Select
WS2.Range("H" & c).EntireRow.Select
Selection.Cut                       
WS2.Rows(d).Select                
Selection.Insert Shift:=xlDown





I usually create a macro to copy the data and send it to a holding area, and the pivot would pick up the data from the dynamic range.
I tried using this method from this link:
HTML Code:

http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/fa9c4f90d179801e/51180a075dc4aab5?lnk=gst&q=one+pivot+many+ranges#51180a075dc4aab5


I tried using this method to switch the data to a pivot table:
Step 1: Create a dynamic ranges named Pivot1 and Pivot2 for Sheet2
Code:

=OFFSET(Sheet2!$A1,0,0,COUNTA(Sheet2$A:$A),2)


and
Code:

=OFFSET(Sheet3!$A1,0,0,COUNTA(Sheet3$A:$A),2)


Step 2: Created a dynamic name called Data:
Code:

=INDIRECT(Sheet1!A1)


Then tried to create a pivot table using the named range Data, but it failed; I got a message saying the reference is not valid.
Can someone tell me what am I doing wrong?

Xrull


Hi,

I'm trying to SUBSTITUTE one of two text values depending on what is in the cell to the left.

Formula I've tried is:

Code:

=IF(SEARCH("XXXX",B1),SUBSTITUTE(B1,$G$1,$H$1),(SEARCH("YYYY",B1),SUBSTITUTE(B1,$G$2,$H$2)))


Each half works separately
Code:

=IF(SEARCH("XXXX",B1),SUBSTITUTE(B1,$G$1,$H$1)


or
Code:

=IF(SEARCH("YYYY",B1),SUBSTITUTE(B1,$G$2,$H$2)


but when I try to combine them I cannot get it to work.

Any ideas?

Thanks


At the moment I have
Code:

XVAL refers to =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
YVal refers to =OFFSET(XVal,0,1,,)


and these are used in a graph (Chart) Code:

=SERIES(,Book1.xls!XVal,Book1.xls!YVal,1)


Now ...

Let's assume the data points (XVal) are in A3,A6,A9, ... (100 points !!)

Can I build a formula to be used in the dynamic range (instead of going through CTRL+Select) and build $A$3,$A$6,$A$9,$A$12, ... ?


Hello everyone. I thank you in advance for your time.

I have a weighted average formula which works well (see below).

Code:

Packages	Zone
56	2
14	3
21	4
27	5
53	6
106	7
239	8
	
516	6.48255814


Code:

=((A2*B2)+(A3*B3)+(A4*B4)+(A5*B5)+(A6*B6)+(A7*B7)+(A8*B8))/SUM(A2:A8)


That works great. It does exactly what I want for the chart in question.

The problem I'm running into is if I put a "0" into one of the package columns (see below).

Code:

Packages	Zone
0	2
14	3
21	4
27	5
53	6
106	7
239	8
	
460	7.02826087


If I put a zero in the first cell of packages how do I tell the formula not to calcuate it. I need the formula above to not calculate for any zeros in the first column A.

If you have any questions or need more information please let me know.

Thanks

Eddie


I have an array formula that looks a list of items, matches the item that it is looking for, and then finds the latest date that something was ordered (using MAX). This is the whole formula:

Code:

=MAX(MAX(IF(LISTDESC=INDEX(PKEY,MATCH($B3,KEY!$B:$B,FALSE),3),LISTDATE)),MAX(IF(LISTDESC=INDEX(PKEY,MATCH($B3,KEY!$B:$B,FALSE),2),LISTDATE)),MAX(IF(LISTDESC=INDEX(PKEY,MATCH($B3,KEY!$B:$B,FALSE),3),LISTDATE)))



Note that this formula uses defined names and also takes into account that some items have multiple names (there are 3 different index/match bits in there). To make things a little more straightforward, lets pretend that this is my formula:

Code:

{=MAX(IF(List_Frame_1!$D$1:$D$606=INDEX(KEY!$B:$E,MATCH($B3,KEY!$B:$B,FALSE),2),List_Frame_1!$E$1:$E$606))}



This works fine for finding the latest ordered date of a specific item. On the same line of this item on the List_Frame_1 worksheet is a due date, associated with the order date. What I am wanting to return is the corresponding cell in List_Frame_1!$M$1:$M$606. This would simply be an offset of the formula above, but I cannot figure it out since it is an array formula.

Essentially I'm looking for a formula that does the following, however the following does not work:

Code:

=OFFSET(MAX(IF(List_Frame_1!$D$1:$D$606=INDEX(KEY!$B:$E,MATCH($B3,KEY!$B:$B,FALSE),2),List_Frame_1!$E$1:$E$606)),0,8)



I'm not sure if this is possible or perhaps I am looking at it completely the wrong way. Hopefully this explanation is in enough detail and makes sense. I appreciate any and all help.

Best regards,
Hoffey


I've always used OFFSET like so Code:

keycolumn = OFFSET($A$2,,,COUNTA($A$2:$A$5000))
Second column =OFFSET(keycolumn,,1)
etc.


The problem with that is blanks will screw up everything.

I've seen on here (can't find the thread but I think it was Donkeyote Code:

EndRow =MATCH(9E+307, $A:$A, 1)
Then
keycolumn = INDEX($A:$A, 2):INDEX($A:$A, EndRow)
Second Column =INDEX($B:$B, 2):INDEX($B:$B, EndRow)
etc.


Any disadvantages to using this technique?
How about combining the two and developing the key column with method 2 and then using the OFFSET for other columns.

Any other ways to set up this type of dynamic range?


Hi all

Having a problem with a vlookup involving part of the cells value..

e.g. on my Names sheet I have info like
89743 Name 1
987232 Name 2

On another sheet I want the lookup
In this example A11 = 89743

THIS WORKS FINE

Code:

=IF(ISNA(VLOOKUP(A11,n_EmpList,1,0)),0,OFFSET(Names!$B$1,MATCH(A11,INDEX(n_EmpList,,1),0),-1))


The reason I have to use the offset is just because of the way the data is imported, but that bit is fine.

The problem is that the value in A11 = 189743 However I still want it to look up the value on the names sheet 89743.

I thought this formula would work by just using the right() function.
Code:

=IF(ISNA(VLOOKUP(RIGHT(A11,6),n_EmpList,1,0)),0,OFFSET(Names!$B$1,MATCH(RIGHT(A11,6),INDEX(n_EmpList,,1),0),-1))


I presume this is because it is a number as it works fine if I test it with text.

Any ideas???


I'm new to Excel so please excuse a rookie question.

I have the piece of code posted below .... however I don't
know where to place the code.

I am familiar with Access and TradeStation which require
coding, but I don't know where this goes in Excel.

Thanks.

Code:

Sub AddRows()
Dim x As Integer, y As Integer
y = Range(Range("A1"), Range("A65536").End(xlUp)).Rows.Count
Range("E1").Select
For x = 1 To y
    If ActiveCell.Offset(x - 1, 0) <> ActiveCell.Offset(x, 0) Then
        ActiveCell.Cells(x + 1, 0).EntireRow.Insert
        x = x + 1: y = y + 1
    End If
Next x

End Sub





Hi!
I hope so, I choose a good forum with: Excel genral/miscellaneous/programming.
My problem is: I want to copy n times, a range, for example range ("G1:H5") like this:
--A-B-C-D-E-F-G-H
1--------------------e--f
2--------------x-o--x-o
3-------c--r--c-r---c--r
4-------e-u--e-u---e-u
5-------l-m--l-m---l-m
Now, I'm using below code:
Code:

Sub copysteps()
    For i = 1 To 2 ‘two copies, make change
        Selection.Resize(Selection.Rows.Count - 1).Copy
        ActiveCell.Offset(0, -2).Select  
        ActiveSheet.Paste
    Next i
End Sub


but after use this code, the data looks like that:
--A-B-C-D-E-F-G-H
1-------e--f--e--f--e--f
2-------x--o-x--o--x-o
3-------c--r--c--r--c--r
4--------------e--u-e--u
5---------------------l--m
Can you help me modify this code? Thanks.
Regards


ok, I have the following formula Which I would like to add a third option, essentially "AR" could also equal "Z" However, If "AR" does equal "Z" then the answer must also be "Z"

Code:

=IF(AND(BG3="",OR(AR3={"Y","A"})),"Y","")


I thought the following would work but it's not quite right

Code:

=IFS(AND(BG4="",OR(AR4={"Y","A","Z"})),"Y","Z","")


I have uploaded an example sheet, the above code is located in column "S"

Any help would be much appreciated


I tried to utilize the solutions shown here but to no avail.
http://excelforum.com/showthread.php?t=593049

my current formula is =SUMPRODUCT($W2:$W138,H2:H138)

I have 9 buttons assigned to macros to filter in various ways.
They may filter based on Column E or Column F
Code:

   Selection.AutoFilter Field:=5
    Selection.AutoFilter Field:=6, Criteria1:="=up*", Operator:=xlOr, _
        Criteria2:="=no previous*"


or
Code:

    Selection.AutoFilter Field:=6
    Selection.AutoFilter Field:=5, Criteria1:="=up*", Operator:=xlOr, _
        Criteria2:="=no previous*"


I would like my SUMPRODUCT to calulate only the filtered data.
is it possible?


Hi all, and thanks for your help in advance.

I'm having trouble with a calculated item in a pivot table that when created adds records to the pivot table that have no data. Any help would be greatly appreciated.

My pivot table has the following data:

Code:

PivotField1 | PivotField2 | Type | Data
---------------------------------------
A           | D           | One  |    1 
A           | D           | Two  |    2
A           | E           | One  |    3
A           | E           | Two  |    4
A           | F           | One  |    5
A           | F           | Two  |    6
B           | G           | One  |    7
B           | G           | Two  |    8
B           | H           | One  |    9
B           | H           | Two  |   10
B           | I           | One  |   11
B           | I           | Two  |   12
C           | J           | One  |   13
C           | J           | Two  |   14
C           | K           | One  |   15
C           | K           | Two  |   16
C           | L           | One  |   17
C           | L           | Two  |   18


I made the column pivot the Type field, with the row pivots the PivotField1 and PivotField2 fields. I also made my data the sum of the Data field. The resulting pivot table looks like this:

Code:

Sum of Data                 | Type      
---------------------------------------
PivotField 1 | PivotField 2 | One | Two
---------------------------------------
             | D            |   1 |   2
A            | E            |   3 |   4
             | F            |   5 |   6
---------------------------------------
             | G            |   7 |   8
B            | H            |   9 |  10
             | I            |  11 |  12
---------------------------------------
             | J            |  13 |  14
C            | K            |  15 |  16
             | L            |  17 |  18
---------------------------------------


So far, so good. I then try to add a calculated item to the Type field, named Three, which is defined as "= One * Two". I would then expect the following:

Code:

Sum of Data                 | Type      
-----------------------------------------------
PivotField 1 | PivotField 2 | One | Two | Three
-----------------------------------------------
             | D            |   1 |   2 |     3
A            | E            |   3 |   4 |    12
             | F            |   5 |   6 |    30
-----------------------------------------------
             | G            |   7 |   8 |    56
B            | H            |   9 |  10 |    90
             | I            |  11 |  12 |   132
-----------------------------------------------
             | J            |  13 |  14 |   182
C            | K            |  15 |  16 |   240
             | L            |  17 |  18 |   306
-----------------------------------------------


Unfortunately, I get the following:

Code:

Sum of Data                 | Type      
-----------------------------------------------
PivotField 1 | PivotField 2 | One | Two | Three
-----------------------------------------------
             | D            |   1 |   2 |     3
             | E            |   3 |   4 |    12
             | F            |   5 |   6 |    30
             | G            |     |     |     0
A            | H            |     |     |     0
             | I            |     |     |     0
             | J            |     |     |     0
             | K            |     |     |     0
             | L            |     |     |     0
-----------------------------------------------
             | D            |     |     |     0
             | E            |     |     |     0
             | F            |     |     |     0
             | G            |   7 |   8 |    56
B            | H            |   9 |  10 |    90
             | I            |  11 |  12 |   132
             | J            |     |     |     0
             | K            |     |     |     0
             | L            |     |     |     0
-----------------------------------------------
             | D            |     |     |     0
             | E            |     |     |     0
             | F            |     |     |     0
             | G            |     |     |     0
C            | H            |     |     |     0
             | I            |     |     |     0
             | J            |  13 |  14 |   182
             | K            |  15 |  16 |   240
             | L            |  17 |  18 |   306
-----------------------------------------------


Is there any way to remove the rows with no data? I've tried using an if function to check if the value is > 0, but that doesn't seem to help.

Thanks again for your assistance.


I have a cell in which I use the following formula:

Code:

=AVERAGE(C10:C30)


which gives me the result:
Code:

45.4


Now What I want is that is to type in a number like this after 45.4:

Code:

45.4 (20)


When I try to manually type in "(20)" it gives me formula error. How can I get around this?

Thanks


hi there,

i think i had posted a thread similar to this before but its just that the requirements are different this time round.

i have a file and in this file i would like to do a vlookup. but the thing is that the lookup_value might have one or more results to it. something like this:

Code:

    A              B                 C              D

1  P152            vc on us        1.9             99
2  P152            vc on us        1.1             99
3  P152            mc on us        2               99
4  P154            vc on us        1.5             98
5  P154            mc on us        1.4             98


but i know that vlookups are quite limited formulas.

based on this, i want to extract the values in col C IF it is 99 in col D.
something like this (in another separate sheet):

Code:

     A             B             C         D

1   P152                     
2             vc on us          *     
3             vc on us          *
4             mc on us          *
5             mc on us          *


in place of the asterisks, i want to get the values for mc on us & vc on us.
as seen in the first table, vc on us has two values for P152 and 99.
so i want my table something like this:

Code:

     A             B             C         D

1   P152                     
2             vc on us          1.9     
3             vc on us          1.1
4             mc on us          2
5             mc on us


is it possible? i used a sumproduct but it returns me the sum, 3 for vc on us.
wud really appreciate if someone can help me with this.
have attached a sample file,where the worksheet REP09 is somewhat similar to the first table example above and its where i have to get my values under column PERCENTAGE and refer 99 under column IPP.

thanks in adv!!!!