I have built a capacity planner which is broken down in days.
In row 1 i have tagged the column with a week number indicator i.e. 5 columns (=5 days) are tagged "Week 1", next 5 tagged "week 2", etc.
I am trying to create a weekly view of this and need to sum the values of all cells on row 7 for a particular week i.e. I want to sum Cells R7, S7, T7, U7, V7 which represent the 5 working days in "week 1"
I am stuck with looping columns and rows.What i am trying to do is... I have to loop through columns E3 to G3 and also rows of each column and check for a condition inside the column.How can i achieve that? I have the following structure.. How can i code it?
For i=E3 to G3 'Column loop
for j=4 to 47 'Row loop
'code to check if the value isnumber
next j
next i
I have a spreadsheet with columns and columns of data. What I'd like to do is identify which rows have blank/no data in all specified columns (H, K, O, S, U, W, and Y).
To clarify, I need to identify each row where all of these columns are blank (as opposed to each row where at least 1 column is blank).
I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:
Month
2010
2011
2012
[Code]....
The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.
What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
4) repeat requirement 3 till no more Scenario's
I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.
With . Cells(Lrow, "B")
If Not IsError(.Value) Then
'****
If LCase(.Value) = LCase("D") Then .EntireRow.Delete
I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.
The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
Find button in Spreadsheet. My problem is I wish to only search 2 of the columns on my sheet.
I have been trying to locate info on my own, see here: [URL]
But I cannot seem to find anything showing how to specify where to search.
Here is the code I am using
[Code] ......
Want to search columns G and J -OR- Search entire sheet but ignore Columns A and B ...
i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
View 14 Replies View RelatedI'd like to be able to search a spreadsheet for a specific word or series of characters. Once all of the ROWS that contain the characters are found, I'd like to be able to have another spreadsheet with ONLY those rows (all columns).
View 8 Replies View Relatedremoving duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
View 1 Replies View RelatedI have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).
In other words, my individual data files look something like this:
Participant 1 (title of data file)
Trial/var1/var2/var3
1 55 43 65
2 54 43 56
3 33 23 56
and I want to compile all the data into a single file that looks like this:
Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ...
1 55 43 65 54 43
2 . . .
I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.
I am trying to track street pavement for a city and as you can imagine, there is a lot of data entry.
At the end of the day, I will need to know the total length of streets with brick, asphalt, and concrete. Column 'D' has the street material (Asphalt, Brick, Concrete) and Column 'E' will have the length. At the bottom of the sheet, under column 'E', I have cells that will show the total length for streets with Asphalt (E61), Brick (E63), and Concrete(E65), so I need a formula to enter in those cells that will look for the specific pavement materials it is totalling
Also, I am tracking streets with/without curb and gutter. If a street doesn't have it, I need to track how much will be needed. Basically, it will be the length (Column E) times 2 (both sides of the street). In column 'G' there will either be 'Yes' or 'No'. If 'Yes', then I don't need a total and the cell containg the amount of curb and gutter need (Column 'J') will be blank. However, if 'No', Column 'J' will have the total amount of curb and gutter needed ('E??' x 2)
Obviously the sheets will be different lengths so the cells will need to be copied and pasted.
I have a workbook that has protected cells however need the ability to specify which columns to allow editing and which not to allow. For example all data in column A can be edited however column B can't. Column C & D can and E can't and so forth.
View 4 Replies View RelatedI plucked a macro off an old post from here (as I always do), but I'm having trouble tweaking it.
VB:
Sub completedtest()
Dim c As Range
Dim rngG As Range
For Each c In Intersect(ActiveSheet.UsedRange, Columns("i"))
[Code] ....
As it currently stands, the macro selects the entire row of rows containing the letter Y in column I. All I need to change is, I want to simply select columns B through I, rather than the entire row.
I want a checkbox to filter column with specific value and hide others. And also there are two checkbox, one should be automaticly unchecked if we check another checkbox.
View 1 Replies View RelatedI am looking for a way to select specific columns in excel that have specific characteristics in the header titles. For example, I have a very large worksheet with columns that specify the year and month (ie. "1900-8"). The years range from 1900 to 2011 and each year has "-1" to "-12" to represent the months of the year. Specifically, I am wanting to pick out all of the columns ending in "-8" (August).
View 14 Replies View RelatedIn my workbook almost all of the column include a RED cell.I want to delete the cells BELOW the RED cell in a column.;And also want to migrate the whole column which has not RED cell in a to new sheet.Here is what I need:
delete-the rows below red.xlsx
How can I count the number of used columns in a specific row? The below code doesn't work.
View 14 Replies View RelatedI want to filter columns from an excel spreadsheet where the value in the cells contains/includes the String "Create".
View 3 Replies View RelatedI have two columns with numbers in ranging as follows:
2
2.5
3
3.5
4
4.5
5
On the spreadsheet it looks like this:
4.5
5
4
4.5
3.5
3.5
3.5
4
3.5
3.5
3
3
3.5
3.5
What I need to show in the next column is a True where these two figures add up to 8 or more. However, the minimum in each column needs to be 4 and 4 i.e. if one column has 3.5 and the next 5 then return a value of False.
I am trying to find the average of a cell with a name in that may appear several times in various columns. I then need to find the average of the value against that name in all the columns that it occurs in. Example:
a
b
c
d
e
f
g
h
i
j
k
Aldridge Sean
2
3
3
Andrew Rob
4
3
3
Aldridge Sean
3
2
2
[Code] .....
In the above example I want to find the average score for aldridge sean in column b, f and j.
I am trying to create the ability to search for a word in specific columns. In my case below I want to search D5:E500 but it's not working. It searches the whole ppage.
Private Sub searchfind_Click()
Dim searches As String
searches = searchfirstname & searchlastname
If WorksheetFunction.CountIf(Range("D5:E500"), searches) = 0 Then
Exit Sub
End If
Cells.Find(What:=searches, After:=ActiveCell, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub
I have requirement wherein, I have to put filter conditions on two separate columns in the same sheet & putting hard coded value eg. "XYZ" in third column for this selected range. I am using macro autofilter but its giving me errors for multiple filters.
View 9 Replies View RelatedI have a file that has various numbers of columns, some of which i need, some of which i do not. Under these columns, there are various rows, sometimes 50, sometimes 400. I need to write something that copies only the specific columns I need and pastes it into a new worksheet.
For example, in the data file there is are columns property type, loan balance current, and amortization type. I need them to be copied with the information in the rows below them to a new worksheet, to eliminate the unnecessary information in the data file.
I have a little problem - as you probably guessed.
I have a spreadsheet in which i need to find the value of the last used cell in a row.
e.g spread sheet uses columns "a" to "l" and rows "1" to "75".
Over time rows are filled in with text ("tom" "dick" "harry") from a to b to c, the most recent being to the right but the rows can move at different paces.
I want to count how many many times each value has come up most recently.
I have a macro that changes the color on a row, but depending on which cell I am in different columns arwe changing color. What I want is that if I make a change in row 1 want the color in A1:L1 to change and if i change row 2 i want A2:L2 to change, so alwaws the columns A to L in respective row. My macro
Sub Markera_Ny()
Dim intRadnr As Integer
Dim intStartrad As Integer
Dim intSlutrad As Integer
Dim Box As Integer
Dim Rad As Integer
intRadnr = ActiveCell.Row
intStartrad = ActiveSheet.Range("First").Row
intSlutrad = ActiveSheet.Range("Last").Row
If intRadnr < intStartrad Or intRadnr >= intSlutrad Then
MsgBox "Macro can only be executed in rows 21 to 1000"
Exit Sub
End If....................