Shorten Code Typing Methods
Aug 11, 2007
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. :-)
View 9 Replies
ADVERTISEMENT
Jun 26, 2007
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...................
View 5 Replies
View Related
Mar 30, 2007
I am trying to run the following
ChDir _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor"
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor2 Tuesday.xls" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor3 Wednesday.xls" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor4 Thursday.xls" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor5 Friday.xls" _
, UpdateLinks:=0...................
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.
View 6 Replies
View Related
Jun 3, 2013
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
[code]....
View 3 Replies
View Related
Oct 31, 2008
Is there a way to turn off those annoying error message boxes that pop-up when a line of code is incorrect? I pretty frequently go to another area of code to grab some previous code to copy and paste, and the error message box pops-up, so I have to hit OK to close it. It would be nice if it still highlighted the problem red, but I'd even give that up for those boxes to go away.
View 5 Replies
View Related
Oct 8, 2012
I am not sure the correct term for this but the autofill drop-down menu that pops up when typing code does not come up for me.
Example:
Sheets("Form").combobox1.
I usually get a drop-down menu with a list of members of the "ComboBox" class.
Is there a setting for that enables/disables this?
View 7 Replies
View Related
Jul 28, 2008
My formula is:
=IF(E4="Break",D4,IF(H4="Break",G4,IF(K4="Break",J4,IF(N4="Break",M4,IF(Q4="Break",P4,0)))))
My problem is that excel only lets me use 7 IF functions and I need a total of 12. So I need a different way to perform that same function.
View 13 Replies
View Related
Feb 22, 2014
These workbooks are located in different folders.
The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.
{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))
What is the difference between using these two different methods?
View 1 Replies
View Related
Feb 10, 2014
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):
Field1[if1]
Field1[if1].Field2[if2]
Field3[if3]
Field2[if2].Field4[if4]
a couple of notes:
- you can have field 2 without field 1, etc.
- 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.
View 3 Replies
View Related
Apr 10, 2013
I am developing a VBA for Excel application which uses transient worksheets to collect data from users before writing the data back to master spreadsheets.
My application opens an instance of excel with a workbook and worksheet using the following code:
Dim xlsInstance As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set xlsInstance = CreateObject("Excel.Application")
Set wkb = xlsInstance.Workbooks.Add
wkb.Activate
Set wks = wbk.Worksheets.Add
wks.Activate
I can reference various ranges in the worksheet and do most of what I need but I need to override the Worksheet_Change method. Easily done for static sheets, but this must be set at runtime when the worksheet is created.
View 9 Replies
View Related
Sep 4, 2013
Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.
Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)
[Code] ........
I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb
Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]
View 8 Replies
View Related
Feb 28, 2014
I have thousand rows of data in the following format:
Despatched Time
Batch
Date
Time
Amended Time
20/4/2013 3:45:00
1
20/4/2013
03:45
[Code] ..........
'Date' is generated by =IF(A2="", "", TRUNC(A2))
'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))
And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.
I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula:
=IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))
The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).
Despatched Time
Batch
Date
Time
Amended Time
20/4/2013 3:45:00
1
20/4/2013
03:45
[Code] ..........
View 9 Replies
View Related
Feb 1, 2013
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.
View 3 Replies
View Related
Jan 25, 2010
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.
View 10 Replies
View Related
Mar 7, 2013
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
View 7 Replies
View Related
Jun 18, 2006
I am trying to compare two workbooks and am running into the problem that since I declared my two workbooks, I am limited in the methods that I can use. Here is all of the code so far.
Option Explicit
Sub UpdateMasterFile()
Dim wbMaster As Workbooks
Dim wbEmailed As Workbooks
Dim wsPC As Worksheet
Dim Master As Long
Dim Emailed As Long
Dim intMaster As Integer
Dim intEmailed As Integer
Set wbMaster = Workbooks("Master Info.xls").Sheets("PlantsCom")
Set wbEmailed = Workbooks("EmailedData.xls").Sheets("NewInfo")
Master = Workbooks("Master Info.xls").Sheets("PlantsCom"). Range("a65536").End(xlUp).Row
Emailed = Workbooks("EmailedData.xls").Sheets("NewInfo").Range("a65536").End(xlUp).Row..............
View 2 Replies
View Related
Aug 12, 2006
I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.
I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.
Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop
Does this funtion also exist in excel?
View 6 Replies
View Related
Aug 16, 2007
I need to learn the syntax and all options I can use with the Selection function - where can I find that info? I can't find anything in in Visual Basic Help (I find microsoft help is generally totally useless).
For example, in the code below, what are all the options I can use after "Selection. " (ie .Copy, .End, .Select) and what do I use if I want to select a certain number of cells to the left or to the right or up or down?
Sub Fill()
Selection.Copy Destination:=Range(Selection, Selection.End(xlDown).Offset(-1))
Selection.End(xlDown).Select
End Sub
I'm just noticing now that the "(xlDown)" is X L, not X 1, which I thought might make sense to say "1 time Down". What does the "xl" stand for?
View 7 Replies
View Related
May 27, 2014
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
View 6 Replies
View Related
Jul 19, 2014
I am doing a spreadsheet where I have 1 main sheet and 2 sheets with data.On the main sheet I have a drop down list to select either one of the 2 sheets and upon selecting,all the data from that selected sheet will be displayed on the main sheet.
What i do not understand is the code given below
data:=IF($C$7='A2'!$A$2,INDEX('A2'!$A$6:$B$20,ROW( $A3),COLUMN(B$1)),
INDEX('A1'!$A$6:$B$20,ROW($A3),C OLUMN(B$1)))
How do i attach spreadsheet in this forums btw?
View 7 Replies
View Related
Feb 11, 2009
some of your favorite Methods/Properties exposed by the Range Object. How's bout some that you just can't live without?
Have an tricks for example that you can do with XLS03 that for example couldn't do with XLS2K or perhaps XLS07 that you couldn't do with XLS03?
View 9 Replies
View Related
Apr 9, 2007
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............................................
View 7 Replies
View Related
Mar 27, 2014
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?
[Code] .....
View 2 Replies
View Related
Dec 24, 2012
When I'm doing cells in a column, there are times where I start a word and it tries to complete the word based on what I wrote before. For example, I'll put Base I, Base II, Base III... etc.
How do I get it to add the word base, but then I can continue typing the numeral after it? I do not want to create another column after with the numeral. So as I type B, A, [click certain button and continue typing] II
View 3 Replies
View Related
Jul 12, 2007
On a sheet of my workbook, I need to allow typing but not pasting. I'm using Workbook, because I'd just as soon not allow pasting anywhere.
If the data is copied from within Excel, this seems to work:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub
but if the data is copied from somewhere outside of Excel, it pastes it anyway.
View 9 Replies
View Related
Nov 14, 2006
I'm in need of a macro that takes data from cells in column A and moves them into different cells. Each block of data includes a company name, contact name, address and usually (but not always) phone, fax, email, website, etc. I want to move each set of contact info into a set of cells that begin in column A, starting with company name. refer to the attached spreadsheet
View 5 Replies
View Related
Sep 23, 2007
Is there anyway to type accents in Excel the same way you can do in Word,
for exemple ctrl+' before e gives é
I am using the Language setting "US and Western Europe" for Office Language settings.
View 14 Replies
View Related
Jun 10, 2009
How do I keep users from writing data into ComboBoxes on a UserForm?
View 4 Replies
View Related
Mar 1, 2007
I'm working on an Excel spreadsheet and basicly i'm inserting manually data from a lot of paper forms.
It isn't a dificult thing, but is boring, because I have to do more than 2000 forms per week!
I want to accelerate, and how to do that
Problem: "Address"! The Excel page that i'm working has a column to feel with Addresses manually, but I have a table with all correct Address names from the city that I'm working about in other page.
What I would like is when start typing some keyword from the Address cell, it could show me all the choises of input (based on the correct Address Table)
Excel, by default, show, by exact order, all the choises, already typed on the same collumn.
I don't want that.
- imagine I have the Table 1 (representing the table with all city adresses) with a collumn called 'Address' with:
A1 - Street ZXY
A2 - Avenue QWE
A3 - Street QWE
A4 - Avenue ZXY
when typing on another Table, if I write something like “QWE” show me the A2 and A3 to choose wich I'm going to feel, and the same to “ZXY”, showing the A1 and A4. Other thing, if I write “QWE street” may show A3 also. Keyword is what i'm talking about
View 9 Replies
View Related
Aug 11, 2007
Is there a way I can set up the worksheet so that in one cell I can type out for example product codes/numbers and by doing the description for the product will come up in another cell. Rather then copy and pasting very the many different product descriptions?
View 2 Replies
View Related
