so I have been googling to figure out how to write a macro that will select every other column on my workbook. All that keeps coming up is conditional formatting, which I do not want. I am not trying to highlight the columns in alternating colors, I just want to select them.
I am starting at column F and I would like it to search until the end of the columns and select every other column ending with the last column with data in it.
I know I could search by a specific row and tell it to find the first blank cell then offset it, but there is no particular row that will always have data in it, so I guess it needs to check by column until it finds the first column without any data in all rows and then offset it.
Anyone know what I am talking about here?
I know it should be fairly simple but I couldn't seem to find any answers anywhere else.
Also, anyone know any good websites that have definitions for some basic macros? I'd love to start learning how to write this stuff on my own.
I am interested in selecting cells in alternating rows as follows:
12 Red Happy 00 Unique descriptor 13 Grn Sad 03 Unique descriptor 14 Yel Happy 02 Unique descriptor
I am interested in selecting all the "12, 13, etc", "Red, Grn, ..." and "Happy, Sad..." for analysis, but want to skip all the rows containing the 0- numbers and the descriptors for them. How do I quickly select only the alternating cells in a column within a selection?
I have a column that begins in the same place all of the time (Cell C6) however the length of this column can vary. How can I copy the column using VBA?
I'm looking for a way to count groups of alternating rows of "TRUE" values across 3 columns. There will never be an occurrence of more than one "TRUE" value per row. In the sample below, alternating TRUE values occur in rows 4 through 7, so this would be counted with the groups of 4.
I need to separate these lines into columns such that, for example, the first line would be in 6 columns: (Jackson, TN) (9623) (BCBS TN) (98) (UnitedHlthCare) (2)
The delimiter does not work because some of there is no one character that always separates the text and the numbers. I've tried going through in word and typing something like '%' where I want to separate, but with thousands of lines of data that is extremely tedious.
Is there any formula I can use that would be capable of solving this?
Range Macro - looking to create a simple macro to highlight a colum range from any location in column A that the cursor has landed on to the fixed location "A3". Example: If I am on A34, then the macro would highlight A34:A3. I can then edit the format in that range. Idealy, I could search on Column A first for a given text, then run the above macro to highlight every cell between that given text and "A3".
I am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns. My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data. with this range i tried hopelessly to use the average function (did not work):
Sub averagemake() Dim iLastColumn As Integer Dim Rng As Range Sheets("All Data(Values)").Select Range("A6").Select Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])" End Sub
I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
At the moment I've got a chart with 3 series lines on it and each line has 12 points. I need to select the 12th point on each series in the chart and create a label for it. The 3 lines are Budget, Forecast and Actual and often the Forecast line hits the same points as the Budget line in the chart and every time I try to find a way to select the last point on the Forecast line, I end up selecting the budget one because it's the one in the foreground.
I can select the forecast line with the keyboard arrows, but not the last point.
I also try to zoom in to get it but one of my charts has exactly the same forecast line as budget line and I still need the forecast line because the forecast will change in future.
I can delete the budget line then add it back, but this is difficult and one I'd have to repeat many times because I'm always making graphs and labelling the final point in a series is something I'm going to have to do more often.
How do I select the last point on the forecast line if it's directly behind the budget line?
I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.
which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).
Is there any way to set the list from a Combo Box to automatically start in the middle?
Whenever I select a Combo Box, the drop down list always automatically starts at the top of my selected range, ideally I would like it to start in the middle.
For example my Range that I am filling the Combo Box with lists dates: Today()-7 to Today()+7.
At the moment the first item in the combo box list is Today()-7, I would like it to be Today().
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
For i = 1 To modelcount Redim Preserve Models(0 To i) Models(i) = Cells(1, i + 7) Range1 = Cells(2, i + 7).Address(xlA1) lastRow = Cells(rows.Count, i + 7).End(xlUp).Row Range2 = Cells(lastRow, i + 7).Address(xlA1) Reference = Cells(2, i + 7).Address(xlA1) ThisWorkbook.Names.Add Name:=Models(i), _ RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes.
Option Explicit Sub PointName() Dim Ws As Worksheet Dim Rng As Range, Cel As Range
Set Ws = ActiveSheet Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp))
For Each Cel In Rng Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
Ws.Columns("A:B").Copy Sheets.Add With ActiveSheet .Paste .Name = Trim(Cel) .Range("A1").Select End With Next Ws.Activate End Sub
I turned it off for a while and when I turned it back on I am getting an error Unable to set the _Default property of the PivotItem class
Debugger is highlighting
Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
I have a column (k) of 65,000 rows with numbers that are either positive or negative digits. The next column (L) has classified them as either 1 for positive number or 2 for negative.
There may be a run of all positive or all negative numbers or 2 positives and 3 negatives or any combination of positive and negative numbers reading down the column.
Reading down the column (from L1), I need to find any sequence of alternating 1 positive, followed by 1 negative or vice versa.
I am looking for evidence to find any sequence that has more than 2 alternations. (Remembering that positive is 1 and neg is 2)
That is, two or more alternations of a positive followed by a negative or neg followed by a positive.
I'm writing a macro that’s taking user input (from a form I developed) and will generate a chart according to the input.
One requirement for this chart is that the background alternates fill colors between gray and white. Every four columns need to be either gray or white. Based on the duration the user entered, the width of this chart is created. The length (the number of rows) is constant.
I've tried different loops (for, if, do - you name it I tried) This loop is just out of my league. Any and all help would be greatly appreciated.
Summary of Facts: - Starting Point of Chart is B2 (the top left corner of the chart will be in B2) - the number of columns is dynamic (based on user input) - The rows are constant are (2 through 16) - Every four columns need the fill color to be either gray or white
I'm sure there's a simple way to do this (I've been trying countif/match formulas but I'm not quite there)! I need to group line items with the same dollar value, WITHIN say 1 cent. If I do a pivot it will group exact matches, but I'm looking for the flexibility to group something within a few cents. So instead of pivots, I was trying to think of a formula that would do what Column A is doing below:
*I manually added Column A, looking for a formula that would group dollar values within 1 cent, assuming I've sorted Column B from smallest to largest:
A word text blablabla B word text blablabla A dog cat france B dog cat france B this that other A this that other A tiger dragon monkey B tiger dragon monkey
My source data I can't get to sort alternating on columns A and B, and I would like the data to alternate A-B-A-B-A-B while retaining the pairings of data in the adjacent fields. Is this possible?
I have a spreadsheet that contains thousands of rows and I need to highlight all the duplicates in Column B.
What I need though is one group o duplicates to be colored one color and then the next group of duplicates be colored a different color. I need just a 2 color banding.
My need for 2 colors is that the cells contain 9 digit numbers and there can be duplicate groups right next to each other and for the eye to distinguish a different grouping of cells I need that 2 color banding.
I have the below code that colors everything in one color. Can I be modified or a completely new code to do 2 colors?
Code: Sub Dups() Dim Rng As Range Dim CL As Range
Set Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
For Each CL In Rng If WorksheetFunction.CountIf(Rng, CL.Value) > 1 Then CL.Interior.ColorIndex = 6 End If Next CL End Sub
My spreadsheet has rows with inspection numbers in column A, but there are varying numbers of rows associated with an inspection number. I'd like to change the color of the rows for every other inspection number to yellow using a formula. I think it has to involve an if/then statement but I can't figure out how to word the formula to just choose every other group of inspection numbers.
Is there a way to shade alternating rows in a range and have it always be alternating no matter if a row is added in the middle of the range. If I do it manually now and add a row it messes up the shading and fixing it manually is very time consuming.
In column H I have dates from 1/1/2008 to 12/31/2013. What I would like to do is transfer that data to column J, but alternate it every other row and then insert the word Date into the blank cells. For instance J1 would read "Date" and then J2 would read 1/1/2008 J3 would be Date and J4 would be 1/2/2008. I have a large database and I tried to use array formulas and they slow it down to a crawl so I was working with DSUM, but I don't feel like wasting time with adding in Date manually.
how to add alternating cells when all are numbers, e.g. B8 to KW8 are all numbers and I want to add B8, D8, F8, etc. until KV8 and then separately add C8, E8, G8, etc until KW8. Is there a more efficient way to do this than =sum(b8,D8, etc)? I also need to do the same with =counta(b8, d8, etc).
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.
