i am trying to find the location of the current cell and use the information to print it to screen. For example, if the cell A1 is selected, i need to get the location A1 and use this value for a vlookup. is there a method that returns the current cell location "A1"??
activecell.address
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
I have a worksheet with a various data in column B.
1/ I want to search column B one row at a time looking for a string value of 'Town'.
2/ When the first row containing this string is found select a left 35 chars substring of the value in the column C and copy this down in column A if the value in Column B = '0000/00' UNTIL the string value of 'Town' changes
3/ Repeat 2/ until the last instance of 'Town' has been processed.
4/ Column A will have some empty cells where Town has not been populated. These rows should be deleted and replaced with one empty row between each change of Town value
3/ Continue pasting this string value into each successive row
I've attached an example of the data I am trying to solve this problem for. Basically I am looking for strings of 26 blanks or more in a row, but I want to determine the ending location of the last string of 26+ blanks. I've been able to determine the location of the maximum string of blanks, but that max group of blanks isn't always the last set over 26.
Any link to find the location of last string of blanks of 26 or more? The example I attached is a good example since there are two strings of 26+ blanks with the last one being shorter. I've also include the answer I'm looking at the end of the data in the example.
Example.xlsx
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.
simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
(I am using Excel version 14.0.7106 and MS Office Professional 2010.)
I have a macro in which I have named a range of numbers in a spreadsheet, used the "find" function to find a particular number in that range, and now would like to copy some information into a cell in the same row as the found number. However, when I try to move over to the cell that I'm copying to, it only goes to the 1st row in the range that I have defined.
Here's the portion of code I think you need to see. Everything works...it finds a match...but then I don't know what to do from there to get the information to the correct row:
'Grab the 1st project number
Range("C8").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value "" Then
prjnum = ActiveCell.Value
Selection.Offset(0, 8).Select
[Code] ..............
I have a worksheet size roughly 100 * 400 cell. there is roughly 100 cells filled with RED color and with different value.
When i used FIND ALL format - RED, I can locate the cell location and the cell value. And I wish to copy these 2 info onto a new sheet so that on the new sheet, I can create a hyperlink and easily access to the location of the RED cell.
However, in FIND and REPLACE, i have a list of results on what I want. But i cannot copied the information out. So is there anyway to copy it out or to achieve what i want?
For eg.
RED value
cell
50
A3
87
D71
251
AQ5
and so on.
I've sent out a survey asking people to sign up for one of two groups, A or B. I have a table with a long string of responses, with the name in column A, and in columns B or C there is an X that shows whether they have chosen Group A (column B) or Group B (column C)
I am trying to write a bit of code where it would paste the names into a new table with the headers Group A and Group B. Here is what I have pieced together so far:
Sub Create_Groups()
For i = 2 To Range("B2").End(xlDown).Column()
If Range("B" & i).Value = x Then
[Code].....
Is there any way to use the offset formula in reference to the location of another cell?
Here is a simplified version of my problem:
Sheet1!A1 refers to Sheet2!A1.
Sheet1!B1 refers to Sheet2!D1.
Sheet1!C1 refers to Sheet2!E1.
Is there any way that I can say that Sheet1!A1=Sheet2!A1 and then use the offset formula based on the location referenced in Sheet1!A1? The cells being referenced are merged cells one column wide and varying rows long, so there is no real pattern to follow except that the corresponding cells (the ones that I want to use the offset formula on) are all taking up the same amount of rows and are an equal amount of columns over (Sheet1!B1 refers to 3 columns over from Sheet2!A1, Sheet1!B1 refers to 4 columns over from Sheet2!A1). The spreadsheet is a fairly complex one and it would be much cleaner and less likely to have any mistakes if I was not referencing each cell individually.
I have received a spread sheet with all the data in column A. It follows the format of A1 = Name, A2 = Email, A3 = Name, A4 = Email, etc. for over 800 entries. What I need to do is etract all the email addresses and place them in column B alongside the Name. I don't have any experience with Makros and tried to use one but kept deleting the wrong data.
View 5 Replies View RelatedI have a row b1, b2, b3, b4, b5 etc. I need to change it to c1,,c2,c3,c4,c5 etc. Is there a fast easy way to do it. I know about $ but I'm past that.
View 6 Replies View RelatedI would like to how I can move the words "Total List Price" over to column "C" rather than have it displayed in column "A". If someone can please advise me as to what part of the code I must change in order to have these changes take place
Private Sub CommandButton1_Click()
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.ClearContents
Range("A8").Select
Selection. Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 9), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Application.Goto Reference:="models2" ....................
I have a column full of data like the below with relevant data on the same row.
I wish to locate the cells which have a specific section most importantly without the "cartridge only" so for example with the below
Word Academy (Nintendo DS)
Word Academy (Nintendo DS) (Cartridge Only)
This would show 2 given there are two with the specific text "Word Academy (Nintendo DS)" there will always be a cell without the (Cartridge Only) part so for this the formula =COUNTIF(D:D,"*" & D1 & "*") seems to do the job to simply count the cells with that in however I do not wish to do this.
What I need to do is locate the cells that have this in but match them together in a large sheet so basically I somehow need to be able to locate the two corresponding cells which both have one of the cells values of "Word Academy (Nintendo DS)"
The cells have data in the row which I need to tie together to create a total value.
For example
Title Qty
Word Academy (Nintendo DS) 5
Word Academy (Nintendo DS) (Cartridge Only)6
I need to locate the all the cells with "Word Academy (Nintendo DS)" in which would mean just the two cells "Word Academy (Nintendo DS)" + "Word Academy (Nintendo DS) (Cartridge Only)" in the above instance but I need it to then see the cell which has that in its title and add the quantity of the two together leaving me with the below
Title Qty Actual Qty
Word Academy (Nintendo DS) 5 11
Word Academy (Nintendo DS) (Cartridge Only)6 11
Which is basically just adding together the quantity shown on the spreadsheet in the rows with the containing text "Word Academy (Nintendo DS)" and as "Word Academy (Nintendo DS) (Cartridge Only)"
On the attached spreadsheet Column L is an example of the result I would be desire, I have put a basic =sum formula in to just represent the value it should be showing.
I imagine this is pretty simple but for the life of me I can't figure it out. Here's what I want to do...
Find MIN in Column F
After finding the MIN in F (let's pretend F222 is the current min)
Show the text in the field next to it (in this case display text from E222)
i'm not sure how to describe this. i've never had to do this before. here is what i'm trying to do. and it's not working.
=MIN(B2:B(G2-G3))
basically instead of saying =MIN(B2:Bx)....i want the "x" to be variable based upong the G2-G3 condition. can this be done some other way?
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here!
...
Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that?
The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
I've been searching for ages trying to work out how to do this but have so far only managed to confuse myself. i have office 2007 and I have found some code to convert an excel sheet to PDF, however I need it to saveas the contents of cell (e6) and save to a location on our network drives (C:TEMP).
View 2 Replies View RelatedI have the following function and cannot get the "cut/paste" section to work (After sOp = "Look at timestamp and determine which entry is newer".
Code:
Sub DeleteDuplicate(WSName As String)
On Error GoTo ErrorHandler
Dim sOp As String
[Code]....
I was just wondering if its possible to create a Hyperlink to open a location with the link ref in another cell?
For example, If I created a shape (Insert > Shape) then assigned a hyperlink to lets say Sheet 2 cell A1 & in that cell the link to open My Documents?
Sheet 1: Shape (as a button)
Sheet 2: Cell A1 - C:UsersUSERNAMEDocuments
Then when I click the 'button' it doesn't go to Sheet 2 cell A1 it actually opens the documents?
I would like to return the cell location of names in column A as they are located in columns B and C, and return the locations in column D....
View 9 Replies View Relatedway to store a cell's location to variables.
Something like:
int a, b
Cell(a, b) = ActiveCell
I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.
Is there a way to have excel track where my mouse is hovering(say if I have it hovering over cell A4) and have it return the value of A4 in a different cell (e.g B7)?
This would occur in real time - that is, as my mouse moved over the spreadsheet, B7 would update in real time.
Note I would also accept someone selecting the cell - hovering (no select) would be ideal but selecting is OK.
i need to loop through a column of values and get each value
Dim LastRow2 As Long
Windows("SCFOutput.xlsm").Activate
Sheets("Q2SCNeg").Select
Columns("A:B").Select
ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select
LastRow2 = ActiveCell.Row - 1
For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells
MsgBox ActiveCell.Value
Next c
i did this but it selects the first blank row (row 15) and gives me an empty message box 14 times (which is right, but i need 14 values) the reason i need the loop is because the number ov values will always change
Is there a standard way across all Excel formulas to have the cell location returned rather than the value of the cell itself?
In other words A33 instead of "52" (with A33 being the cell that contains "52")
Increment all cells located in Column H up by one (the one above it) cell Except for the header cell (since its the header)(H1),
H2 will always be a blank due to the make up of my database so that will not cause the header to be re-written.
Column H is not an entirely filled column and some cells are empty (if this info was needed in optimizing the code),
Im seeking the quickest and easiest way of implementing this since the database is very large.
code that will go to a cell in Sheet1 column A that has the same or closest value to cell B11 in another workbook. The number values in the column are to three decimal places and the run in order down the column from lowest value to hightest.
View 6 Replies View RelatedI have the following formula/array:
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4
Now this works perfectly fine, but I am wondering if it is possible to replace 2013-004 so that it reads the text from a cell on that sheet to complete the file name.
Example:
A
B
1
2013-004
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4
I want to replace the 2013-004 in B1 with text from A1. I have tried multiple methods but it just always assumes it is part of the file name or breaks the formula.
I just used the excellent formula =row(index(range,match(1,index,--(range)>0),0),0))) to give me the first instance of a number, but i am wondering if there is a formula to give me the last instance of the number as well? Maybe? This would save me much time if possible.
View 11 Replies View RelatedI am trying to code where when someone hits the command button it will popup with the browse form and they find the location of the file and it puts there choice in a cell. don't know if it matters but the location would always be a picture. Here is what I have but it shows -1 in the cell
HTML Dim strFilePath As Variant
strFilePath = Application.FileDialog(msoFileDialogFolderPicker).Show
shUserInformation.Range("D12").Value = strFilePath
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View 3 Replies View Related