I have an excel calculator that I use daily (mostly written in VBA). I change some of the values to get a new result. Is it possible to post the values of the results in a column so that I can see what the old values are? For security reasons I cannot disclose the calculator but I guess I can post an example of how I would like it to work.
View 3 RepliesI'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
I'm not that clued up on VBA or macros but I do have a few i regularly use, I’ve found a thread related to my problem but cant make much sense of it.
Basically i want to post the 3 cell values (C5,C7,C9) into row B20,C20 and D20 respectively. However I want these values to form a list staring with row 20 and working down onto the next available/empty row.
i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results
LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design owb").Cells(i, 10)
With Sheets("design")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True
In my spreadsheet, I have a column for credit card totals (G) and cash totals (J)
I am adding the amounts for Food (Column B), Liquor (Column C), Wine (Column D), Beer (Column E), and Taxes ( Column F) for a total in Column G.
However, Column G is only for credit card totals and I want to do the same calculations for cash totals (Column J) using the same B through F Column without those numbers being plugged back into Column G, when I have no credit card total.
I have been able to produce a column that gives me key date information. I might have many empty cells in that column before it hits another date. I need to get this data over to another sheet and place it in a row.
I am trying to get it to look like this:
B2(value), C2(value),D2(blank),E2(Value),F2(Value),G2(blank) etc depending on how many more elements are found in the sheet.
I am able to find topics that allow me to copy a range of data but not one that will allow me to copy a range then skip a cell.
I have attached link to a resource planner I have cobbled together and am stuck on the last piece of code. The "Assign Project" button needs to take the project and resource from the drop-down lists and populate the column of that resource with the project name between the dates that have been selected.
Resource Planner: [URL] ....
I'm using Excel 2010 and Windows 7
In column A I have 10,000 road names with many duplicates. In column B I have the duplicate roads removed with only one left (about 1400). In column C I have a map code for that road, married to column B. Question; how do I get the map code to post to all the equaled roads in column A?
View 4 Replies View RelatedI have two named ranges 'wrkNRP' and 'wrkQTY'.
Instead of totalling each range seperately, I need a way (within VBA) to go through every value in both ranges and mutiply them together, then record the total- e.g.
wrkNRP has the values
10
20
30
40
wrkQTY has the values
10
20
30
40
Then I need a way to do (10*10)+(20*20)+(30*30)+(40*40)
Is this possible WITHOUT adding an additional column?
I have a spread sheet which is organised in date order column A and produces three signals Buy, Sell, No trade in column S. If i get a signal to buy or sell the price is taken in column T so i might have Buy - 1.9977. I wish to calculate the diff between the value taken to buy in this eg and deduct this value from the opp signal at some point ie a sell signal. Other buy signals and notrade signals should be ignored. The opposite is true for a sell signal
Eg
A,s,t,u
13/4/07, buy,19955,35
12/4/07,no signal
11/4/07,no signal
10/4/07,no signal
9/4/07, no signal
8/4/07, no signal
7/4/07, sell, 19990,13
6/4/07,no signal
5/4/07,no signal
4/4/07,buy,19977
if a buy signal is generated then in this eg 19990-19977 gives the diff if selling then 19990-19955 gives the diff .
I have a file where I have few sheets (link to file below). Sheet zrzut is dropdown from other system - updated daily. Sheet Productivity is filled with data supporting to group data and add conditions (for example column "I" in "zrzut" sheet). Sheet "Vena" has data from "zrzut" based on condition in column "I". Sheet "VENA_PLAN" has the same data as "Vena" but only values - I need only values for other actions. The thing is my current code replaces old data with new, but what I would like to achieve is to have new data stored below old. For now code looks like
Code:
Sub PlanVENA()
Application.ScreenUpdating = False
Worksheets("VENA_PLAN").Range("A3:H300").Value =
[Code]....
Based on filtered data I want to produce a result for the Average trade gain.
Lets say I have 100 rows of data, and based on filtered settings the rows reduce to 5 rows of results.
Of those results I want to find an average of all the positive values (trading profits) and ignore the negative ones.
$2000
$1500
-$700
$4000
-$1000
The answer should be (2000+1500+4000)/3
The need for filtering makes this problematic. I am inclined to use SUBTOTAL as it applies to filters rows only, but I can't see how to use an IF statement within it.
In addition to this I would also love to know how to subtotal all positive values without averaging.
I'm trying to populate the Hourly Rate from 4 different Vendors based on 2 conditions.
What is the AREA and what is the Category.
The Area is a pick list (10 unique values) and the Category is a pick list (50 unique values)
If A2 is picked from AREA and B5 is picked from Category THEN populate the value of C5,D5,E5,F5 into the cells of J2, K2,L2,and M2 ...
I need cell d4 to display the number in column b next to the applicable insurance company when the insurance company is selected from the drop down menu in cell c4. How do I do this?
View 4 Replies View RelatedI have a simple one today but on a time crunch so I don't have time to go through the book.
I am trying to hlookup a list of values from a table and average the results. The values are setup horizontally.
I have the following code that copies only the visible cells in an auto filter...how could I modify this code to paste only the values and not the format?
.Range("a:a").AutoFilter Field:=1, Criteria1:="True"
.Range("b:d,g:r").SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Monthly Hours").Range("A1")
Depending upon a figure entered e.g. = $ 895.00 and also Depending upon a state being entered e.g. = NSW. Then Excel will lookup a series of cells to produce the correct figure depending upon the correct data ciritera being met. I have a data table sitting behind the spreadsheet with all relevant values.
View 3 Replies View RelatedI want to locate the corresponding acct number between worksheets “primary_data” and “qty_movement” and once a match is found (from acct worksheet) then copy over the acct numbers and the share data to the results page. If there is no match I don’t want anything copied to the results page, just ignore that data on either worksheet “primary_data” or “qty_movement”.
•Both ranges on “primary_data” and “qty_movement” worksheet are variable, as accts are left off or kept on depending on the daily activity so the ranges are never set.
•I need to concatenate on “primary_data” worksheet cells A&B&C to get acct number
•concatenate on “qty_movment” worksheet cells B&C (need to keep zeros in front of actual numbers for acct number reasons)
•these accts need to be cross referenced as the accounts on the “acct” worksheet (pre-populated with corresponding acct numbers that never change and will always be on that worksheet)
•If a match is found on the “acct” sheet then I would like it to either replace the acct so they match on both worksheets or just to recognize those accounts correspond with each other and do the below.
•then take the 2 concatenated acct numbers off of “primary_data” and “qty_movement” in the I cells and copy them over to the “results” worksheet as well as the share data from “primary_data (column E)” and “qty_movement(Column D)” and then compare the 2 share amounts on “results(column E)”
i need to elaborate on anything, the attached sample sheet shows what i want to do, but without any formulas or code.
I am wanting to have conditional formats so the cell fill in a range on sheet 1 will change to:
- red if the value of a cell on a seperate sheet (date) is greater than 18 months old
- yellow if the value of a cell on a seperate sheet is 'In progress'
- green if the value of a cell on a seperate sheet (date) is less than 18 months old
I have a work sheet which includes a column of numbers representing certain daily events. I am building a user defined function to analyse the trend in the numbers by assisigning values from -2, -1, 0, 1, 2 based on comparison of two days.
Below is the function I built but it is not working, it is resulting in zero values in most conditions. I have attached the sheet which includes the numbers and the function.
When I type in a cell [say cell F1] "=sum(A2:A8)" I get a "0" as the result.
Yes, there are numbers in A2 through A8 which add to 12. Anyone know why the
formula doesn't work? This happens on some sheets in my workbook, but not all
sheets.
Also, another wierd thing: on some cells doing the above reults in the
formula showing as text in the cell.
At the end of a long macro, I want to search the value in Column A and if value = 0 then it'd return the values in adjacent cell to the right in a msgbox.
All that is returned is the text I inputted.
What I have so far (I picked it up on the internet):
Code:
Sub Macro2()
Dim msgaddress As String
For Each c In Range("A:A")
If c = 0 Then msgaddress = "Portfolio Deal with invalid ID:" & vbNewLine & ActiveCell.Offset(0, 1).Value
Next c
End Sub
can Excel make a Ratio of the results of one column against another ?
eg, Col A 2.00
Col B 1.00
in Col C, the ratio result is 2 : 1
I have a workbook with two worksheets.
On Sheet1 I have two columns 'Date' & 'Serial Number'
I want to search the 'Date' column for entries that contain todays date & then display the corresponding 'Serial Numbers' in the first column of Sheet2.
I would like if someone could tell me what formula to put in the cells in Column E so that I can copy it to all cells in Column E and get the right results.
Here are the results I need: ....
I have a problem with my spreadsheet. I am trying to created a sheet to record time trials for bicycle racing. I have created in the main tab 'time entry' a class, rider #, name, incremental start time, and finish time. There will be 6 classes of racers (i.e. xm,xw,sm,sw,nm,nw). What I am trying to accomplish on the results tab, are the results based on the riders class. All of the riders classed as 'xm' listed with their respective info and time. I have attached a copy of what I have so far.
View 2 Replies View RelatedI am attempting to copy the results of a formula in a column to a row. The data in the column is in a different worksheet. Although pasting the data using the transpose option in the drop down menu works, I really want to have the results of the formula returned. The reason for this is the results are variable based on the formula.
View 9 Replies View RelatedIn column B I only want to show the results from Column A if the character count equals 3. In Column C I only want to show the results from Column A if the character count is greater than 3
Example:
Column A
ast
john
julie
tyw
nhy
frank
I have a table of data with years in row 1, week # in column C. The data array is from C2:O34.
I want to find the max value in each year column, then look up that result and return which week that value occured.
Is there a vlookup that allows you to specify which column of the array has the lookup value and which has the lookup result?
Im trying to write a marco that will look in colum E and find criteria "a", then offset (2,1) and copy the activecell and the one below it to a1:a2, then return to where it was and offset (0,1) and then copy the active cell and the one below it to a4:a5.
after this is accomplished i need it to return to column E and start the process again for "critera b" paste the result to a7:a8 and a10:a11
I know folks usally post examples of the there code for other to take a peek at however I only know bits and pieces of the process and im not sure how to tie it all together...I was hoping someone could help me get a good start....thanks