Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range
Jun 3, 2009
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I Need to loop through col W Used range except the last row which is totals or the first 4 rows which is Heading stuff, and if value > 0 post it to a column on the right based on the day code. ( 31 values 1 for each day)
I built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.
What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount
Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But how to go about it.
I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.
I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer
For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count - i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken Cow Donkey Pig
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
I've had a look at some examples of extracting unique values but it seems quite complex to me!
I have a list of customer names in column A, column B contains a material code (number format), column C contains the material information sheet (text & number format), column D contains the date that the material information sheet was sent (all column headers are in row 1).
Columns E, F & G follow the same format as above but for a different material code - this is repeated further in Columns H, I & J.
All of the material codes are different, but some material information sheets can be the same for some materials.
In column K I need to identify the unique material information sheet values for each customer, as some are duplicated.
Is this possible? There may be some blank values in the row i.e. a customer does not receive a certain material.
Perhaps just an easier way of organising the spreadsheet!
I need some assistance with a simple macro. Im clueless when it comes to macros.
Heres what I have:
column "e" contains the year built for homes. Some of the fields contain "9999" or "0000" or blank. I need to delete these entire rows. If the year is between 1900 and 2020 I would consider it valid and keep the row.
So I need a macro that says something like this:
if column "e" is not between 1900 and 2020 delete the entire row.
I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
I have data of different accident year. Now I need to identify different Accident Year and use them in Macro to do certain things. For example in the file attached I am using them in Msgbox. Now to acheive that I first extracted unique values using advanced filter on Column C and later use the different value to POP up the Msgbox. (This is achieved). Now I want to skip this part of doing Advance filter to extract unique value. Is there a way I can know unique value of data through the code itself and use them in program. here is what I am doing currently: I have delted the most of the data cause I am concerned with coulumn containing acc_year only.
Sub test() Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= Range("C2" _ ), Unique:=True Range("C2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("C3").Select Do While ActiveCell.Value <> "" MsgBox ("The Year is " & ActiveCell.Value) a = ActiveCell.Row Range("C" & a + 1).Select Loop End Sub
I'm working with reports where I am given a list of used RFID tags which contain 13 alpha-numeric characters, and need to compare it against another list, in order to determine if any are matching. I'd like to be able to loop through one column of values to compare against the other, but unfortunately my VBA skills aren't that great..
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
I have 2 worksheets. The first sheet has data of which some needs to be copied to a second worksheet. The trigger is a value found in column E. If a match is found, then a copy statement needs to be built. The values in F, G & H are the values to be used in the copy statement. The content of cell H may be numbers or letters or both.
What is the correct format of the copy statement?
Dimension all variables Dim RowPointer As Long Dim wbContrib As Workbook Dim wbMaster As Workbook Dim SheetName As String Dim target_sheet As String Dim target_cell As String Dim Target_value As String Dim CellAddr ' ' Initialize variables Set RowPointer = 1 Set wbMaster = “Master.xls” ' '******************************
In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?
I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.
What i'm trying to do is i would like to compile in 1 column all duplicate values from multiple cells.
ex. A1 to 10 is numbered 1 to 10 respectively, B1 to B10 is numbered 6 to 15 respectively. which means in A1:B10 the duplicate values are 6,7,8,9,10. i could like these number to show automatically in C1 to C5.
I am finding it very difficult to extract the Project reference data for each employee, I have attached sample working file for reference.
I have a project names in Rows and Employee names in Columns and each employee fill the working efforts in multiple projects and i would like to extract which all project they have worked for each month. Since the data is very huge i am not able to progress fast in collecting the data
Column A lists an account code on the summary line of each invoice. I'd like to copy this account code upwards for each individual line of the invoice so that I can summarise each type of charge by account code in a pivot table.
I'm a bit stumped as to how to do it. If possible I don't want to have to modify the data itself in any way so that I can paste new data in the each day without altering it.
I've started on the right with a CountA function for each row, which I was intending as a means to identifying the blanks rows and discount them, but it also showed up that each line requiring an account code to be copied returns a 4 and each invoice summary line returns a 9. I'm just not sure how to reflect this in a function.