To Find Part Contents Of A Cell
Apr 16, 2009
I've got a code that does what I want it to do, but I have to select the start point manually becuase it's position varies on different documents. It's usually around row 650 out of 1200, but the cell I want to start the macro at always contains the word ELECTRONIC. What I would like to do is modify my code so that it finds that cell itself and then runs the rest of the code. I'm not very good with VBA, but I've tried a few things like Dim LR-2 and using that but I get error messages about duplicates.
Here's my code
HTML
Sub Macro1()
'
' Macro1 Macro
' SALES ORDER FORMAT
'
'
View 9 Replies
ADVERTISEMENT
Dec 2, 2011
I need a way to copy part of contents from a cell, the cell contains product information like size, name and weight of a product, and I need a copy the weight to be put in it’s own cell, here’s a copy of one cell “E65 MAPP SKDV 5 DIGIT 90G ST.K” the information I need from this is “90” the number is always followed by a capital G, but it’s between two and three digits where the lowest is 55G and the highest is 300G. Is this possible?
View 6 Replies
View Related
Apr 4, 2007
IF statement that looks at a cell to see if it contains a word.
ie IF A2 has " Total" in it then do this
View 9 Replies
View Related
Jul 30, 2014
I have a spreadsheet with four columns of text.
In column A, i have multiple levels followed by a letter (i.e. Level 1A, Level 1B etc).
In column B, i have some other details and then so on and so forth.
In column C/E/G lets say, i want to copy the information from column A to show only items that appear as "Level 1" (not "Level 1A", i only want it to check for things without the letter at the end). Then the same in column E but with "Level 2" and so on and so forth.
Column A...Column B-Column C...Column D--Column E...Column F--Column G...Column H
Level 1A....Metals----Level 1A....Metals ---Level 2A....Integral---Level 3A....Television
Level 1B....Energy----Level 1B....Energy--- Level 2B....Flowers---Level 3B....Kitchen
Level 1C....Synergy---Level 1C...Synergy--Level 2C....Full
Level 2A....Integral---Level 1D....Orders
Level 2B....Flowers
Level 1D....Orders
Level 3A....Television
Level 3B....Kitchen
Level 2C....Full
I also have data in Column B that is to do with column A (i.e "Level 1A" - "Metals") and so on with the following columns. I want the items that are in column B to also move over to column D when the things from Column A move to Column C, so at the end it will appear as below so it appears as above.
View 1 Replies
View Related
Apr 29, 2008
I use this code to "search" a value in columns A or B and if found to select the row the value is in.
Trouble is if I look for say value 11 (the code finds 1) I understand what happens but need your help to fix it hoping to keep it as simple as it is. (I cannot use filters)
Dim wht As Variant, Found As Range
wht = InputBox("Searchfor")
Set Found = ActiveSheet.Columns("A:B").Find(what:=wht)
If Found Is Nothing Then
MsgBox "Not found"
Exit Sub
Else
View 9 Replies
View Related
May 20, 2009
As part of a macro i do a find and replace on a range cells that replaces "/1" with nothing (""). Therefore 2/1 becomes 2, what i don't want though is if the de-nominator includes a 1 but not only 1 to keep the whole of the de-nominator, say 8/11 does not want to become 8/1.
View 4 Replies
View Related
Dec 12, 2006
i can use the VLOOKUP function to find entire cell in one table and paste the required data from the row....
Is there a function to search part of data and paste the required data
View 9 Replies
View Related
Jun 26, 2012
I have a formula in cell J2:
='H:Folder[CI10001G.xlsm]Sheet'!$C$5
I need a macro that will change the part of the formula that is "CI10001G" to the value of cell A2 which would basically be something along the lines of "CI10004D" so the formula would change to the following:
='H:Folder[CI10004D.xlsm]Sheet'!$C$5
And then this formula will then successfully look in the newly directly file path.
View 9 Replies
View Related
Nov 24, 2008
I need to find the "x" in range C2:C44 and replace it with a value (variable) specified in cell I2
The catch is that I then need to be ablt to change that character when I2 changes.
Coloum C
*VER
*TxSTATUS
*ON
*OFF
*TxBANDS
EG
I2 - Variable = B
*TxSTATUS will become *TBSTATUS
I2 - Variable = A
*TxSTATUS will become *TASTATUS
But " *OFF " should not change.
I can change *TxSTATUS to *TBSTATUS but then I can change *TBSTATUS to *TASTATUS
Please see attached example file as it shows it better than I can explain it.
View 11 Replies
View Related
Sep 4, 2013
I need a formula to check a range of parts and bring back a value of 0 if matched or the value in another cell if it doesn't match.
Part number H-200-3
Part number H-200-3-A
Part number H-200-3-B
If I enter any of the above part numbers then cell B1 returns 0 if it is a different part number then B1 would need to match cell A1.
View 2 Replies
View Related
Sep 3, 2006
On Sheet2 I have a table where I want M2 to do the following:
1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.
2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"
3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.
So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.
Maybe combining the persons name in to one column would make it easier?
View 9 Replies
View Related
Apr 3, 2007
I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.
Private Sub Update_Click()
Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)
Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single
oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5
View 9 Replies
View Related
Jul 6, 2014
I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)
In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)
What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)
There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.
View 5 Replies
View Related
Aug 12, 2014
I have data arranged in cells A1 to Z1 as Name1, Amount1, Name2, Amount2, Name3, Amount3, etc. Is there a formula that can determine the name that corresponds to the maximum amount in the row?
View 7 Replies
View Related
Aug 18, 2014
I have a worksheet where columns C, F, I, L, O record scores within a league. Each row records a persons score in that league and there are two rows per person recording their score and their handicap score. So Person A would be on rows 3, 4, Person B on 5, 6 etc. The persons name is recorded in Column B.
What I would like to do is to have a cell(s) elsewhere in the worksheet which show the highest score in that league and display that score and the name of the person who achieved it. This ideally would need to be done for the highest score and the highest handicap score.
For the life of me I can't even begin to work out how to do that or even know if it is possible in Excel.
So to clarify, lets say the highest score is in cell L7 and the highest handicap score was in M3. The cell(s) containing this formula should then show the name in B7 and the score in L7 and below it the name in B3 and the score in M3.
View 7 Replies
View Related
May 11, 2007
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View 3 Replies
View Related
Feb 20, 2008
I would like to find a text string in a document, move the information in the cell to the right of this to another cell (say A1).
View 8 Replies
View Related
Jun 21, 2012
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
View 1 Replies
View Related
Dec 19, 2011
I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.
Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).
The relevant section of code attached below:
Dim role_count as range
Dim role as string
If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Range("role_count").Select
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value
View 4 Replies
View Related
Apr 16, 2009
I don't know if this is even possible via VBA, but thought I'd throw it out there and see what you guys think.
In the attached workbook column D contains fields for the UsagePN. If a part is "rolled-up" in our order system to a new part number, then the OLD part number, (in colmn B), is associated with a UsagePN. What I need to do is, for every part that has a UsagePN in Column D, I need to find that part number in column B and add the inventory values from the old part number to the new one.
For Example:
Row 74 in the attachment has a UsagePN of AC1230105V. I need to take the values from cells G74 thru K74 andd ADD them to the existing values in G77 thru K77, which are the inventory values for AC1230105V.
In other words, since part number AC1230105 has rolled-up to the new part number AC1230105V, I need to add all of the inventory to the new part number, AC1230105V.
After this is done for ALL parts with a usage part number, I will delete all the rows containing UsagePN's.
Here's the catch, as you can see in the example, there is no consistency, so a simple formula like adding the values to the same part number with a "V" at the end won't work. I guess you would have to do something like for each row with a value in column D, dim that row so you can refer back to it, then search for a match in column B, insert a "helper" row below where the part number is found to add the quantities, then replace the original with the helper row, and then delete the helper. One of you guys probably knows a better way.
Keep in mind that you won't always find a match, so the macro needs to continue to the next part if no match is found.
I hope I was clear. This one looks like it will be a nightmare, but I hope somebody on here has some ideas.
View 14 Replies
View Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx
View 10 Replies
View Related
May 5, 2014
I want to go ("E:E") and if part of the cell contains "WAL-MART" than put the word "Food" in cell G of the same row and contunie until end
View 1 Replies
View Related
Feb 25, 2009
Find part of string and delete. I have the following type of string:
View 4 Replies
View Related
Nov 25, 2008
In A1 I have a date '01/01/2008' and in B1 I want it to return the year and month which I have acheived with =year(A1)&month(A1) which returns '20081'. This is great but I want the format to return '200801', not '20081'. Also if A1 is blank I want an empty cell in B1. I tried this with the following but have failed.. =IF(A1,"","")=YEAR(A1)&MONTH(A1)
View 4 Replies
View Related
Apr 20, 2012
I'm trying to 'find and replace' part of a filepath which is buried in hundreds of formula, but when I hit 'Replace All' a file navigation window appears. Hitting cancel simply bring up another window, and again and again, each time a replace is executed.
View 3 Replies
View Related
Mar 9, 2009
I have a one year sheet for a customer. Column A has a 6 digit part number, Column B has the quantity of that part number and Column C has the date they of the invoice. There could be several rows with the same part number (say, if they purchase them monthly, or weekly they would have 12 or 52 rows, etc.). If I want to find out how many of part # 123456 have been sold all year, is there a formula I could use in Column D?
View 8 Replies
View Related
Oct 28, 2006
I have ComboBox on a UserForm that is looking to a long list on a worksheet. A lot of the entries in this list start with a brand name instead of a more discriptive name. I really need to be able to find an entry with any key word (not just the first word) in this list.
View 7 Replies
View Related
Feb 22, 2008
see attached spreadsheet. In sheet 1 I would like to look up each word in column D, seeing if they are in column B at all. Note if the word "Jill" is in D and "jilly" in a surname in B I would like it to get picked up. I have highlighted manually those that would get picked up. Those that do get picked up I would like to be copied into column C as per sheet 2 (this is what I would like it to end up like). There is a very long winded way of doing this using a find function and 1 column per word but as the actual sheet i'm using has thousands of different words this isn't really viable!
View 8 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Feb 19, 2010
My question is how many parts havinig end with C?Also arrange these parts in ascending order in a new column. Refer to the attached file and expected result also.
View 5 Replies
View Related
