I am basically doing some housekeeping and trying to reduce my number of code lines.
Can this bit of code be done a bit simpler?
For r = lrow To 15 Step -1
If Range("N" & r).Interior.Color = vbRed Then
Range("N" & r).Interior.Color = vbCyan
Range("N" & r).Value = "Triangular"
End If
If Range("V" & r).Interior.Color = vbRed Then
Range("V" & r).Interior.Color = vbCyan
Range("V" & r).Value = "Triangular"
End If
Next r
I am wondering if there is an easier way to look up values in a matrix. I use the VLOOKUP with the vertical values as the lookup_value, then another lookup for the col_index_number to find the horizontal values. The example in the attached file is just a sample of my spreadsheet. I need to apply this to a roughly 10*10 matrix (which changes every now and then) and apply this matrix to over 1,200 rows. Is there an easy way to returne theses values or is the way I am currently doing it the best option.
I am using VBA to enter customer information into a Microsoft SQL database. I have an excel sheet with thousands of customers listed. I will be using a While loop to process each line of the sheet. Part of the While loop needs to make any format changes necessary before the data moves over to the DB. So here is the issue:
Into a uniform layout? I would prefer ###-###-####
Each of the numbers above is an example from the list I have. There may be more variations (I have over 20 seperate lists to process, with over 10000 customers per list)
I imagine I need some process that will remove all character except numbers and then break the numbers up to add the hyphens back in at the appropriate places.
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).
I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!
I frequently use the record option to start off my macros. I realize this usually results in recorded events which are not absolutely necessary (recording a print macro is one that is full of excess line items). I then try and combine what I have recorded with what I have garnered from this wonderful website. If my spreadsheets are small - and I am not writing code to pass any inspections - is there a need to go back and clean up?
However I have survey data results and in one of the cells it has multiple values which are separated between ; and some are not separated at all e.g B&Q; The Range; Wicks The Garden Shop
Also there are spelling mistakes everywhere and variation of the word B&Q e.g b+q, B n Q
I need to add count up all of the B&Q, Wicks etc...
I have an old customer list maintained by my company's previous owner in excel format that mostly stored numbers ###-#### format, but also some in ###-###-#### format.
My new list has always been maintained with numbers in (###) ###-#### format.
I have merged these two lists in a new excel file have begun the tedious process of cleaning up duplicates and bad data.
I have used conditional formatting to highlight duplicate numbers in the appropriate columns (L, M, N, O) in this case. However, it does't find matches in the other formats or perhaps the number with the old area code. I know the area code change could lead to false positives, but they should be minimal as this is a small business with mostly local customers. So what I'd like to find out is a formula for conditional formatting that will compare the last 8 characters in each cell (###-####) so as to eliminate issues of missing or different area codes. I would like it to check all of the columns for duplicate numbers not just within each column or within each row individually.
Also separate but related if possible to highlight a different color any cells with data not in any of these formats or if not in (###) ###-#### format if it's too much for all the formats.
Is there a formula where I can take a zip code, and for any zip codes that have the 4 digit zip code extension, to remove those 4 digits, and to add a zero to the beginning of any zip codes where the zero was cut off. Is there a formula I can run my zips through to do both of those tasks?
I have several time series in excel pulled from a datasource on a daily basis, however some series include weekends, some dont and some others have a few days missing. What I did is I already have a column set up with only weekdays on the other sheet, what I am trying to do is match the data with the dates.
The obvious choice is vlookup, but given that I have over 50 time series and the time span is over 10 yrs, its not the optimal solution...it will crash the sheet i think. I also tried a combo of match and index...but same...too many formulas. I think I need a macro. Does anyone who analyzes time series have a "cleaning" code that he oe she can share with me?
I have a worksheet that contains lots of value. I want to run quick calculation on it but for some reason counta function counts blank cells.
I checked and the cells with =len() and it returns a number > 1
What is the proper way to clean the whole worksheet with some copy paste value and get rid of invisible character like space or nonprintable ones to be sure to get the right number of cells with value?
My problem has to do with an list of over 22,000 addresses (don't want to think about going through it manually )
The names are in column A and the addresses are in column B. The problem is, is that some of the addresses fit in one cell and others are spread over two (Oklahoma City, OK in one cell, 73034 in the one below it, for example.)
The names with corresponding addresses taking up two squares have been placed in merged cells, so that they line up with their addresses. Here is a picture below.
Capture.PNG
What I need is each address in one cell next to its address in one cell (to make it searchable, etc.) I thought I was being really clever when I copied Column A, then pasted the formatting onto Column B (so that the cells would be 1:1, one name cell, one address cell) but didn't realize I would be losing the information in the cells being merged.
I have a set of (imported) data consisting of 1000+ rows. The data set (bank statement) contain six informations (date1, transaction, vendor, date2, sum1, sum2) in one cell which has to be split into six columns.
Using text-to-columns will generate more than six columns since the set dosn't have any delimiters and both the transaction and vendor name-fields contain blank spaces.
Is there a way to either split the data into the correct number of columns or if text-to-columns is used - a way to manipulate the data into the correct columns, for instance with help of filtering ?
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
I have an excel file having part code,name,vendor and Qty ( Quantity ).
My problem is that I want to apply an excel formula to pick up that vendor code who have highest Qty of a part code.The condition is that S.No.should not be disturbed.This file is so large,but here I have taken an example,
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Public IE As Object Sub Sample() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = False Then Cancel = True
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
Is there a standard way to organize sections of code with code tags? Due to the nature of my work, there is a lot of documentation involved. And, my spreadsheets rely heavily on VBA. These code tag sections will be going into a design specification, so that there is a layer of traceability from the document to the program. These are the general sections or item numbers that I've come up with.
1. Dimensions and Variables
2. Data
3. Processing the Data
4. Rules of analysis
5. Analysis
6. Formatting
I guess I'm looking for something standard like UML diagrams for DFDs, but with tagging code to break them up into sections?
I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent.
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water SW = Saline eater CW = connate water MW = Meteoric water
numeric code as follow
FW=1 SW=2 CW=3 MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
