I have been searching Excel Help for a code that will allow me to program the Ctrl + Home function.
Specifically, I have a macro that leaves the selected cell too far down on the spreadsheet. Instead of making the user scroll up or press the Ctrl + Home keys, I want to write a code at the end of my existing macro.
Does anyone know the equivalent of CTRL (Home) to move the cursor to the a1 position in VBA? I have tried Range(cells(1,1),cells(1,1)).select but with no success
View 3 Replies View RelatedI have several spread sheets I sort daily, now suddenly today when I hit Ctrl / home it wont go home and I can not sort the sheet.
View 7 Replies View RelatedCan you ctrl-F for only highlighted cells? I want to change all cells with '#VALUE!' in them, however, I only want to do this for a few columns.
View 4 Replies View RelatedI was thinking of how the Worksheet_Change(ByVal Target As Range) macro can be used to paste values instead of formula when the user uses Ctrl-C and Ctrl-V. Instead of assigning a macro to a command button for user to activate.
Suppose I have a worksheet where the range ("D7:D56") is where I would want the user to paste his values in it.
I have to factor in the possibility that these values have formulas attached to them, and that the user is not tech-savvy enough to know about the "paste special --> values" functionality of excel, and chooses to use the Ctrl-C, Ctrl-V method instead.
What then appears are ####### which might alarm the user.
Neither would we want the user to press a button (which we can assign the xlpastevalues macro as an alternative) as that adds an extra step for the user. There is also a chance the user might not use the button, or overlooked it.
Is it possible to use the Worksheet_Change(ByVal Target As Range) macro, or sth similar, to paste values instead of formula when user uses Ctrl-C and Ctrl-V?
CTRL+END not move to the last cell on the worksheet. How to make to move to the last cell?
View 9 Replies View RelatedBasically it is a if statement saying that if the selected cell falls between 1/01/06 and 31/01/06 then Jan would be selected. The end part is not a problem; I’m just not sure how to write the one line of code that would test if the cell falls between the two dates. I attemped to create it as shown in the code attached below but wasn't successful. I used an else if statement to test the other 11 months.
Sub test()
Dim SelectDate As Range
Set SelectDate = Range("SelectedDate")
If selectedDate >= 1 / 1 / 2006 And selectedDate <= 31 / 1 / 2006 Then
ActiveSheet. PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Jan"
ElseIf selectedDate >= 1 / 2 / 2006 And selectedDate <= 28 / 2 / 2006 Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("PnLDate").CurrentPage = _
"Feb"........................
I am writing a sports schedule or fixture list. The home teams are in column B and the away teams are in column C.
I want to prevent any team having 3 consecutive home matches. Is there any way a formula can be used to alert me if there are 3 consecutive home matches.
I have used the PMT function but this gives me the total to pay per month.
I want to know what the repayments would be and also allow me to add in each one of the periods and extra repayment option.
So if in the year I wanted to pay an extra $100 per month, I would put $100 next to each period as in a particular period(s) I might not have to put in there but want to predict what the amount owing on the house is.
Is this possible or is this too complicated.
On the computers in my office they have a My Documents folder that is in the directory C:Data. There is also a My Documents folder in the usual windows location C:Documents and Settings etc. Is there a function that will give me the path of the folder that is on the desktop? The macro will be run on computers with the folder in different locations which is why I need to look it up.
View 5 Replies View RelatedDoing a search function where the cursor is on a row some where (unknown).
I would like to move the cursor to the first column of the spreadsheet.
I am working on a budget spreadsheet and want to find a formula that will automatically tell me what my take home pay is depending on the yearly salary and see if covers my expenses (linked to another spreadsheet). It will be easier to budget my expenses depending on my salary. My attempts to create IF and = haven't worked.
View 2 Replies View RelatedI created a custom tab withthe UI editor into my personal sheet which is set as startup and I want to have this tab automaticly shown when it is opened.
So when the workbook is opened I would like the show the tab Andre rather than the home tab.
I have an older laptop with XP and 2007 Office / Excel this works, with the new laptop doesn't work?
basically - I open a new spreadsheet - blank - just basic
I used to be able to click the data tab, then click From Access, then enter my URL to an access database on my website and BAM my data would populate in cell A1
with VISTA - I get the same error over and over again, tried a different XP machine, no problem? What gives?
error box states: Microsoft Office Excel cannot access the file [url]
there are several possible reasons.
the file name or path does not exist
the file is being used
workbook has the same name
I'm starting to really hate VISTA after about 3hrs with this new laptop!
Found the wonderful code here at Ozgrid and would like to change the return to home selection.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("d4:Ad104"), Target) Is Nothing Then
Int_color = Target.Interior.ColorIndex
Select Case Int_color
Case xlNone
'Question, what does "xlNone" do?
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 3
Target.Value = "true"
'Range("A1").Select - would like to change this to move the selection to
'the present row clicked, column c. If I click on G9 the selection returns
'to C9.
Case Else
Target.Interior.ColorIndex = xlNone
Target.Value = "false"
End Select
End If
End Sub
I have a category field, and a money spent field in a table.
Category Money spent
1 $12
2 $19
2 $122
1 $5
4 $65
5 $34
5 $12
3 $26
Category 1: $17
Category 2: $$$
and so on..
So I'm looking for a formula that returns information to one cell. A formula that looks if in 'this' column you see the category code '1' for example, then return to me the money spent for ALL the 1's. It should return $17...
The table above is small just for demo purpose, my table has 8 category codes at the moment and the number of rows can be between 15 and 50 I guess... it's a monthly break down.
I would like to see how much is spent in total for each category...
In addition if possible, can this be done for two separate tables... We are two people, and I have two table recording expenditure.
So the formula would need to, from these tables:
Person A Person B
Category Money spent Category Money spent
1 $12 2 $14
2 $192 4 $25
2 $122 4 $65
1 $5 1 $32
4 $65 3 $75
5 $34 3 $85
5 $12 2 $12
3 $26 2 $11
Category 1: $49
Category 2: $$$
and so on...
Look up in both tables, search the two category columns for category '1' for example, then return all the $'s added together... So for Category 1 now it would show $49...
When using 2013 Excel in the Home tab, line 1 is not visible. Using the File tab will show line one, but the Home title bar is unavailable. Toggling back and forth is not efficient. No one in this office has ever seen an Excel program not display a full page under the title bar in the Home tab.
View 1 Replies View Relatedi have tried using the letter M as a shortcut key (because it is to activate a "Move" function) but unfortunately it doesn't work. I gather from this that not all of the keys are available as shortcut keys presumably because they already have function.
Is there a way around this? and/or is there a list somewhere of the available shortcut keys?
the keyboard shortcut to switch between workbooks in the application seems to have stopped working on my boss' machine.
View 4 Replies View RelatedHow could I detect if the CTRL (Control) or ALT key has been pressed. I want to execute a macro when either one is press over a given cell.
View 3 Replies View RelatedI've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy()
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub
The column is only 25 rows tall.
I recently moved to Central America and now am working on models that are either in $ or in the local currency (Q). I do not want to change my default currency value in the regional settings of the control panel because it is more convenient for me to stay in $ but it is a pain everytime I want to set a worksheet in Q and not in $.
So, I am wondering how I can change the default values in the currency drop-down list on the Home toolbar under Numbers in Excel 2010.
Currently, it gives me the change to apply the following currencies:
- $ English (US)
- £ English (UK)
- € Euro (€ 123)
- ¥ Chinese (PRC)
- fr. French (Switzerland)
Instead of either the £, ¥ or fr., I would like to have listed the Q, my other frequently used currency.
I'm trying to replace Forecast12.xls to SEPFCAST Forecast12.xls but I keep geting error message that says "the name you entered is not valid" and gives me 3 reasons. Both the location of the file is the same and I have spelt it right. Im not sure what is causing this error as it let me change in a different sheet to the file name as above.
My other option is to link manually about 500 cells which i dont think is an option.
Been using this code...
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 11/28/2012 by Doug Garn
'
'
Dim Rng As Range
Set Rng = Range(ActiveCell, ActiveCell.Offset(0, 3))
Rng.Select
[Code]...
What it does, I hilite (put focus on 1 cell) a cell and it copies that cell and 3 more to the left, switches to 2nd xls file, pastes that, puts the cell focus on next line, then goes back to the first sheet and I just have it select a random cell(s) so it does a Ctrl-F properly.
Problem is the 3 ways I have above to bring up the find box aren't working for me. It won't find anything and I don't understand why. And I hope I explained this correctly.
know the keybaord shortcut to select a range? Indeed, how can i trick excel to use CTRL SHIFT Down in a range with blank data and to select the last data in the range?
View 1 Replies View Relatedi am trying to modify and existing array formula
=VLOOKUP(R2,TRIM(Codes!$C$3:$D$283),2,FALSE)
but ctrl shift enter does not seem to work. has anybody else encountered this problem?
FYI auto calculate is on, lookup value and table array are all formatted the same. as i said, the formula works but i need it updated for one extra row.
How to send by holding Ctrl+Alt and i need to press Down Arrow
I tryed with
Sub test()
Application.SendKeys "^+({DOWN})"
End Sub
I recently upgraded my computer. I noticed I can no longer enter array formulas. When I tried to enter an array formula using CTRL+Shift+Enter, nothing happens. I don't get any error, just nothing happens.
If I do the same exact thing on my old computer, it works - formula is converted and I see the braces {} added as part of my formula. Am I missing a macro or add-in? I'm using Excel 2002 - same version on my old computer. Is there another way to generate an array formula besides using CTRL+Shift+Enter?
I want to be able to define on excel 2007 what gets cut when I cut and paste
cause I just want text, comments, and font formatting, i dont want the fill of the background or border when I cut and paste
Is there away to disable the Ctrl PageUp / PageDown function to stop users seeing worksheets in a workbook? I know I can use sheetveryhidden etc, but I am trying to keep the coding as simple as possible. I have found reference to this problem on a couple of occasions within the forums but no definitive answer.
View 4 Replies View Related