Select Cell In A Table When Row Name And Column Name Is Given
Dec 30, 2013
. A B C D E
1 JanFebMarApr
2 Galle JGFGMGAG
3 Matara JMFMMMAM
4 KalutaraJKFKMKAK
5 PanaduraJPFPMPAP
6 ColomboJCFCMCAC
7
8 City Month
9 Kalutara Mar MK
When I type 'Kalutara' in A9 and 'Mar' in B9 I should get the answer as MK in cell E9
'Colombo' " " 'Feb' B9 " " " FC in cell E9 by refering the above table
can we use MATCH & INDEX or VLOOKUP & HLOOKUP
What is the formula I should write in cell E9 to get this done?
View 2 Replies
ADVERTISEMENT
Oct 20, 2013
I have many tables in separate sheet but in the same workbook. I need in sheet1 to call the specific table based on the value in D3 which it has the table name, how to do this?
View 9 Replies
View Related
Oct 31, 2013
I am trying to randomly select 10 data from a table by using index formula. But there are some blank cell in the table so It kept returning 0 in the cell.
problem.xlsx
View 2 Replies
View Related
Jan 29, 2013
The first code is on the top of the module.
Code:
Private Const OPEN_TIME_COL = 3
Private Const CLOSE_TIME_COL = 4
Code:
RowNum = .Cells(.Rows.Count, CLOSE_TIME_COL).End(xlUp).Row + 1
.Cells(RowNum, CLOSE_TIME_COL).Value = Now
So according to the second code, it records the closing time(now), in the CLOSE_TIME_COL(Which is D)
in the next availabe cell, from the CLOSE_TIME_COL....
But here is the part i am trying to change
Code:
RowNum = .Cells(.Rows.Count, CLOSE_TIME_COL).End(xlUp).Row + 1
So that, what it should do is find the last cell used in column C, (OPEN_TIME_COL) and shift right.
if the last cell used in C is C1, CLOSE_TIME.Value should be in D1
for example somthing like this... but this code doesnt work
Code:
RowNum = .Cells(.Rows.Count, OPEN_TIME_COL).End(xlUp).column + 1
View 2 Replies
View Related
Nov 14, 2006
I have five columns, everyone with a header.
ABCD E
HD1HD2HD3 HD4HD5
2xsdwkj
3xsaer
4xre
5xtrwhj
6xhj
7xdf
8x
9xww
I use filter on the headers, HD1, HD2, HD3, HD4, HD5
If I use Ctrl+Shift+Down(arrow down) on HD1, I manage to mark all the x from row 2 until 9. If I do the same on HD5, it’s stops after the first one(kj). But I want to mark all the way down to 9, even the empty one, but it must not go any further than to 9, last cell I column A. Is there a shortcut for this on the keyboard, or can someone give me some tips on writing a macro witch does this.
View 2 Replies
View Related
Sep 25, 2007
can a macro be used from the selected cell to select every other cell in same column? see attachment for before and after.
View 4 Replies
View Related
Oct 2, 2012
I am using 2010 and want to select a range starting at the same cell all of the time (regardless of whether or not it has contents - so let's say B7. Starting at B7, I want the range to include all of the columns starting at B and go to the end of all of the columns. Then I want the range to include all of the rows starting at B7 and end at the last row. I will eventually copy this range and paste it on another worksheet start on a specific cell of another worksheet. (Perhaps, we can add a name to this range).
View 2 Replies
View Related
Sep 10, 2009
the macro code to select the cell after the bottom of a column of data e.g. I have data in A1:A300 and I require the macro to automatically calculate the last row in the range (in this case A300) and then to select A301 (in this case)
(I know I can record a macro but the number of rows can potentially differ every time I run the macro)
N.B. Excel version 2003
View 3 Replies
View Related
Apr 23, 2014
I want a simple macro which will go to the cell in row one in the active cell's column
View 2 Replies
View Related
Feb 9, 2010
I want to select the first blank cell in the first blank column. I would just look at the column and instruct it to select a given range, but each month the blank column will change (by moving one to the right).
So for example
This Month:
Columns A-Q all have data in it. So, I would need to select cell "R1"
Next Month:
Columns A-R will all have data in it. So, I would then need to select cell "S1" Need code to auto-detect the blank column and then select that column's first cell?
View 2 Replies
View Related
May 28, 2007
How do I code in VBA - I need to select the next empty cell down in a column - ie blank field so that I can transfer the next set of data
View 9 Replies
View Related
Apr 2, 2004
I am finding that I am using the 'go to' option frequently in my macros, but I feel very limited by the options that I have (I need to learn VB-I know) I would like primarily to discover how to select the last cell containing anything (not including blanks like the 'last cell' option in the edit>go to) within a selected row. Ideally, I would like a formula to fill downward a given amount of rows so as to prevent myself from having to fill them all the way down to the end of the spreadsheet (therebye making the file to large). Also, If anyone could guide me to a resource at which I can find similar commands.
View 9 Replies
View Related
Feb 5, 2007
create a code such that it will select a cell which is not empty and display the content in that cell. For example , in the attached file below i would expect output to be cells(2,5) = 12 and cells(4,5) = 13
View 2 Replies
View Related
May 21, 2008
What is the best way to select every third cell in column c starting with cell C5
View 3 Replies
View Related
Jun 20, 2008
I want a macro to be able to select & highlight (ready for copy/paste) all cells in the column range "A2" to the last populated cell in column A.
View 3 Replies
View Related
Oct 24, 2008
I have Microsoft Office Excel 2007 Power programming with VBA in which this website was listed as a good reference site. I am working on a spreadsheet where I want to select the last non-empty cell in a column and the column always has some cells that are empty, though it is NOT the last non-empty cell in the column. This spreadsheet is automatically generated and I want to change this particular column to all one format and all one number, 0042. This spreadsheet is then loaded into another system via tab-delimited txt file, so this number is meant to serve as an indicator. Sounds like I gave too much information, just wanted to be clear.
View 9 Replies
View Related
Dec 12, 2011
Data example:
AB1AREANumber2SW103SW114SE125NE136SW147SW158SW169NW17
I need a code in VBA that can find the 1st cell in col A that contains "SW" and store the number in col B as V1= # then find the 4th cell in col A that contains "SW" and store the number in col B as V2= #.
so that i can do some math with the numbers. The code should result in 10-15
View 3 Replies
View Related
Apr 13, 2012
How do I select a cell based on its row & column count?
For example, Range("D8").Select
Instead of using ("D8") ,I need to select the cell based on its row & column count, i.e. (4,8)
View 3 Replies
View Related
Jul 30, 2014
excel VBA to find the first colored cell in the column "A" and copy it to the Range (E1)
View 5 Replies
View Related
Jun 20, 2006
how i can go about finding the next empty row (and select the first cell of that row (column A))? edit: It probably should be noted that there are cells in Columns A through P. There are rows where all and/or just one cell contains data per row. So i cannot use a " lookup" based on a single column.
View 2 Replies
View Related
Feb 27, 2008
I am trying to paste data into a sheet immediately following the last weeks data. I went to use an offset function like :
Range("A2").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
However, each weeks data is "Grouped" and therefore the .end(xldown) only takes me to the bottom of the visible rows, and the offset function selects one of the hidden cells.
View 5 Replies
View Related
Nov 15, 2008
I am making a worksheet that I intend to use to track my money. When I first open the worksheet, it opens on a tab where I can click a button to report a type of transaction. For example, if I make a withdrawl from the bank for $50, I click the button, it takes me to the sheet that tracks my bank-related stuff, selects a cell and opens up a form, at which point I type in what the transaction consisted of. However, the sheet also tracks what is in my wallet, so I'd like to finish reporting the bank transaction in the form, and have a button to click that reports the wallet part automatically.
So, essentially what I need to do is select several non-contiguous cells that are in the last row of the bank sheet, copy them, switch to the wallet-tracking sheet, and paste them in a row that is one past the last row of that sheet. The paste should keep the cells next to each other, even if they were non-contiguous when they were being copied.
View 10 Replies
View Related
Aug 10, 2012
Highlighed the Column and Row of the selected Cell. I have seen someone using it. Whenever he click on any cell the related column and Rows clour change. For Example if I select Cell B5. The Column B and Row 5 change its colur from B1 to B5 and Row from Column A to Column B. If this option is already available in office 2010
Column B
Row 5
View 3 Replies
View Related
Aug 22, 2007
Was wondering .. as I can not seem to find the right type of coding.
I am trying to have the file open in the next blank cell on column C, ut at least have 5 lines showing above it as there is Freeze Panes.
Is this possible for excel to do upon opening?
View 8 Replies
View Related
Apr 18, 2013
I try to do something like this
VB:
Sub autofill()
Range("A1").Select
Selection.autofill Destination:=Range("A1:A1048576 "), Type:=xlFillDefault
End Sub
but i need to select first cell in every 20th column and then execute autofill till the last row that excel provides. I can only do this manually but I have for now 125 columns to fill or to write some monstrous code. Unfortunately I do not have the knowledge to do this in a smart way and I guess there is an elegant way to solve this problem.
View 1 Replies
View Related
Oct 14, 2013
I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(LR, 1), Cells(LR, "A")).Select
View 1 Replies
View Related
May 2, 2008
I'm trying to get a code that will do the following: I've got a list of stocks followed by extra information in de columns to the right of it. If, based on that extra information you'd like to know more about the stock, I'd like the user to just select the cell that holds the name, and click a button.
What this button should do is the problem for me right now: it needs to select the cell left to the one selected holding the name of the stock.
The cell to the left holds the code that is used to lookup information in Bloomberg. This cell is invisible to the user since I hide it to make the tool look good. How do I write a piece of code to select for instance: B10-1=A10. The rest is just a recording job.
Also, I know you can add text to the text in a cell, however in this case I want to cut some text. Is the any way to get: "B10-1"-"equity sedol1", leaving me with the text in A10 minus equity sedol1?
View 9 Replies
View Related
Apr 12, 2013
I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?
View 8 Replies
View Related
Jun 6, 2014
I have multiple tables like the one in the picture and have to duplicate this code for different known ranges.
View 11 Replies
View Related
Nov 3, 2006
I'm trying to select the range starting from the last used column all the way to the last used row of that column and I have no idea where to begin.
View 6 Replies
View Related
