Writing/modifying An Macro
Oct 18, 2007
Every month I download a report from a state agency that I need to reformat, create charts from and send reminder emails based on some of the data. The report is downloaded as an EXCEL workbook containing a single worksheet. The worksheet is preformatted as a printed report. I have no control of this formatting and I can not get the data as a .csv file. The report contains 3 sections. See detailed report description below.
What I want to do:
1.Is copy the 3rd section of the report to a new worksheet.
2.Sort the new worksheet by name, and date.
3.Filter some names based on resource type.
4.Create a new file, which will be attached to an email to my boss for follow-up.
Originally I used the built-in EXCEL macro recording function and this worked for a couple of months. Then the number of rows in the 2nd section varied and my generated worksheet failed to sort correctly, and I lost the column headings so my filter no longer worked. The results were not something I could forward. I do not know how to modify the macro to search for the literals which end section 2 or start section 3 so I can copy the correct data to the new worksheet. I am not a VBA programmer, just an old-fart trying to do some administrative volunteer support for an important program, and really do not want to become one.
Detail Report Description:
Section 1: Headings –contain fixed headings in merged & centered cells.
Row 1 – Report heading 1 in merged cells A1–I1
Row 2 – Report heading 2 in merged cells A2-I2
Row 3 – Report heading 3 in merged cells A3–I3
Row 4 – Blank row............
View 9 Replies
ADVERTISEMENT
Aug 11, 2014
Macro (see below).
I have two sheets 1) ap modified 2) gl download
It's merging the two sheets together. however, it's pasting over the last row of data. For example, gl download has data from row 4 to row 100. The Macro is pasting data from ap modified starting in row 100 of gl download instead of row 101. How can i correct this?
Sub C_Merge()
Dim w1 As Worksheet, w2 As Worksheet
Set w1 = Sheets("ap modified")
Set w2 = Sheets("gl download")
Dim lr1 As Long, lr2 As Long
[Code] .........
View 3 Replies
View Related
Jan 18, 2009
I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set.
Dim Arrayone(0 To 9) As Integer
Dim Arraytwo(0 To 9) As Integer
Dim Arraythree(0 To 9) As Integer
Dim Arrayfour(0 To 9) As Integer
Dim Arrayfive(0 To 9) As Integer
Dim Arraysix(0 To 9) As Integer
Dim Arrayseven(0 To 9) As Integer
Dim Arrayeight(0 To 9) As Integer
Dim Arraynine(0 To 9) As Integer
Dim Arrayzero(0 To 9) As Integer
Dim Arraytwotest(10 To 99) As Integer
Dim x, I
Dim Row As Long, Col As Long, Step As Long, Colcells
Dim Digits As Long, Total As Long
View 9 Replies
View Related
Jun 2, 2008
i have three worksheets: "List 1", "List 2" and "New ist".I want to merge the first two sheets into third.
List 1:
View 14 Replies
View Related
Jan 27, 2014
I am trying to make a find and replace macro for multiple items.
Please see the attached file for further explanation : macro.xls‎
View 3 Replies
View Related
May 14, 2014
Basically, what I'm dealing with is an inventory system. When a cell value reaches below a certain quantity, we have a cell light up with the word "YES" (under the "re-order" column). What I'd like to do is have a macro that checks to see if that cell is populated with "YES" and if it is, to run another macro (which I already have written) that sends out an email notifying us of the need to re-order.
How it would work: The person pulling out the inventory would fill out this form and click "Submit"
submitbutton.jpg
I would then have a "call" code tied to the "Submit" button that when clicked, would run the macro to check cell population and send out the email. The email code is already written and works flawlessly on it's own. I just cant seem to figure out how to write a macro that checks for cell value and then runs it (or not) based on that.
View 14 Replies
View Related
Jul 3, 2007
i have an report in which i need to delete the entire row if the column contains values more than 250K, but the chanllenge here is; if any of the cells in the column contains a formula then the macro should not delete the row.....
View 9 Replies
View Related
Nov 5, 2007
I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).
If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.
Should we write the macro or can it be accomplished by any Excel Function?
View 11 Replies
View Related
Oct 16, 2009
I'm trying to use code to write the numbers 1 to 100 in 10 rows of 10
1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16......................
So far i have the following which works:
View 4 Replies
View Related
Dec 10, 2008
I am trying to write an if statement in my macro. It is not returning what I want. Can anyone tell me where I have gone wrong.
Dim D As Variant
D = Sheets("Instructions").Range("n3").Value
If D = 1 Then
'range("n5).value
Else
'range("b3").value
End If
View 9 Replies
View Related
Sep 23, 2008
I am now trying to hide rows when the data in a column matches that of a cell with a data validation list. Ideally i would like only the rows with matching data to show. When the cell is changed then the visible rows change and when the the entry "total" is selected all the rows are shown. There are several small tables of data on a page so I can't use auto filter. The data areas will also change in size as new entries are added. So the last row in the data tables will increase over time. I will copy and apply the macro to each separate table of data.
As an example cell c19 contains "lwsf"
The data table is in the range c24:q47. the column with matching data is column D so all rows of entries with "lwsf" in column D should be visible and the rest hidden. When c19 is changed to "TF" then the rows visible become those with TF in column D and the rest hidden.
View 14 Replies
View Related
Nov 18, 2008
Just wanted to know if I could have a range or ranges which are split. The current range i have is N5:AV6. I need to miss out every 6th cell so am looking to do a number of ranges like this - N5:R6, T5:X6, Z5:AD6 etc etc. If this can be done could you show me how I would need to lay it out?
View 2 Replies
View Related
Dec 11, 2008
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
View 8 Replies
View Related
Mar 17, 2014
As part of a research project i need to extract as much data as possible from a webpage. The problem is to access each table i have to follow lots of links which I can't get to work automatically.
Its from a greyhound-data.com. So an example would be I want to extract all the racing stats for every dog that raced in swindon between 1st jan 2010-1st feb 2010. When i put it in the search engine I get 512 races in a table. I have to follow the link on name of race for each race.. Then I have to follow the link for each dog in the racers box, 6 for each race. Then when i'm on the dogs page theres a link saying on the top of the page saying a nember of races eg. 67races (number will vary for each dog). If I press that I get a list of all the racing stats for that dog. Its that table i need. But i also need the dogs name as a constant column. I need the table for each of the 6 dogs for each race out of the 512.. So in total 3072 tables.. It a lot of data and I have no idea how to get it?!
My biggest problem is I don't know how to say follow the various links. And I don't know how to loop multiple times - once for each of the races in the original list and then for the 6 dogs.
View 4 Replies
View Related
Feb 12, 2014
I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.
Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.
know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?
View 1 Replies
View Related
May 13, 2014
I have attached a screencap of an example source table that I am trying to write a macro to manipulate each day. Starting conditions are that I have data in column A and B. Column A would be reference numbers that may be on one line, or more likely, on multiple lines repeating with varying counts. Column B is the weight associated with each line. I would need a macro that could loop through column A, determine if the ref number has changed, and then calculate/populate column C. Column C is the % of the total weight for the unique reference number. I have illustrated this in the table using column D. This is taking me incredible manual effort to complete right now and I do know VBA, I just don't know how to loop a variable range in this case.
View 9 Replies
View Related
May 8, 2014
I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.
So leaving it general, Word.doc and Excel.xlsx
It's all because my company's POS system will only export to Word but everything we do is in XL.
View 6 Replies
View Related
Dec 3, 2008
I have a series of hyperlinks that take me to named ranges. The named ranges are on different worksheets. Once there, there is no way to get back to the worksheet that hyperlinked me over.
Is there a macro that I can attach to a button that will take me back to the last worksheet? Does Excel record a history? I will hyperlinked from multiple worksheets so it has to link back to the last last worksheet opened prior to hyperlinking.
View 9 Replies
View Related
Feb 16, 2009
This code works great for me, but I need some help modifying it. This code searches for data in the whole sheet, two things I would like to do is limit the search range to one column and then to find the whole string, not partials, for example, find 331 without finding all the 33's or 31's.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
If Target.Address = "$A$1" Then
Set MyRange = Cells.Find(what:=Target.Value, After:=Target)
If Not MyRange Is Nothing Then MyRange.Select
End If
End Sub
View 9 Replies
View Related
Nov 6, 2008
I am copying some data from one workbook and pasting the data into another workbook depending on certain criteria (using autofilter) using excel VBA. For only One column i need to multiply data by 100 (i.e. convert it into percentage) For example column A in source worksheet has all values and i will paste it in column D of target worksheet. But before pasting i need to multiply the data by 100 so column D in target worksheet has values in %.
View 3 Replies
View Related
Jan 24, 2012
I need a way to take the below example and combine each entry into 1 line.
ALRSItem Number/QuantityStockingDescription On HandUnit Actual Cost170PC1832PCB1un43.75circuit de PC1832 nouvelle version 5010 (P832)Avg. Unit Value:43.750000Costing Method: Moving AverageCosting Unit:un250282un2.05Braquette pour contact anti-vandale 112 ADEMCOAvg. Unit Value:1.025000Costing Method: Moving AverageCosting Unit:un
I would like to take each part and place the specific part information provided into 1 line for each part number.
View 1 Replies
View Related
Aug 8, 2007
It's refreshing to know that to make VBA even more lightning fast it doesn't check the For limiting variable. /sarcasm mode off
Does the following surprise anyone? Surprises me. (And yes, I know what an Exit For is. This code is designed solely to illustrate the point.)
Sub JimmyLoopVar()
Dim i As Long, K As Long
K = 6
For i = 0 To K
If (i > 3) Then K = 2
Debug.Print i; ; K
Next
End Sub
So the loop runs 7 times regardless of K! (At least on XL03 )
View 9 Replies
View Related
May 24, 2008
I am trying to modify the below code in order for it to:
1) Check the cells in Column A to find any blank cells and zero values. If the macro finds a blank cell or a zero then delete the entire row.
2) Check the cells in Column D for any blank cells and "N/A" (this N/A is text, it is not an excel error). If the macro finds a blank cell or "N/A" then delete the entire row.
Here is the old code
Sub DL()
Dim lastrow As Long, i As Long
With ActiveSheet
On Error Resume Next
.Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("C" & i).Value = 0 And .Range("D" & i).Value = 0 Then .Rows(i).Delete
If .Range("C" & i).Value = "N/A" Then .Rows(i).Delete
Next i
End With
End Sub
Here is the modified code...
Sub DL()
Dim lastrow As Long, i As Long
With ActiveSheet
On Error Resume Next
.Columns(3).SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
lastrow = .Cells(Rows.Count, 3).End(xlUp).Row
For i = lastrow To 1 Step -1
If .Range("A" & i).Value = 0 And .Range("A" & i).Value = "" Then .Rows(i).Delete
If .Range("D" & i).Value = "" and .Rang("D" & i).Value ="N/A" Then .Rows(i).Delete
Next i
End With
End Sub
1) Is this the correct and efficient way to accomplish the task?
2) How to i change this in order to select a specific sheet?
View 9 Replies
View Related
Jun 28, 2006
Basically my code works fine if I enter a number into the reference cell. But for my actual application, I need it to update the reference cell's background color when I enter other data. B21 has a =IF(COUNT(B18, B19, B20) < 3,"",B18 * B19 * B20) statement. So as I modify B18, B19, B20 it should update B21's background color. Here is the VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B21")) Is Nothing Then
If Target < Worksheets("OEE Data").Range("K6").Value Then
Target.Interior.Color = vbRed
ElseIf Target >= Worksheets("OEE Data").Range("K6") And Target < Worksheets("OEE Data").Range("L6") Then
Target.Interior.Color = vbYellow
ElseIf Target >= Worksheets("OEE Data").Range("L6") Then
Target.Interior.Color = vbGreen
Else
Target.Interior.Color = vbWhite
End If
End If
End Sub
Obviously my VBA isn't sound and hence won't apply any background to B21. If I just change it to AA1 and enter the criteria into AA1, it's background changes without problem. The reason I need this in VBA is because in the cFormatting I need the first condition to be 'is equal' ="" (white background) or it defaults my BLANK CELL to being > 1 (Green background). This is why I need 4 conditional statements.
View 2 Replies
View Related
Jul 19, 2006
I have imported text from a database that has names shown as "last name, first name" in a single cell and am trying to figure out if it is possible to modify those names to "first name last name." I would also like to see if I could just have the first name transferred to a different cell.
View 3 Replies
View Related
Jun 2, 2014
Below is a formula that I am attempting to modify:
From this:
=IFERROR(IF(A8<MIN(A$8:A$30)+365*5+1,(IF(AND(A8>=$E$4,A8<=$E$5),1,0)*B8*(MAX(C8,D8)/365)+B8),(IF(AND(A8>=$E$4,A8<=$E$5),1,0)*B8*(D8/365)+B8)),"")
To this:
=IFERROR(IF(A8<MIN(A$8:A$30)+365*5+1,B8*(MAX(C8,D8)/365)+B8),B8*(D8/365)+B8)),"")
But it is giving me an error result.
View 2 Replies
View Related
Mar 7, 2012
I have this function that checks a postcode and returns a value using the first part of the postcode.
What i would like to know is how to modify the function so if there is only the first part of a postcode already entered, it looks for that.
At the moment sometimes i get #N/A even though i know that value is in there.
Code:
=INDEX(U$4:U$1068,LOOKUP(1068,MATCH(IF({0,1},LEFT(B338,FIND(" ",B338)-1),LEFT(B338,1+ISERR(MID(B338,2,1)+0))),T$4:T$1068,0)))
View 1 Replies
View Related
May 27, 2013
I do not have excel or an xls for a sample, I will try and restate the question.
In Cell A1 It will be used for copying the contents of and pasting the completed information into web browsers.
In Cell A1 You will find This information contained and formatted in one cell alone.
Figure-1
A1=
Information About Company
Brief Story About Company
Product Name: (X)
UPC Number: (Y)
Serial Number: (Z)
Terms Of Sale
Etc
End Figure-1
I want to essential Modify (X)(Y)(Z) using different cells one for each variable. "All completed information will be found inside of A1"
Having Cell B1-B9 Being used as labels "Product Name" "UPC Number" "etc" and using cell C1-C9 to input variables for = (X)(Y)(Z)(ETC)
In reality from cell A1 will change dynamically, but some information will need to stay constant.
Every thing In B column will just be labels.
Every thing In C will be for variables.
View 1 Replies
View Related
Oct 31, 2013
I'm using this custom format
Code:
_-£* #,##0.00_-;[Red]_-£* #,##0.00_-;_-£* "-"??_-;_-@_-
How do I modify it so that if the value is 0.00 then 0.00 is showed and not the current "-"?
View 3 Replies
View Related
Nov 23, 2007
The following code is a is designed to paste into "Y1", the value in column 5 of the active row when that cell is changed. This works perfectly if I select the value from a dropdown list and hit enter, or if I enter a value manually and hit the right or left arrow key. However if I manually change the value of the cell and then hit enter, it does not work because it looks for the value in the next row.
Does anyone have any idea what I can do to make the macro stay focused on the cell who's value changed even if I hit enter?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim AR As Long 'Active Row
AR = ActiveCell.Row
If Target.Column = 5 Then
ThisRow = Target.Row
If ActiveCell.Value > 0 Then
Selection.Copy
View 9 Replies
View Related
