Macro To Find Last Row In Column B And Text To Next Column
Sep 3, 2013
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
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 trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X” Cells.Select Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date Column D = loan# A Column E = loan# B Column F = status Column H = followup needed (Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows: Column G&H = Merged cells where due date will need to go Column I&J = Merged cells where loan# A will need to go Column K&L = Merged cells where loan# B will need to go Column M thru S = Merged cells where followup needed will need to go
I have the file here i work with, basically the first column is a legend and the column to the right of it is a pointer column to help me find out where a legend is located in another file. So i was wondering if a macro could be made to basically find where the "legend column" A, C, E ect ends ( every other column is a legend column , one next to it is a pointer column ). and then combine the ends all of the columns contents and put them into 1 column.
In the file with this question i have showed you what i start off with, i highlighed in yellow where each column legend ends, ( normally these are not highlighted and i find them manually ). In the 2nd tab i show what the end result should be. All the columns are now consolidated into 1 column. 1 after the other.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test() Dim i As Long LR = Cells(Rows.Count, "B").End(xlUp).Row For i = LR To 2 Step -1 If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete Next i End Sub After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive. Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
I have been working with a few people on here to setup a macro to copy text from a column of cells to another column and then print this in to a text document but it seams to have got stuck in a loop ....
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.
A B C D E 1 X X X X 2 X X X X 3 X X X X X 4 X X X X 5 X X X X
This macro is extreemly basic but i have no skills in excel with macros. Basically i have 'Column A' with stats that i need input into 'Column B', however i need then to be input inside a field that is already there.....
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.
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
I have a spreadsheet and need column S to look in column J to find "AO" And "AI" once it finds this I need it to look at column P and post that data into column S. I have tried all sorts of IF's but I cannot seem to get it to work.
I have some xls files. I need to find the sum of a specific column. the column heading is always TOTAL but the column it resides in changes column to column. so one file may have the text total in column H and others will have the text total in column X. how can i make a macro so it finds the text 'total' and then sums up all the values in that column underneath it and displays it at the very bottom of that column?
I'm evaluating the effectiveness of a keyword list we use to identify certain, high priorit,y medical situations from a string of text.
So, I have a column on a sheet of data (EIS Report!"B:B") in this case, and a seperate column on another sheet with keywords (Keywords!"A2:A47").
I use the formula ={OR(NOT(ISERROR(FIND(keywords!$A$2:$A$47,$B2))))} to identify if any of the keywords exist in the string. This works well but I would like to improve my work in two ways:
1 - Identfiy, in another column on the EIS report sheet, which keyword it is that's been found within the string, 2 - On the keyword sheet, add another column of words, which if found within the string, would act as an exclusion.For example, I might use "STAB" as a keyword to look for "Male stabbed" etc, however might want to exclude "STABBING" to stop the solution triggering on "STABBING PAINS IN ABDO"
I need to create a formula that searches column E for text, let's say "BANANA", and when it finds that text, the formula returns the numerical value of the cell in that same row in column B. Let me know how this can be done.
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button. Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
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
Set rFound = Sheets("ProductCount").Rows(14). Find(What:=sProduct, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext)
ProdRow = rFound.Column
where sProduct contains values such as SW , CO. Now, when I use rFound to query SW, it finde me this first occuring SW. I have this inside a for loop. When it goes and queries again, it should get me the next occuring SW, not the first SW that it searched for me.
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..............
Essentially, I have 2 Sheets: "SheetWSS" = data to be copied "SheetWSD" = destination of copied data.
Below is a sample of the data in "SheetWSS" ----------A------------------------B-------------------------C 1------Trade ID -------Description---------System no. 2--------579----------------Loan ---------------------- 7 3--------580----------------Deposit---------------------22 4--------702----------------Deposit--------------------- 11 5--------703----------------Loan ----------------------- 58 6--------732 ---------------Loan------------------------66 7--------733----------------Deposit-------------------- 99 etc...(no more than 10000)
Now, an explanation of the data:
1) I work for a small bank (CORP) that takes deposits and gives loan.
2) CORP books these trades using only system no. 7 and 11; other system nos. belong to customers.
3) When a single trade is booked, the 2 sides of the transaction is recorded (by Trade IDs) . E.g rows 2 and 3 relates to one trade. So if CORP loans money with system no.7 to CUSTB, who uses system no.22, it shows for CORP a loan and for CUSTB, a deposit.
4) Everyday, the data in "SheetWSS" is updated with a different number of rows from previous, but the number of columns remain the same.
So, here's what i'ld like the macro to do: 1) Go into Column C, find system no.7 and 11. 2) For each 7 and 11 found, Copy the next row . So if for e.g, system no.11 was found in C4, i want the ENTIRE ROW 5 to be copied. 3) Paste the entire next row in "SheetWSD" until we have a list of all opposing sides of the same transactions initiated by CORP.
In the attached sheet I need to check on the Destination in column B, and if it matches set criteria, divide the figure in Column E by 2. eg If LAX is the destination, then for any rows directly under LAX until the next destination appears, column E needs to equal column D divided by 2. If the Destination is not LAX, then Column E equals Column D.
the difficulty I am having is that there can be a variety of row numbers for each destination. I am having trouble creating a formula that can basically work backwards from Row-1 in column B until it gets to a text string, and check that text string against the criteria. so it may be row -1 up to about row -9 dependant on the number of flight times available to each destination throughout the week.
The above basically checks for a number, and if fails (there could be #N/A, #DIV/0!, text, or Number-stored-as-text in any cell here), it checks for text. (the 65000 is arbitrary; we will never have more than a few thousand rows)
However this is not reliable--the main problem seems to be number-stored-as-text. And it's not possible for me to do the Paste-Special Multiply *1 trick to convert the numbers because this data is dynamically generated from a database--the user would have to do that every time the sheet opens.
Is there not some formula that simply says: "Give me the last non-blank cell after row 4 in column E, regardless of what type of data"? (Excel 2007)
I'm trying to find a function, or conditional format, that will allow to find certain text in a column and then replace all the similarities from another cell.
So basically, I have 3 Columns.
Redundant Text Replacement Text What The Text Currently Reads
Red Hats Save On Red Hats Online Black & Red Hats
Blue Jeans Find Blue Men's Jeans Blue Jeans On Sale
1) There is text that is recurring in the the thousands of the cells of Column 3. 2) I have used a program that found the recurring data, and put them in Column 1 3) Column 2 is what I want ever cell in Column 3 to change to when it matches the cell in Column 1 4) Is there a function that will find text in the 3rd Column that matches text in the 1st column (multiple cells in the 3rd column will match one cell in the 1st column) 5) Then Replace the text in the 3rd Column with the corresponding text in the 2nd column
I've been racking my brains trying to find a way of doing this. I have a list (column A in Excel) of over 50,000 organisations and I'd like to know what the most common words used in the names are. Ideally it would great if I could produce a top 10 list of the most common words at the top e.g. Ltd, School or Church with a count in the next column of how times that word it appears
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 am trying to move through the worksheets and delete all columns with "Accession" in contained in them. I have to do this with an external macro as the spreadsheet with the data is created from another program. I tried this and get an 'Object or With block variable not set' error and the debug highlights the Cells. Find line of the code.
Also, the number of columns could be variable within the spreadsheet as it is compiled by the other program.
Sub DelAccessionNum() Dim Wrkst As Worksheet For Each Wrkst In ActiveWorkbook.Worksheets Cells.Find(What:="Accession", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _ , SearchFormat:=False).Activate On Error Goto Completed: Selection.EntireColumn.Delete Shift:=xlToLeft Completed: Next End Sub
I would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:
VB: 'this line will be modified so that i cycle through the non blank cells that i have found For row_cycle = 1 To 7 'not signifcant If Abs(ActiveCell) < 1.96 Then Selection.Interior.Color = 255
[Code] .....
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.
