In an excel sheet, I have 5 columns namely, name, address, source, subtype,code. In subtype column we enter the the type for example: name,SBI bank then subtype will be bank, and if it is SBI atm then subtype will be ATM etc. Similarly all the subtype will be given a code like 4 for ATM and 10 for Bank respectively. My query is that , is it possible to update the CODE column by comparing the subtype column using macros in excel.
View 6 RepliesI need a formula that let me change the value in column B with the value from column D. But to do the change I have to check that the value in column C is equal to the value in column A. If a value from column A is not present in column C (like 1002 in the example), the value in column B wont change. The values in columns A and C are alphanumeric, the values in columns B and D are numeric.
For example:
Column A Column B Column C Column D
1001 2 1001 4
1002 1 1003 5
1003 0 1004 1
1004 2
The columns A and B should end like this (Changed):
1001 4
1002 1
1003 5
1004 1
I have two sheets. One called "Roster" and one called "final". On the final sheet i have cols for each question on the final exam. I also have a total col which sums up the pts for each question.
On the "roster" sheet. This is kinda like a summary sheet. On this sheet i use a vlookup (shown below) in the cells which are supposed to reference the cells on the "final" sheet for the total pts.
Check the attached sample file
a. actual data in columns is approx 150 items (that is why i need to hide/unhide columns)
b. there are about 120 persons whom above items are issued depending on expiry of life of item i.e. issue date plus number of months mentioned in row 3. for example if coat was issued to John on 5th Jan then it's life expires on 5th May and so on.
Now what i want
1. by clicking hide checkbox only that specific column should hide or if i click Hide All check box all column in range should hide.
2. as i enter issue date expiry of that item should automatically update on corresponding month sheet i.e for above example 5th May should automatically get entered in sheet May.
I have entered sample data manually in months sheet.
aJITCBP.xlsm
I am trying to read a value in cell A1 and make say cell B1 read the same. The data in cell A1 is a real time percentage value that gets updated every lets say 10 secs. Every time the data is updated the new value goes into the next cell A2, A3, A4..... and so on. Every time it gets updated (i.e into A1,A2,A3,A4.....and so on), I want to read the new value each time in B1.
View 9 Replies View RelatedI copy a table from a data dump dbf file into worksheet 1. I enter a separate column titled "Comments" and use this for status purposes. My problem is that when I refresh the worksheet, my comments don't stay attached to the rows/ records that they were originally entered for.
View 9 Replies View RelatedI have a list of two columns. Here’s an example. The left most column provides the row number.
_ A B
1 1 0
2 2 1
3 2 1
4 3 2
5 4 2.5
6 5 4
7 1 0
8 1 0
9 2 2
Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.
For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.
Here’s what the updated list would look like:
1 1 0
2 2 1
3 2 1
4 3 1.5
5 4 2
6 5 3.5
7 1 0
8 1 0
9 2 1
I have several macros running on my workbook, referring to different cells. When I add/remove a row/column in my workbook, I need to manually update all cell references in the Excel macros so that they point at the correct cells after the new rows/columns have been inserted.
View 2 Replies View RelatedI would like to convert rows of text into a table thats readable.
There is a list of 10,000 rows filled with address information like below all in one column, separated by rows.
The Macros should look for a row that starts with the word "Detective"
It should then delete the word "Detective" from the cell in Column A, and copy that same cell into cell C1.
It should then take the next 4 rows below it, and copy each row to E1, F1, G1, H1 respectively.
It should then go to the next occurance of Detective, and continue this process copying into C2, E2, F2, G2, H2 like mentioned above etc.
After the loop is over, It should look for the word "PO BOX" in Column F, if it does come up, lets say in Row 2, it would cut cells E2:H2 and paste into D2:G2
This is the data all in Column A, each line seperated by rows: ...
When I record a macro I use the paste down feature, but in the code it just sets the paste down to the last cell and sets that number in the code.
What do you put in the code so it finds the NEW bottom of the column when the columns get longer or shorter? I tried recording in relative reference and that did not do it - what's the trick?
I Have a row witch contains some numbers…..some of them are zero. I want to look: column after column throw this row and find first not zero item. For example:
A B C D E F
0 0 0 12 3 0
Result is “D”
BUT!
U want to make it with standard excel functions, without programming any user functions with VBA! Only Excel…
transpose raw data to column macros ...
View 9 Replies View RelatedI'm making excel macros for the first time and getting things figured
out, but I have a few questions:
What code do I use to delete columns that have all empty cells EXCEPT
the top cell (row 1) that contains any of a number of headings? I need
to insert this into an existing macro.
What code do I use to fill color every other row (preferably even numbered)
light grey after selecting a "currentregion" ? Again, to put inside an
existing macro.
A related question: When I find code help on the web, it usually
starts with "sub" and ends with "end sub" so how do I insert that into
an existing macro? It looks like a "sub within a sub" doesn't work.
I am totally new to Macros. I need a Macro which should format a column based on the value of another column.
Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?
I am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do
-In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11)
-I want to create one for every number in the column
-Then I would like to copy my 'Template' tab to each new tab.
-From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom'
-Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
I feel like I am losing my mind here. I have a basic SUM calculating a bunch of cells. I have done the SUM from th autosum feature as well as, just typing it is (=SUM(B1:b16)), It totals up the cells correctly, but, when I change a value in one of the cells, the sum is not updating? I have never seen this happen...Does anyone know what could be causing this?
View 3 Replies View RelatedIn my Excel 2007 I have downloaded an Add-in (Webiress data) which allows me to receive RTD of a particular security which I then use to plot a chart. In a day there are around 15,000 changes in prices of that particular security.
The problem is that the RTD misses many prices when the market is fast and also it frequently (around 20 times per day) stops updating and then I have to clear the cell where the RTD is updated and it returns to normal, updating the prices for few minutes and then stop again.
A friend of mine told me that I have to change one Registry in Excel to tell Excel to check for a RTD every 2 miliseconds. I found this suggestion a bit strange and I have no idea how to do it.
Has anyone ever come across a RTD issue like this?
I contacted the server provided (webiress) and they said there is no problem with the updating of RTD (real time data) and the problem should be on my side.
Is there a way to insert a cell/row, and add data to one page/tab, and have it automatically insert a cell/row and add the data on another page/tab?
Excel 2003 is the version I am using.
I update graphs/charts everyday. I've been looking at ways of updating a lot of charts in 1 action.....
What would the formula be to have the range of the chart (ie =Data!$BS$1188:$BS$1201) to get the row numbers, not the row, from numbers entered elsewhere on a data sheet (ie =Data!$BS$1188:$BS$1201)
As a lot of the time I use the same number range but on different rows and sheets.
I have created a series of numbers using a column 'n' and 3 cells for Start value, End value and increment, this is thwe formula: .....
View 9 Replies View Relatedif it would be possible to update all the links in a workbook at once. For example, say I have a worksheet which is linking to a file named something like 4-6-08 (1) and 4-6-08 (2) and so on with the number in brackets being from 1-21. Now say I have the same thing for 4-7 and 4-8 and 4-9. Now I want to change the links to point to 4-10,11,12,13 instead of 6,7,8, and 9. But still there are 21 copies of each sheet. Is there any way to change all the sources at once so all the 4-6's could become 4-10's and 4-7's to 4-11's?
View 12 Replies View RelatedI have made a series of of udf's and used them without a problem in my worksheet. One of the variables used by udf's, " commission " is a named range on the "vrs" sheet in my book. All was working fine, all the udfs updated whenever i changed the value of "commission" by directly entering a new value in the cell referenced by "commission" I then wrote sub to call an input box wich collected a value for commission and put it in the ranged cell. Since then, of the 4 columns of data that commission acts on only 3 now update automatically. The recalcitrant 4th column will only update if I select the cell, place the cursor anywhere in the formula and press enter.
View 3 Replies View RelatedI have written some code that populates a list box with data, and have allowed data to be deleted from the list box if a line of data is double-clicked, with the data below the deleted row being updated to remove the empty row, etc. All works fine except that, when I force the list box's 'selected' line to be the one below the last entry (as if you were selecting the next empty line with the mouse) and the code completes, you cannot click on the form and move it around, nor can you access any other of the form's controls, until you click inside the list box.
Apparently, forcing the line selection in the list box (to simulate someone clicking on a row in the list box) is what's causing the problem. I need to force the selection because the number of data lines in the list box is more than can be shown in the list box, therefore I need a method to automatically scroll the list box to show the next available line in the list box.
I have the folowing vba function that i use to check the existance of files but when the workbook is refreshed, the formula doesn't update. It's only updated when i double click it's cell.
View 2 Replies View Related