what I need to know is how to trap the in the event it does not find any in my case the column will only contain 1's or 0's. If there are no 0's then it should skip the line: Rows(SR1 & ":" & tr1).Delete Shift:=xlUp and continue with the line after that (which happens to be return to the macro that start the marcor this code is in)
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
I have a rather frustrating issue that dates a few months back, so I hope to solve it today. The following code tests a value in Column A. If the value is between 1-5 or equal to 11, column B will return the string A. It works.
I've been through the forum and I've got my VBA to check that columns 37, 38 and 40 are entered if column 2 = 'O'. However I just can't get it to exclude column 39 from the check! Is there a way of getting Case Else to do this or should I use an Array?
Dim rCell As Range Dim strMessage As String Dim RowCounter As Integer Dim ColumnsChecked As Integer
For Each rCell In Range("B14:B5000") RowCounter = rCell.Row If rCell = "O" Then For ColumnsChecked = 37 To 40 If Cells(RowCounter, ColumnsChecked).value = "" Then Select Case ColumnsChecked Case 37 strMessage = "COMMENTS" Case 38 strMessage = "INVOICE NUMBER" Case 40............................
I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Dim Cell As Variant For Each Cell In Range("A1:OL1") Select Case Cell.Value Case "Eng1" Cell.Offset(1, 0).Value = "Engine One" tons more in the middle here Case Else Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
I need a macro or excel formula so that it will remove duplicate products- case sensitive(removing all duplicate prod using using excel,that i know.) i.e. it should not consider Blue BLUE as duplicate. only consider Blue Blue as duplicate.
So the macro should work same as data -> remove duplicate function already exists in excel, except it will be case sensitive..
I need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues.
I want to use a Select Case statement so I can set all other found values as blanks.
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
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.
For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....
I'm trying to compare two range values within a macro to see if they match...if they do/don't I capture and write some other data.
One list is lowercase, while the other list is UPPERCASE. My current macro needs them to be in the same case because I'm using the following to compare: If VPNID.Value = BuildHRName Then
How can I change the format of one of the lists to UPPERCASE or lowercase so that I am comparing apples to apples?
if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change: "This Is Strange" to "tHIS iS sTRANGE" "THIS IS STRANGE" to "this is strange" "this is strange" to "THIS IS STRANGE"
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..............
Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range Columns AJ through AX Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
I am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:
This works, UNLESS "My Column Header" is the last column, or second to last column, then it jumps left two columns, instead of landing on the correct column.
The purpose of this script is to select a cell directly in that column that I was searching for.
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
I have this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between 1st and 2nd call Time between 2nd and 3rd call Time between 3rd and 4th call 0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
I want to find the minimum value of column A if there is anything greater than zero in column B. I tried this formula (simplified): MINIF(B2:B6,">"&0,A2:A6) And it gives me the # NAME? error
