I have a macro that has following line
Range("C38'").Select
I want to dynamically change the C38 to may be C37 depending on a value in a cell i.e. F1
so if the value in cell F1 is 31 then I want this statement to look like
Range("C31'").Select
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).
View 6 Replies View RelatedI have accounts that I need to compare to see if they exist on my system the account that has a listed date, exist on my system then if I can fill the dates in the accounts the match then I will be able to delete the other accounts that don’t have a date see attach file for more understanding.
View 2 Replies View RelatedHow can I add the number of remarks to the number classes based on their row?
I have a TALLY SHEET which auto computes the number of occurances of each classes
and remarks…can someone help me how to add the class and remarks? In this example
you can see that CLASS A occurred 3x ,YES remarks occurred 2x and NO remarks occurred 1x for A class…
how can I add the occurances of YES and NO remarks to A class?
This should be the output…Remarks are being added according to the class they belong
ABEYESNONot Applicable
643242
how to count the number of occurrences base on a criteria? My sample file contains a Tally Sheet and a template sheet...how can i count the number of occurrences of Yes and No per class? Say for the A class, how can i count the yes or no?
View 2 Replies View Relatedtotaly new to Excel (just using two weeks) as a database
I need to number a colum, 1 to what ever, so that I can use that colum to re-sort the data base back into original order.
I have a number of Grand Totals that equal to Hours of Work in a day ( Based on Demand from Customer Orders)
I only have 95 ( this will be a number in a cell that I want to be able to change if needed) work hours available to me each working day.
I want each day to attempt to fill in up to 95 hours , anything more and it will push the remaining balance forward into the other cells.
IE here is what I have for the next 5 days for Totals
211.3798120.304870.0099110.734375.041828.1292
Under the 211 I want it to change to 95 and then carry over the balance to the cell under 120 , I then want that cell to change to 95 and carry over its balance to the next cell and so on down the line. I will always have 22 Working Days I want to work with. So the last day may or may not have a greater then 95 total.
The 95 part I want to be able to change that to whatever number I think I will have available to me and it will adjust accordingly through the line.
Lets say I have
Admin:
Base Salary
US Duties
Consultant:
Base Salary
Senior Advisor
Is there a way to create a macro that will change 1 of the Base Salary(s). I need one of them to be Base Salary - Admin, or Base Salary ' or something different for my vlookup.
Will this macro work for each different tab I have?
What I required is either a Macro or Code for formulas in column 'F' in the attached spreadsheet that correspond to the SUM of each description and divided by 37.5 e.g. in F10 the formula should be =D10/37.5 the formula should be F12 D10/37.5 and so on all the way down the Pivot table
My problem is as the amount data increases on the Data Tab the formulas in column 'F' will become out of line with the corresponding Sum of each description so I guess I need some code or formula that check every time the Pivot table is refreshed.
I have a macro that delete's a row when a cell in that row contains a specific value. I am unable to make the macro work automaticly when the file opens.
Sub Delete_Rows()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A1:F400"), ActiveSheet.UsedRange)
For Each cell In rng
[Code].....
I have multiple worksheets that call one userform.
Each worksheet has a specific word in cell J1 that matches a worksheet name.
How do I select a a sheet based on a cell value in J1?
Code: .....
I have this certain value "Y" in all the sheets. In the summary sheet, I would like to get the cell address for this value in a given sheet. I know that you can get cell address using CELL worksheet function. However, for that you need to know the reference to the cell, which is what I am interested in.
I tried Hlookup to get column #. But couldn't figure out how to convert the number to letter so that I can use MATCH to get row # and then finally, index or address function to what I am looking for.
If someone knows how to convert column # to column letter OR knows a better/easier way to obtain cell reference/address based on cell value,
i have it to where is highlighting a single cell i will like the macro to highlight entire row base on a single cell value.
View 2 Replies View Relatedi have a device that produce its data as following:
NTFLog_D2013-02-12_T104016.csv
I need to divide the single column to 7 column as separated by semicolon ";"
I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use.
Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN
Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE?
---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE)
I have attached a scaled down version of the Workbook.
Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
Is it possible to make a macro that will go cell by cell and format the last number of the cell as subscript? Here's an example:
Brooks #1H 5Stewart #8H, #9H 8Stevenson D Unit #1H 2150,000 BblsStewart #8H, #9H 8Stevenson D Unit #1H 2Brooks #1H 5Stewart #8H, #9H 8Holasek #1H 5Brooks #1H 5Stewart #6H, #7H 8140,000 BblsRayburn Unit #5H, #6H, #12H 6160,000 BblsHolasek #1H 5450,000 BblsStewart #6H, #7H 8#N/ARayburn Unit #5H, #6H, #12H 6Stewart #6H, #7H 8Holasek #1H 5Rayburn Unit #5H, #6H, #12H 6Stewart #6H, #7H 8Holasek #1H 5
I would like ONLY the last NUMBER of the cell to be formatted as subscript. The cells with "Bbls" would be left as is.
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
View 10 Replies View Relatedi have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
View 8 Replies View RelatedI have a macro that increase a cell number and one that decreases a cell number, heres my code:
Code:
Sub Macro1()
Dim t
t = Split(Worksheets("Sheet1").Range("d5").Text, "/")
t(0) = Format(t(0) + 1, "0")
Worksheets("Sheet1").Range("d5") = Join(t, "/")
[Code] .......
So they do exactly what i want them to do but i want to put a limitation on them, so say the number in the cell is 0 then i dont want it to be able to subtract from it anymore, and say the number is 20 i dont want it to be able to add any more, i tried to put if statments into the code but no luck.
I am new to macro. I am trying to convert the following from column 1 to column 2.
1
1
0
1
3
3
[Code] .....
In the left column, the numbers who are greater than 0 can have the same number of rows of zero.
Is there anyway I can do it to convert Column 1 to column 2.
i want a macro that make acircle around number in acell in excel debending on the nuber that the student given that under 14 has take acircle over is none
like that
this is another example code i fill acell with gray color
----------
For Each cell In Range("q6:q580")
Select Case cell.Value
Case Is >= 16
cell.Interior.ColorIndex = 2
Case Is < 16
cell.Interior.ColorIndex = 15
End Select
Next cell
------------------
I've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)
View 2 Replies View RelatedI have an excel sheet which is protected (mainly so that the users will not change any formulas) the excel sheet is basically a list
Just to put you in the picture
Column B Is PO Number
Column C Is Remarks
Column D Date
Column E Description
Column F Supplier
Column G Amount
I would like that when Column C changes to "OK Checked" column B C D E F G are protected and locked. But obviously for that particular row.
For example the input of Cell C15 changes to "OK Checked" then B15, C15,E15,F15,G15.
the macro must NOT be fixed to a particular row (in this case 15)
I would like to sum the amount column of unique date that is in active status of each account.
Account Date Status Amount
11 jan 1 A 200
12 jan 1 A 5
13 jan 1 A 1000
11 jan 1 A 200
11 jan 3 A 100
12 jan 3 A 8
13 jan 4 A 1
13 jan 4 A 1
11 jan 6 I 50
12 jan 6 A 10
Below is the expected result
Account Amount
11 300 (200+100) [200 is only add once since jan 1 of 11 has appeared more than one time]
12 23 (5+8+10)
13 1001(1000+1)
I have entered this value into a cell on my worksheet. It is referencing a cell in another worksheet on a shared drive on my network.
='\servershare$[doc1.xls]Sheet1'!$A$1
All is fine until I re-open the document, whereby it has changed to:
='\serverusers$share$[doc1.xls]Sheet1'!$A$1
I have been advised to set the hyperlink base as \servershare$
...but this clearly does not work.
i have used the database from j & r solutions. i have altered it slightly to suit me. the database works fine apart from when using the find all button it will only return 4 entries. if there are more than 4 entries it returns runtime error 9.i have zipped up the code and marked where the error is shown when i debug
View 2 Replies View RelatedTrying to extract data from Column C which contains date and time and number
example :
column c cell contains
<html><b>04/12/13 00:44 - text:</b><br>Event xxxxxxxx, yyyyyyy assigned. <br>Reference ID associated with event. ID: 123456<br><br></html>
need to extract and paste in Cloumn D
04/12/13 00:44
and column E to contain
123456
note : <html><b>04/12/13 00:44 - ( <html><b> and event. ID: is constant in all my cells)
I'm working on a charting application that uses Office charts. I'm wondering what are some of the main reasons people change the base unit from the default, and how common that is.
Some reasons I've identified on my own:You're comparing different monthly metrics, but they could be recorded on different days of the month.Get a zoomed-out view.Compare the max value of each month.