I recorded a macro:
Code:
Sub Makro4()
Range("A4:C15").Select
Selection.ClearContents
[Code] ......
This code is working home on my MacBook, but I wonder if you have any suggestions for improvement? I will assign this macro to a button.
I am trying to find something that will let me look at a Log Book Page (a Worksheet). I need it to look through Cells E4 trough E15 I want it to look for the 1st entry of 3 different materials.
3/4" Plywood
1/4" Plywood
3/4" Particle
These names are in a drop down menu from a different sheet in the same work book
Sheet Name link '.'!
'.'!A2 3/4" Plywood
'.'!A3 1/4" Plywood
'.'!A4 3/4" Particle..............
I need a macro that will search though Column AD and delete the cell contents if the cell contains "0" (zero). Edit: I'm sorry I just figured this out using the following
Dim lastrow, j As Integer
lastrow = Cells(Rows.Count, "ad").End(xlUp).Row
'I presume the last row C is not blank
For j = lastrow To 1 Step -1
'MsgBox j
If Cells(j, "ad") = 0 Or Cells(j, "ad") = "0" Then
Range(Cells(j, "ad"), Cells(j, "ad")).Clear
End If
Next
I got some data like this
CTM
KKB
ACD
TEEE.PK
ACFFE
OO.OB
What i want to do is to delete everything after the dot. And the dot itself
i have code like this to copy certain word in cell and place it at new sheet:
VB:
Sub Foo()Dim i As Long, iMatches As Long
Dim aTokens() As String: aTokens = Split("Accommodation", ",")
For Each cell In Sheets("Sheet1").Range("C:C")
[Code].....
the problem is the cell source is not deleted. what line should i change in code above to move the result to new sheet and delete row source
I am having some trouble writing a macro to delete cell content. I know which cells I want to clear, but I can't seem to get the macro to do it.
I also want to have something in the macro that inserts data into the lines that I am trying to clear. I want cell A67 to say "Payment" and cell A71 to say "Total."This is the code I am using:
Sub DeleteCell()
Dim i As Integer
For i = 67 To 71
If Range("A" & i).Value = "--" Then
Range("A" & i).Delete
Next i
End Sub
I need a VBA that i will attach to a button that will delete spisific data in the cell.
Eg... delete all the cell CONTENT in column A1:A65536 with the word "NONE" in them, it must only delete the word not the cell.
This formula counts the number of spaces between words in a cell:
View 5 Replies View RelatedHow to determine the correct formula for this requirement?
Assuming I have 2 individual excel files and an index excel file (in reality, there are more than hundreds of file). For index excel, once the user enter part number (eg. 1 or 2), the excel will look for the part number excel file and determine vlookup function.
The only problem I have here is I can not make the index file automatically add the part number shown on most left column into the required formula (replace the part number file section).
I tried use the indirect function but this require each file to be opened, which is not possible for actual use. I am looking for a function that can work in closed worksheet.
Attached files :
index.xlsx‎
1.xlsx‎
2.xlsx‎
I am currently trying to create a spreadsheet whereby if I enter certain text in a cell in Column A on worksheet 1 that correlates with text in a cell in Column A on Worksheet 2, then the description in Column B in Worksheet 2 is entered into Column B on worksheet 1.
For example, if worksheet 2 has the following:
Column A Column B
XXXX PRODUCT 1
YYYY PRODUCT 2
and I enter XXXX in column A on worksheet 1, I want Column B on worksheet 1 to automatically enter PRODUCT 1.
is it possible to have a formula that will look for keywords and display the cell content?
View 2 Replies View RelatedI am trying to reference a named cell in a VLOOKUP formula as the table_array parameter.
The Named Cell "Input1" contains a table array definition of '[Test.xls]Sheet1'!$H$10:$J$100
When I try VLOOKUP($D3,Input1,3,0) - This returns #N/A as a result.
When I try VLOOKUP($D3,INDIRECT(Input1),3,0) - This returns a #REF error.
When I copy and past the contents of the Input1 named cell directly into the VLOOKUP formula, it works so I know the array reference is valid.
I am not proficient in VBA so I would like to accomplish this using basic Excel formulas if possible. how I can reference the named cell and have the cell value returned in the VLOOKUP function?
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Attached file 140515 Matchsheet.xlsx has formula that finds costs matching two criteria.
But the formula depends on me typing in the specific Worksheet Name: So to find the cost for 'Type 1' products, I enter:
=IFERROR(INDEX('Type 1'!$E$5:$F$11,MATCH($C$4,'Type 1'!$C$5:$C$11,0),MATCH($D$4,'Type 1'!$E$4:$F$4,0)),"")
I want the formula to "see" the sheet name as whatever is in a specific cell.
E.g. Cell B4 says "Type 2". The formula will be:
=IFERROR(INDEX('[WHATEVER NAME IS IN B4]'!$E$5:$F$11,MATCH($C$4,'[WHATEVER NAME IS IN B4]'!$C$5:$C$11,0),MATCH($D$4,'[WHATEVER NAME IS IN B4]'!$E$4:$F$4,0)),"")
how to pass a cell content into a formula as a named range? For instance, I have 3 named range (Budget, Actual, Forecast) and I use the named range in vlookup formulas. But instead of using the named range in the formula directly, I want to refer to a cell where I can type in which named range I want to use. Can this be done? I am attaching a sample spreadsheet to clarify what I mean.
View 6 Replies View RelatedFormula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
I have a column containing mixed text and I want to remove everything from the column except instances of certain colors; black, red, blue etc. Is there a formula which would do this or can it be done with Search/Replace?
View 9 Replies View RelatedCan a macro be used to:
1. Delete rows that contain certain text in a worksheet ?
2. Highlight a cell a colur based on a response ?
I have attached an example of what i mean .....
In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ?
Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?
I have a long list that has all the services from our stores. I´m using the formula below to count how many services does each store have that are over 20 days old.
=SUMPRODUCT((G!$A$2:$A$6000=$B4)*(NOT(G!$I$2:$I$6000="Valmis/Odottaa nouto"))*(NOT(G!$I$2:$I$6000="Valmis, toimitettu"))*(NOT(G!$I$2:$I$6000="Finished/WaitingDeli"))*(Päivämääärät!$C$1-G!$C$2:$C$6000>Päivämääärät!$I$3))
Now i´m wondering is it possible to make a macro that would delete all the cells in the range that do not match the criteria of my formula?
With one exception in sheet G cell A2--> has the number of the store. There are over 40 stores so each store has a number 10, 20, 30, etc...
This is the part of the formula that determines witch store it is counting
(G!$A$2:$A$6000=$B4) B4=10 in this case and B5 would be 20 and so on... By modifying that one part i can count old services for all of our stores.
Now I want the macro to delete all services from the list that are not over 20 days old and belong to the store 10 (B4). If it would delete everything that dont match that formulas criteria i could not get a list of old services for our other stores.
What i´m trying to accomplish is get a list of the services that are over 20 days old. I´m now picking them from the list by hand and that takes a really long time. Because of the number of the stores and the size of the range.
Need macro or other code that will examine a column in my my table for particular text (example: find text: "Total") and then DELETE the whole row that the cell containing the text is in.
I have this:
Sub CompileData()
'Dim rng1 As Range
'Dim rng2 As Range
'Set rng1 = Worksheets("Sheet2").Range("A4").End(xlDown)
'Set rng2 = Worksheets("Sheet2").Range("C4").End(xlDown)
'Set AllRng = Union(Range("rng1"), Range("rng2"))
'Worksheets("Sheet1").Range("A5").End(xlDown).Delete
Now when its pasted it has cells with "Nil" in it. I need to delete them, or just not copy them over.
i have data from D2:D10000
I Used data of first five rows e.g ("d2:d6") if in range ("d2:d6") any cell is blank then delete the all contents of the rows
e.g
d2 have data
d3 have data
d4, d5, d6 have no data then the contents of range ("d4:d6") should delete
I have a list of data done by date that is updated weekly. The data in the list changes but only the current month will have changes but the data goes back for a couple of years. Is there a way to clear only the current month from the list and then bring in the data for the current month to the list. The monthly data is stored in worksheets labeled by the month (January 13, February 13) So what I need with for the active worksheet (Master list) to keep all the entries from previous months, remove anything from the current month, then check the the current month worksheet and import the new data. The data on the month worksheets is in columns A and B, and gets moved to Columns A and B on the Master list.
View 7 Replies View RelatedI would like to delete the content only of every 5th row - I do not want to delete the row.
I have about 6000 4 line addresses in one column and between each address is the number 0. Need only to delete the 0.
I have a worksheet in whichs Column A Cells , there is corresponding string content in Col. Z. Some cells of column Z contain of many lines (up to 100 line) which i want to reduce to one. I want to select one line through double clicking in the listbox of the userform and all other lines should be deleted and only the clicked one should remain. Code should then jump to the next non empty Z cell.
Columns B, C, D, E, F, G and H should also be shown in the userform and they should be live editable. I have attached and example file with userform and example data.
Need to find a column of numbers from one worksheet and delete them from another.
Example
To be deleted is below:
83221
83223
83232
83233
83236
to be deleted from is below:
83221
83223
83226
83227
83228
83229
83230
83232
83233
83234
83235
83236
Another issues is that the worksheet that needs to have the numbers deleted from has 3 columns total so the whole row would need deleting, or if its easier I could replace the numbers with 0 and then sort and delete.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I am trying to create a way to easily maintain a list of outstanding checks. I am able to import cleared checks into the spread sheet but cannot find a way to compare the two lists and then delete the cleared checks off the original list.
View 2 Replies View RelatedUsing excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"
EX:
05S0128
06S0112
05S2298
S25852
S36963
SA36185
I would only like to keep the last 3 lines.
I need a formula that would scan a given column for data, get the first available data from the first available cell ( D1 ), perform computation, input result in a different cell ( A1 ) and then delete the contents of D1 and move on the D2 and so on till end of data. So far I have been able to have the result in A1 but could not achieve the deletion of D1 contents. The purpose is to prevent the formula from scanning the data column from D1 again. In this way, the datawould be made available in one column and when the data have been processed the column would be empty and the result would be in another column.
View 2 Replies View Related