Find All Occurences Of Text In Column & Offset To Add Data
Aug 22, 2006
I have been unable to get this code quite right.
I want to be able to search all of column A for the string "UNAUTH O/D FEE £20.00" and when it is found, add £20 to column B, and 01/01/00 into column D of the same row.
Sub feedate()
Set rd = Sheets("fees")
z = 20
x = 1 / 1 / 6
For i = 1 To rd. Range("A65536").End(xlUp).Row
If UCase(Cells(i, 1)) = "UNAUTH O/D FEE £20.00" Then
Cells(i, 2).Value = z
Cells(i, 3).Value = x
End If
Next i
End Sub
I need to make a list of items that occur above the string "Room" and the data offset to the bottom and bottom right.
Here is the sample data: 1, 100A 2, Room, Rh 3, 123, 11 3, 200B 4, Room, Rh 5, 456, 24 6, 300C 7, Room, Rh 8, 789, 56 ...
On another sheet this is what the output should look like: 1 100A 123 11 2 200B 456 24 3 300C 789 56 ...
Here is a copied function that I've been trying to work with. "ROOM_AREAS" is the range in column A. I just can't seem to figure out how the ROW and SMALL functions are supposed to work here.
I'm writing some code where I need to know the column in which a specifc text string occurs. Because of the nature of the sheet, the string will only appear in one column, but that could be in almost any column on the worksheet.
For example say the text string is "Year 1", I need to find the column with this string, and then offset down to a predetermined row, or offset over to the appropriate column (something like below).
With Selection .Offset(0, x).Select If List.Selected(0) = True Then y = i - 1 With Selection .Value = Salary * Inflation1 ^ y End With Else
I am in the process of writing a VBA code(I should I am fairly new to this code writing!!!).What I need to be able to do is below.(enclosed please find a output in an xls file) What I should be able to do is below:
1.Find the text "Cash(No Listing)(Monthly)"
2. Offset to the 13th Column after that
3.Select the value in this column ,copy the value
4.Go to a row above and move one column to the left i.e.offset (-1,-1) and move 12 column backwards i.e.offset(0,-12)
5.Copy the value in 3 above to this entire range
6.Repeat this process for the entire file
My code is as below.Let me know where am I making a mistake. Just to let you know that this code performs the job well for the first entry i.e in the yellow makde area and doesn't do the job for any further entries .Llooks like I have some problem with the loop but not sure as to whats happening!
I am attempting to do is have a macro (Via Command button) go through column A to find the word "TOTAL". Once that is found I want it to offset by 3 rows (from the word "TOTAL") to grab the number and place it into column B.
i tried using the lookup but it gave a different result. i want to search a value from sheet1 A to sheet2 A and copy the remarks from sheet2 B and paste it to sheet 1 B and if not found leave it blank.
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities 0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities 0014B22<@44>Soil Properties and Qualities 0014B22<@44>Soil Properties and Qualities 0014B23<@28>Coursey 0014B23<@28>Ogles 0014B23<@28>Shelocta 0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches) 0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches) 0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr) 0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches) 0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches 0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.
I'm very new to excel macros and the parts I think I've put together are:
I have and interesting delimma and I have been spending way too much time trying to figure this out.
I have a value in column B "Agreement". I have data in column C which could be just about anything. I need to be able to find "Agreement" copy the value immediately above it and paste it next to the value in column C.
You see this gets difficult for me, because there are many spaces in the spreadsheet and there may or may not be a value in C.
Here is an Example: I need for the value above "Agreement" to bel placed in the same column where there is a value in C? Clear as mud. I need this for every occurence of "Agreement" in this column. There are many occurences of "Agreement"
On the worksheet, I need to find wherever the cell value begins '2 X 4 ...' in column L, and then replace the cell value in the same row, in column K, with '113010'. In my attempts, my code just changes the first occurrence, not all occurrences.
I want to use VBA to do this.
Here's my code so far:
Sub Update_Product_IDs()
Application. ScreenUpdating = False Dim i As Integer, iCount As Integer Dim numberOfPMDs As Integer Dim PMD_Name As Integer Dim worksheetName As String Dim worksheets_in_file As Integer worksheets_in_file = Worksheets.Count worksheetName = Worksheets(2).Name numberOfPMDs = worksheets_in_file - 1
how to write a vb code to do the following actions with an excel sheet, which contains a dump from SAP. This dump usually consists of anywhere between 25 to 30 columns and about 20000 to 30000 rows…
1. Find all rows which contain “VN” and place it into the sheet Vendor Charges (which already exists). Which could be in any column
2.Find all rows which contain “MT” and place it into the sheet Material Charges (which already exists).
I have attached the excel sheet to better explain what I had in mind. ‘sheet1’ contains the raw data from SAP but, the number of columns varies every time so it cannot be directly sorted by selecting the column. I tried recording a macro but, as I don’t have a constant sheet to work with each time the macro obviously doesn’t work.
how to write a search and replace macro, but instead of writing:
Cells.Replace "OldText", "NewText"
how to independently read this "search" text into a string (hopefully a string that can handle 'more' than 255 characters per cell, even in Excel 2000) then replacing that search text("OldText") with ("NewText") step-by-step?
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria) Search range is: B1:D400 (for example) Cell B40 = samuel C40 = Driver D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1() Range("B1:D400").Select Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub
I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2) Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
Column A - has data but there are data which occur more than once then it will be counted to column C
Reason is I need to delete the data with several occurence at column A so when I see the count at column C then I can be aware how many times the data at Column A occurs so I can delete it by filtering the data.
I am trying to get this code to loop through a dynamic range on one sheet and copy all occurences of a name (John Doe) to be copied into another sheet in a single column, but a new row for each occurence of the name.
I believe the problem is in looping the findnext statement,
Sub find_actor()
' Keyboard Shortcut: Ctrl+Shift+A
Application. ScreenUpdating = False
Dim random As Range Dim connector As Range Dim paste_connector As Range
is there a formula or a way to return a unique entry in a column if a value is present in another cell? refer attachment; if their is a value in column C (site A) return the entry in column B but only if it is unique, ie. return supplier 'DEF' only once irrespective of the amount of times the value appears under the site heading.
how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!
the macro will look at the value I entered in cell A1, then find all instances of it in Column A in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)
I can't seem to get the findnext to work. I can find the first instance but none after that. Here's my code.
Dim B As Range Dim SheetNumb As Integer SheetNumb = 1 Do While SheetNumb <= Sheets.Count Sheets(SheetNumb).Select If ActiveSheet.Name = "Page1" Then Exit Do End If Set B = Sheets(SheetNumb).Cells.Find(" Mechanical", SearchOrder:=xlByColumns, LookIn:=xlValues) If Not B Is Nothing Then B.Activate FindAddress = B.Address Do B = Sheets(SheetNumb).Cells.FindNext(B) Loop While Not B Is Nothing And B.Address <> FindAddress End If SheetNumb = SheetNumb + 1 Loop
I want to search for a word in column A and when I find it I want to copy it to column B. Column A is a description that can be 6 or 7 words long. Column B is a single word.
Example:
Col A Engine Kit, V-8, 306, forged. I need to copy the word Kit to column B.
I have a macro that copies data from one sheet to another. I would like getting the macro to figure last row used in column B from B3 on and put the word Sum below it. For example, if last row used is B64, on B65 would like the word sum on it.
I cannot just input in as the number of rows could change when macro is run. Data is dynamic.
Here is simple code I have:
Sub Weight() Sheets("Final Month").Select Range("A2:B1055").Select Selection.Copy Sheets("Final Weight").Select
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.
Example
Find all occurences of And or AND and replace with and Find all occurences of With or WITH and replace with with. Find all occurences of Or or OR and replace with or
