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)
When i select a cell with the mouse, so its active, then i want S27 to show the location of the selected cell.
View 1 Replies View Related(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] ..............
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 RelatedIs there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...
1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
*(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)
2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)
3) Open another workbook on the local drive (OilChart.xls)
4) Run the macro in the new workbook (OilChart.xls) to update the data
5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).
This is the code I have so far.
Private Sub btnFind_Click()
Dim vehicleNumber As Variant
Dim sourceLocation As String
Dim sourceFileName As String
Dim fileName As String
Application. ScreenUpdating = False
I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook.
I have a list of items in column A. Column B has each item's net price. Columns C-N shows the consumption of the items per month. Column O shows in which location the items are stored.
I need to do a sumproduct so that it shows the value of the items retrieved from that particular location per month.
If it didn't have to be by location, I would've simply done something like =sumproduct($B1:$B10,C1:C10) and copied it across the columns. How do I tell excel to sum per location as well? I know there is a simple solution to this, just not seeing it...
EDIT: I tried =SUMPRODUCT(($B1:$B10)*(C1:C10)*(O1:O10=$B1)) but it gives a #VALUE error
What i am trying to do is make a command button appear when you hit any cell of the row its on. For example, If you were to hit any cell on rows 1 or 2 the command button will appear and when I am not on the visible property goes back to false. I have a ton of buttons on this sheet and I am trying to clean it up so buttons only appear as needed.
View 9 Replies View RelatedWhy than going line by line on a document that has 60k rows!
I have a document with NAMES in column A and ROLES in column I. If the user has more than one role..their name will be listed in "A" for each role in "I". I'm trying to purge down a file that has 60k + rows. Lets say i want to see all the people and all their roles if they are in "PROGRAMMER_ANALYST" role...can this be done by macros? or formula?
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else
I have a spreadsheet containing 3 sheets. MW01 (first floor inventory), MW02 (second floor inventory), and SEARCH.
The sheets contain a part number in one column and a shelf location in the second column. I currently have over 1000 parts listed and growing.
I want to be able to type a part number into a cell on my "search" sheet and have it return the shelf location of that part.
Is there a good way to find out after the exists returns True in which place in the array the Z is located in ?
...
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
Z = a(i, ii) & ";" & a(i, ii + 1)
If .exists(Z) Then
msgbox "The array location for existing Z value is ..."...
...
After I run Hlookup on a product, I would like two variables, Row and Col, to assign the actual row and column of the spreadsheet to the location in the table. I have a small worksheet/program that I have attached, what I am trying to do. The program works, but my code is very long for the little bit of information.
Sales.xlsm
I have a table with customers and their addresses (sheet2) and I need to find their GPS locations in sheet1.
I guess the logic would need to be something like this:
find CityName and StreetName in sheet1 where StreetNo is between minStreetNo and maxStreetNo return GPS X and GPS Y
with the formula or VBA code?
Example.xlsx
I am looking for a VBA Code which will:
1. Search for a specific File.
The file will the same file (ABC.xls) BUT the user might want to change it by typing a new name & Extension in the first Inputbox.
2. A second InputBox (Drive & Path) should now Pop-up.
If it will be left empty - the search should take place throughout ALL the installed HDs.
If the user decides to minimize the search range he/she will type a Drive & Path, the search should reduce/constrain itself to the specific Path ONLY.
My task is to show its Path location in a MsgBox (or to show "file not found")
It might be a good idea if the code can be extended beyond presenting the Path to:
1. Open the Windows "Explorer" (or "My Computer") in the specific Path with the file being selected (marked).
2. Opening that file, in "Excel", upon finding it.
A 3 section, VBA, code will be more than appreciated.
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 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 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 an excel spreadsheet that has an instruction page for the first worksheet. I have included screen shots for clarification. One of the screen shots is of the Find/Replace window. Sure enough, when people started looking at this sheet, they tried to click on the picture of that window to use it. I would like to make it so when they click on this picture they either a.) Open that command window or b they can start using it right there and not be able to move it so the next person will always be able to use it from the start.
View 3 Replies View RelatedI'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].....
I know there is something I am missing here and I can't really find the solution I need in past threads. I have a macro that calls about three macros in a row, then once it has done those tasks, I want it to show a particular userform using the userform.show statement. However, when it comes to show the userform I get an error saying that it cannot find the specified object? This is quite frustrating and I think I fixed it in another part a while ago, but I can't remember how.
View 2 Replies View RelatedIs 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 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 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 am trying to find a function to calculate the percentage grade as the semester progresses. I'm attaching an example of my sheet what I need is column "D" to show the grade percentage from columns "E"-"AK" as each grade is entered.
View 3 Replies View RelatedI am trying to do is create a button that when pressed, will bring up the Find screen (which can be brought up by pushing ctrl + f)...I tried to record it as a macro but nothing came up.
View 9 Replies View Relatedi'm using Sendkeys Edit Find to open the Find dialog box (works well)...i would like to restrict the searching to only 4 worksheets (of 14) of the workbook...?
(i see the option to select Sheet or Workbook)
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
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.