it counts data in columns 4 to Endxlright and then creates a graph based on the status. I'm sure there is redundant code in there but can't seem to shorten it myself.
Option Explicit
Dim vEndCol, vTotal, nFail, nOth, vRow, vCol, nExc, nSum, n, x
Dim Mth As Integer
Dim Cell As Range
Dim vMth, vstMth, vstart, nOK, xRow, vStatus, wks, Chart
Private Sub Calcs()
Sheets("Report").Activate
'Clear previous data and formats
Range(Cells(2, 2), Cells(51, 30)).ClearContents
Range(Cells(2, 2), Cells(51, 30)).ClearFormats
Sheet1.Activate
'Identify last row of data
Range("IV8").End(xlToLeft).Select
vEndCol = ActiveCell.Column
'variables for all data...................
It worked for a short time but now I am having problems running it. I get an excel error that says excel can't continue. Someone said in a previous post that this could be cleaned up and consolidated. how to clean this up. It took me forever to get this right.
Instead of typing out my code hundreds of times, I would like to replace it with an abbreviation or variable(?). Here is my example. If I have this in the wrong place please let me know. I want to replace Activecell.Interior.Colorindex with ACIC. I tried to Dim ACIC as range, variant, string...to no avail. In combination I Set ACIC = Activecell.Interior.Colorindex and that didn't work. I have been toiling with VBA for a year and I am still trying to learn the terminology and understand it so please forgive me if I seem really NOOBIE. :-)
I have a macro that works but was wondering if there is an easier way to shorten up the part of the code that is very repetitive with an array or ? Nothing I have tried works except the code below.
Code: Sub ClearAndReset() Dim WS1 As Worksheet Set WS1 = ActiveWorkbook.Worksheets("Stocks") WS1.Unprotect Application.EnableEvents = False With WS1
I'm developing a spreadsheet that is generating relatively simple source code. As far as excel is concerned, based on columns of data in the first sheet, generate other sheets - conceptually straightforward, but the formulas are getting stupid long. For example, suppose I have 4 columns:
Needs A, Needs B, Needs C, and Needs D.
If there is a "Y" in any of these columns, I need to generate corresponding text that is concatenated together:
=if(a1=="Y") use "Field1[if1]" =if(b1=="Y") use "Field2[if2]" =if(c1=="Y") use "Field3[if3]" =if(d1=="Y") use "Field4[if4]"
The result in some other column could be (samples):
- if there is a trailing field, then I need a period separating the two.
Needing this separating period between some of the columns is driving me bat crap crazy. My approach to this point is to build work sheets that feed the main page, thus isolating the logic complexity. This seems to be the only way to segment the processing into something one can edit.
I have need of VBA code that will check collumn D, starting at cell 7 until cell 206, for when data that is entered starts with 100. When this occurs it will remove all of the data in that same cell except for the last 12 digits.
Here's an example.
When using our barcode scanner to capture a FedEx Tracking number the barcode captures the following:
1001853514360009020200802541997796.
Embedded in that long string of numbers (which are formatted as Text) is the tracking number. Specifically the last 12 numbers are the tracking number. However, FedEx varies this code at times so the last 12 numbers are the tracking number only with the raw data captured by the scanner starts with 100. Until I find additional conditions this VBA code will have to ignore all of data that is captured in collumn D starting at cell 7 until 206.
I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.
I got a list of counties in Texas, but the list is in mixed case and always has a " County" after each one. I got it converting to upper case, and have tried removing the " County" but no luck. Below is the code I have:
Code: Sub TEXAS() For a = 1 To 254 R = "D" & a i = "a" & a ActiveSheet.Range(R) = UCase(ActiveSheet.Range(i)) Next a End Sub
i have some code (see below). at the part where it says
If ActiveCell. Offset(0, 9).Value <> "" Then .Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
i want to make this smaller - i have to write this line bout 60 times 3 times over (incresing the numbers as i go ((0, 9..10..11 etc etc and (Skill_32..33..34 etc etc) )) to give you an idea of what im trying to accomplish - im opening and writing to a database but if the cell doesnt contain any info dont update.
Function EditCPS(ByVal StaffNumber As Long) On Error Goto Err_Handler Dim SkillsBuilderDB As Database Dim RSSkillsBuilder As Recordset Dim MySQL As String Set SkillsBuilderDB = OpenDatabase(Worksheets("Adding Data").Range("IV1")) ' database location MySQL = "select * from CPS where StaffNumber=" & StaffNumber Set RSSkillsBuilder = SkillsBuilderDB.OpenRecordset(MySQL) With RSSkillsBuilder .Edit If ActiveCell.Offset(0, 9).Value <> "" Then .Fields("Skill_32") = ActiveCell.Offset(0, 9).Value Else............................................
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
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 .
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.
1) Macro Recorder Code to Copy One Sheet to Another
I have been trying to track down why this message keeps on popping up, doesn't matter what excel file I open it always pops up, code will halt at different times in the code.
I am looking for VBA code by which the results can be obtained without having to run the code. For Instance, if Z = X*Y, I would like the code to automatically calculate Z for as soon as the value of X and Y are changed.
I have implemented the code for the FuzzyPercent code as a module and Excel will not recognize the function for the cell (=fuzzypercent($C$3,$B5,D$2)). I am using Excel XP.
