Let me know how can a worksheet pick automatically equal = sign before manually feeding data
for example:
A B C
1 5+3 2+1 9+2
2 2+3 8+6 4+7
I want 8 in cell A1 and 5 in cell A2. I do not want to manually assigning/adding equal = sign to get sum 8 in cell A1
i want formula in b1 to get the formula in a1 without Equal Sign
A1=(A2*A3)+2
Reults B1 should be (A2*A3)+2
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort?
Private Sub ConditionalRowDelete()
Dim NumRows, iLine As Integer
ActiveSheet.UsedRange.Select
NumRows = ActiveSheet.UsedRange.Rows.Count
Dim CircuitType As Range
Set CircuitType = Range("C" & iLine).Value
For iLine = NumRows To 2 Step -1
If Range("A" & iLine).Value > "6999" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
For iLine = NumRows To 2 Step -1
If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
End Sub
This seems simple...
If I let A1=8
If I fill in the cell green, either manually or with conditional formatting, and then copy and paste the cell into A2, A2 is now green with the number 8 inside.
However, if in A2 I type "=A1", A2 still has the 8 but is no longer green.
Is there a way to copy the formatting of a cell by setting it equal to the original?
For Exempel
A1=B
A2=1
A3="="&A1&A2
I have even tried with concernate and indirect in A3 but no difference there...
I want A3 to return =B1 but it only returns it in text but i want it in form of a formula. Is there a formula that returns text directly into a formula or an easy way to do this?
i have table of values if any value in the coulomb G and I greater than or equal to 1 the entire row must be deleted how can i do this ?
View 4 Replies View RelatedI need automatically deleting of whole row based on cell value, if value of cell which is in h:mm format, is less than or equal to 2:00 (h:mm)
So far I got.
Code:
Sub FillDownFormula()
Code:
Range("L2").Formula = "=RC[-3]-RC[-2]"
Range("L3").Select
Columns("l:l").Select
Selection.NumberFormat = "h:mm"
Dim rng As Range
[Code] .........
I am trying to do is erase the entire row if column J is equal to 1205. I have made an attempt which is shown below, but I am no where near a VBA programmer, yet.
Sub Softrak_Format()
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False........................
I need an easy code that searches all of column A and deletes the entire row if the cell has the value "-". It needs to find the last row of data using something like LastRow = Range("A" & Rows.Count).End(xlUp).Row
View 4 Replies View RelatedI receive a large file every month with 30K+ rows of data that need to be manipulated manually before I can send it off. Within the data are "chunks" of data that are anywhere from 1 to 90 rows each. Each chunk needs to total 100 or less, and while they do for the most part, some end up over 100 due to rounding (usually 100.02 or so). The rows above and below each chunk are blank, and I currently have a calc to add each chunk, and flag it if it's over 100. Then, I have to go through each of the flagged chunks and manually decrease one of the lines to make the total 100.
Is there any way to automate any or all of this procedure?
I have attached a sample file with two chunks of data. The actual files contains 14 more columns to the left that aren't shown.
Sample.xlsx
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
My boss uses the + symbol and the = symbol in his formulas eg "=+E3*E4" What is the advantage or difference in this as to just using "=E3*E4"
View 3 Replies View RelatedIn a formula, what effect does putting a plus sign after an equals sign? e.g.
=+((1+B8)^12)-1. I orginally assumed that it made sure that result the would always be positive but I was wrong.
I am trying to work out a formula. I know it's a simple one but i can not work it out. If i work 9.5 hours or more per day I get an extra $10.50 for food allowance.
For example (see attachment) IF E17 is equal to 9.5 or higher then cell E23 should automatically read $10.50
What I want is that I have a table like below (but it's long for 52 weeks) and long down with Vlookups. I want the formula with which I can just do the copy-paste and it will work. W1, W2.... are the sheet names with exactly the same formats inside.
A
B
C
D
E
5
W1
W2
W3
W4
6
Sales
10
#N/D!
[Code] .......
The base formula (for W1) is:
=Vlookup($a6;'W1'!$A:$B;2;0)
What I want, is the formula which instead of "W1" will write the sheet name which is in a row 5 (basically - cell name which is equal the sheet name), so with just dragging and moving the formula I will got the data from different sheets.
I tried this: =Vlookup(A6;'indirect("c5";1)'!$A:$B;2;0)
But I got #N/D! as in the example, instead of the numbers (yes, I put numbers into W1 and W2 sheets .
I have a 40+ page workbook that I'm working and I need to print every sheet in the workbook on legal size paper, and each sheet can not be > one legal piece of paper
The macro will cycle thru all the sheets in the workbook, yet it will only adjust the current active sheet. I highlighted where I think my error is in red....
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories:
Requirement: 10358
Title: Customer requirement 1
Text: This describes the requirements for a product for the customer[code].....
The text of the categories may begin in column A or B
What I need is a macro that will search each worksheet for a category, e.g., "Configuration:", copy the row where the keyword "Configuration" is found, and then have that row transposed and pasted to another WorkSheet (e.g., "Extracted Data") cell.
Data extracted from the next Worksheet would begin a new row in "Extracted Data"
Example:
Requirement1 Title Text Verification Method-Level ...
Requirement2 Title Text Verification Method-Level ...
Requirement3 Title Text Verification Method-Level ...
The Requirement# is best extracted from the WorkSheet tab since some of the worksheets are missing this information.
I can provide an example spreadsheet, however, I was not able to figure out how do that in this post.
I found a thread similar to this problem:
find and copy row
However, it only finds, copies and pastes for one keyword.
I am looking for a solution to an otherwise very tedious problem.
I have an Excel WorkBook with 100 WorkSheets.
Each Worksheet has a unique identifying label - "Requirement Number"
Within each worksheet is free form text data of the following categories: ...
Anyway, here's what I'd like: I have a spreadsheet with every student in the school listed. If they have signed up for my after school programs, they also have a 3-day schedule. Each program has a specific initial, so if a student is in 1st grade Art on Monday, they have a "1A" in the Monday column. I've attached a sample spreadsheet with a simplified version of my setup.
At this point, I'm filtering by each initial and highlighting names to print a roster for the day. Is there a way to have the names of each child in a particular program automatically populate an (easy to print) formatted roster on another tab? Each student does have a unique student ID, but I wouldn't want that private info to be printed on the roster. I'd like to just be able to enter kids' schedules as they come in and that automatically adds to the program's roster, then go to the "Monday Rosters" tab and print the whole thing, cut it up, and hand out to teachers.
Basically every month I keep a track of my sales in a spreadsheet.
I've attached a sample file : Discrepancy Form.xlsx
Sometimes I don't get paid correctly. So rather then filling everything in manually, I want to simply put a "Y" (for yes, there's a discrepancy) or "N" (for no, there's no discrepancy" next to worksheet "Sales" but then have it display the values in worksheet "Discrepancy."
I have a worksheet (will be the last worksheet) that I want to use as a template for further worksheets.
I want the user to be able to run a macro (?) and copy the entire worksheet and then insert it to the right of all the worksheets as many times as needed.
I've a worksheet where I maintain the IT tickets that I handle. Whenever, the I column is updated with Implemented/Closed, the entire row should be copied to a new worksheet.
I've tried with other VBA scripts provided, but was not success with the same.
This is for mileage purposes. I have two sheets. 1st sheet I have Date, From, To, Mileage. I want the Mileage cells to automatically equal the mileage it is from and to. Right now I have it setup like this on the 1st sheet B7 is Date, C7 is From and D7 is To and E7 is Mileage. I have a drop down menu for C7 and D7 for our facilities such as Ming Ave (MNG), Coffee Rd(COF) and so on. On the 2nd Sheet I have the chart that show the miles.
example MNG to COF is 8.3 miles.
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies View RelatedSo I have an option on my userform that the user input selected.
I need the data once written to the worksheet [Master] to move based on cell AD [Customer Divert / TBP Divert / Failed Delivery]
Example: Row 12 has Customer Divert in cell AD I need to keep a copy on the master sheet but also copy the entire row to the customer divert sheet.
Need code and here to input in the Code sheet?
I want to run a macro that looks thru column F of all my worksheets (in my entire workbook).
If it finds an x then I want it to copy that entire row and paste it into a worksheet called "old" or a new worksheet or a new document in word (it doesn't matter - just whatever is easiest for my little brain to understand).
I should end up with about 40 rows of data in the new sheet.
(I have some code that looks thru it all and colors the cell blue but I just don't know how to get it to copy and paste the entire row into a new worksheet.)
I have a worksheet thats makes things easier by sorting to users needs. heproblem I have is I have totals in columns p:AJ rows 1:3. How can I prevent the user of sorting the whole worksheet. Most of my users have basic Excell understanding so sorting is usually the whole page. I've tried hiding and protecting the columns but must be doing some thing wrong. I just need them to only be able to sort column A:N. I'm already working on moving totals to a separate sheet to I know this will work but curious for future worksheets.
View 3 Replies View RelatedI am starting within a workbook with a standard invoice/form. During the course of a month this invoice will be copied 20 to 30 times within the workbook and the amounts changed each time. Is there a way that I can:
a) Have amounts from 2 of the cells (say E30 and E31) within each new invoice worksheet automatically included on a summary page? These cells are calculated by formula.
b) (This one would be nice but is not essential) Have each new invoice worksheet that is added (by copying) automatically numbered sequentially in a cell (say A2) and also re-named with this number. The first existing invoice would be manually numbered and each additional invoice would follow from there.
trying to speed up my workbook, because all of the formulas depend on time and the now() function. It takes forever to add/change anything.
Here is a general description of my worksheet. It's for a game called tribal wars (online browser mmo). You have towns around you, which create resources to plunder. This resources build up over time, thus I need to use the now() function in basically every formula. Each row finds the distance to that location, the time since last attack, the estimated resources built up since the attack, and the number of troops to send.
If only there was some way to update now() only every 1 to 5 min instead of every time I hit a key...
Is there a formula/function in excel that can take all the cells in a column, lets say Column A, and paste/display it in another file without the spaces between the cells with values and without duplicates?
Worksheet1
Column A
1 Name
2
3 Mike
4 Rob
5 Ryan
6
7 Mindy
8 Paul
9
10 Rob
11
12 Mindy
13 Chris
Worksheet2
Column A
1 Name
2 Mike
3 Rob
4 Ryan
5 Mindy
6 Paul
7 Chris
8
9
10
11
12
13
