I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.
View 3 Replies View RelatedI need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I started on the code below but am stuck.
How would I make the following code shorter/ more efficient? The columns don't vary much - either B, G or L. The rows vary a bit more....
View 9 Replies View RelatedHow would I shorten this code using a loop and arrays? It is working correctly but needs to be shortened down.
Sub cr()
Dim j
Cells(1, 1).Interior.Color = RGB(250, 155, 100)
j = 0.09
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.094
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.043
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.05
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.034
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.032.........................................
I have put together some code which works fine but is very clumsy and, when it comes to expanding it in the very near future, will look really bad.
rn = Worksheets("Test"). Range("C1").End(xlDown).Offset(1, 0).Row
Worksheets("Test").Range("C" & rn) = Odate 'a variable already created
Worksheets("Test").Range("D" & rn) = Range("E8")
Worksheets("Test").Range("I" & rn) = Range("E4")
Worksheets("Test").Range("K" & rn) = Range("D6")
Is there a s way to simplify this code (and the several more lines of similar) using For..Next and/or With function(s)? I've tried a few combinations but can't quite strike the right one.
I have a combo box that is used very simply to select a column from sheet and copy that whole column down to where text ends into a specific column in another sheet. I.e whatever column is selected ends up in column B of this other sheet. At the moment I've been using If functions for each case but there must be a more efficient way of doing this; here is just a sample of my
If textseries1.Text = "Fund01" Then
With Sheets("data")
Range("C1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund02" Then
With Sheets("data")
Range("D1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
If textseries1.Text = "Fund03" Then
With Sheets("data")
Range("E1:E" & Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Sheets("calculations").Range("B1").PasteSpecial
End If
There are about 75 columns in total but thought it would be a little repetitive to put it all up here!
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")
[Code]......
I would like to have a macro to loop through all the worksheet except for "Summary" worksheet.
The macro will look for the keyword in the worksheet starting from row 2 from every sheet.
if the keyword is found on the row, the entire row will be deleted and shift row up.
Keyword for example is found in H13 in the Summary worksheet
Summary
GH13KeywordLondon
Excel tables to the web >> Excel Jeanie HTML 4
I've written a macro for a set of tests I've created. It says "If the old value of the cell is '-' and the user enters 'Pass,' 'Fail,' or '-' then insert something into the cell directly to the right of the modified cell."
However, I'm receiving a Type Mismatch error any time I insert or delete a row. I think this is because of how I'm getting the previous value of a cell. The conditional is saying "I don't have a solution if there was no previous value of the cell."
Code:
Dim old_value
Private Sub Worksheet_Change(ByVal Target As Range)
prev = old_value
If Not Application.Intersect(Target, Range("E1:E65000")) Is Nothing Then
If prev = "" Or prev = 0 Then
'Do Nothing!
[code]....
I run a match formula which returns the row numbers of items i need to delete - at the moment, when a match is found, it will say "Match found, delete row 4" for example. Since multiple matches, potentially into the hundreds could be found - is there a way that i could run a script to delete the rows automatically when a match is found? So instead of putting the above "match found, please delete row 4" into an adjacent cell, could we just delete row 4? & then move onto the next row where a match is found?
View 9 Replies View RelatedI am trying to figure out how to not allow a word to be type into a cell. I am relatively new to excel and I am working on a spreadsheet where I do not want the word TOMORROW to be allowed in a cell.
I need to read A1 Rows and if it's values is bold letters then i have to add from top of it's above un bold members like that the should work for 2000 rows Like below Ites income - (A) vlues need to be added from row 3 to row 9
A) ITES
ITES Income
B P O - Domestic
4000101
[Code].....
I want to be able to create shortcuts for cells by using abbreviations. i.e If I wanted a cell to come up as "Food Services" all I would have to do is type in "FS" rather then the entire word or continually using copy/paste for the various abbreviations. Is this possible through excel or would it be best to be done through ms access?
View 4 Replies View RelatedI want to delete if my sheet have a value miles away
Sample
A1=0 miles away
a5=25 miles away
a30=50 miles away
how i delete these
a1=""
a5=""
a30=""
"' means null
insert a word object, type... and resize...
Dim objX As OLEObject
Set objX = ActiveSheet.OLEObjects.Add(ClassType:="Word.Document.8", Link:=False, DisplayAsIcon:=False)
objX.Border.Color = RGB(255, 255, 255)
objX.Height = 200
objX.Width = 600
objX.Top = 300
objX.Left = 100
'-------make active
objX.Activate
Set objX = Nothing
i would like to place the object in a worksheet and have all the rows of data move down below the word object (i.e., below the word object--i do this resizing manually now...)...?
I want to a macro to start of with a Find method Eg Account..something like this
Cells.Find(What:="Account").Activate
Account could be in any row or may not be in the document.
My problem is when it is not in the workbook my macro will come up error. how to recode my macro to fix this.
Dim Finder as string
Set Finder = Cells.Find(What:="Account",...............
'Problem here
'
If Finder is not found then skip till next part of macro.
If it is then just select the Cell and I can work of that.
Also Can I do a Find formula to Find Either 'Account' Or 'Debt' Or Asset'?? Just want to know save me copy and pasting it down to change a name.
I have an Excel Worksheet with 80,000 lines on it. The Columns are arranged thus:
Col A
Col B
Col C
Col D
Col E
Col F
Code
Description
Cost
Rate
Mobile
0.13
1234
Australia Mobile
0.12
Not Mobile
[Code] ........
I am looking for an Excel Formula that will look up Australia & Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to mobile (F3).
Then I just want a variant of the same formula that will look up Australia but exclude Mobile and then return the highest value of the range of cells it finds that meets that criteria and enters that value in the Rate column next to Not mobile (F4).
I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula = SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word.
Range("B7:B257").Find(What:="Reservoir").Activate
I have a data set of Purchase Order information (each PO on one line) that spans from 2013-YTD and includes the following fields.
P.O. Amount
P.O. Issue Date
P.O. Close Date
I want to know how much P.O. Amount was "Issued" in the same week in which it was "Closed".
I would like to derive the following from that information:
Average of P.O. Amount that is Issued and Closed in the same week (in % and $ terms),
Count of P.O. Amount Issued and Closed in the same week.
I don't know how to aggregate the information to know how much P.O. Amount was Issued and Closed in the same week.
I need to convert numbers to text in excel 2007. I have done it before in 2003, but can't seem to remember exactly what I used. I may have used some kind of conditional formatting, but not sure.
Here is an example of what I need.
If I type "7203" in a cell I want "Home Repairs" to appear. I have a list of words associated with a list of numbers and want to be able to type the number in a cell and have the word appear.
I have 10 worksheets in my workbook. The two that I'm concerned with in this post are "Main" and "Completed".
If Column AA in "Main" contains the word "Complete" (which is from a drop down list) I want to copy the entire row from "Main" and paste in the "Complete" sheet without over-writting previous pasted rows.
Back in sheet "Main" I want the data in that row to be cleared with the exception of Column A as it contains a formula for sorting blanks. If I delete that row it will mess up my links on other sheets.
I have two lists, one has 250 items, the other 4500, both contain a String
I need to compare any word within a cell in the short list, against any word within a cell in the long list.
The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.
I have two different functions, first is importing website to excel and the a second is testing string according to pattern. Each one of them is working ok. I'm trying to find a word " finance" in URL and put it into cell "A1".
Sub ParseWebsite()
Application.DisplayAlerts = False
On Error Resume Next
For i = 1 To 10
SiteURL = "URL;http://www.cnn.com"
With ActiveSheet.QueryTables.Add(Connection:=SiteURL, Destination:=Range("A" & i))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False................
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.
Sub test()
Dim r As Range
With ActiveSheet[code]....
The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.
How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?
I'm trying to automate a redundant task. I have several Word files in one central folder. I need to open each file, remove all paragraph marks (via find/replace all), copy the result, and paste it into an Excel sheet. This process will be repeated for each file in the folder.
The code I have is almost there, however, I can't get it to do the find/replace. Below is an extract of my code for the Word application part. The code for the copy and paste will be inserted after the replace action works:
Dim Wapp As Object
Dim i As Integer
WorksheetsLocation = TextBox1.Value ' this is the folder location brought in from an input box
On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number 0 Then 'Word isn't already running
Set wApp = CreateObject("Word.Application")
End If
On Error GoTo 0
I had alot of cells that has replication in the same cell. I would like to find out if the cell has any replication. If there is, delete the repeated data if not go to next cell
Example,
Before
Peter, John
Peter, John, John
Peter, John, Peter
John, John, John
Peter, Peter, Peter
After
Peter, John
Peter, John
Peter, John
John
Peter
[edit] The example above is 1 cell per line [/edit]
i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
I have a excel file with more then 10 sheets..Some of the sheets contains this word in some random cells" #DIV/0! " I want a macro which can find it in every sheet except parent sheet and can remove it.
View 5 Replies View Related