Easier Way To Shorten Up Part Of Code That Is Repetitive With Array
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
I have over 500 labels in a userform and each labels content is gathered from an workbok like this:
Private Sub UserForm_Initialize() CommandButton3.SetFocus Application. ScreenUpdating = False Workbooks.Open "V:allaBeredningSemesterstänging Medleverantörer och Verkstäder.xls" Sheets("2006").Select ' Sheets(Year(TODAY())).Select LabelA1 = Range("A1")...............
I have shortened the code and I haven't yet written all labes. The ... is supposed to mean "from" and "to". I don't want to write over 500 lines of the label codes. So what is an easier way to write it?
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.
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,
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. :-)
so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.
Is there a way to set an array equal to part of another 2D array. If say I have an array with 5 columns. Is there a way to break that up in to two arrays one with 3 and one with 2. Even getting it to 5 1D arrays would work. I bring it in as 1 array but I need the information in different places. This is actually only the output. So if there is a way to send only part of the array out that would work to. I know you set the range smaller than array with range=array, but that still doesn't get me where I need to be. This is obviously without simply going through a loop. I could set it to 5 singles with a single nested loop I know. I was looking for a non-loop way.
I have a multicolumn listbox, and a multidimensional array.
I want to put just SOME values of the array into the listbox, but I ger errors using both .list or .column to access single items, and even using .additem .
I have: dim variable(2000,2) as string variable(1,0)="aaa": variable(1,1)="bbb": variable(1,2)="ccc" variable(2,0)="www": variable(2,1)="awasd": variable(1,2)="asdfa" .... variable(2000,0)="www": variable(2000,1)="awasd": variable(2000,2)="asdfa"
I don't want to store all 2000 elements, just some ones: how can I do it?
I'm trying to convert some data from MLS to make it easier to work with statistically. The raw data regarding Bath count downloads from MLS in 2 columns; 1 column for Full Baths & 1 column for Half Baths (see Raw Data.jpg). I want to concatenate the baths to one column and, if there is a 1/2 bath, tack ".5" to the end of the full bath count.
The problem with my formula is that if it concatenates the ".5" to the full bath count number, it converts the format to text (see Current Formula.jpg). I know I can go through later and convert it back to number format but is there a formula I could use that would keep it in number format?
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
I am tasked with putting together an Excel Spreadsheet to analyze our accounts. There are over 80 items for the accounts - about 20 Income and about 60 expenses. These are for various years - actually the last 5 years - by months.
So, I am trying to figure out how the best setup could be done to make this easier for pivot table analysis.
So I have 12 "rows" for each account item for each year.
Maybe I should wait for comments and/or questions before going further.
Just need to figure out how to set up the workbook/worksheet.
I understand that we can protect the VBA coding through check the "Lock project from viewing in the Tools>VBAProject Properties. However I'll still need to open some of the code for other users to customize the program while some of the important code are protected.
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 an excel sheet where i enter company information including Zip code. the zip codes around here all start with 797 and end with numbers from 01 to 12
i am looking for a way to enter just the last 2 numbers and have it automatically insert the full zip
example:
line one i would enter 01 and 79701 would show up in the box.
I know there are ways to do this, just can't remember its been so long since I've had to use excel.
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows: =VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0) but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following: =VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE) The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
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'm an entry level Excel user who is looking for a spread sheet to print multiple pages for tagging pallets in a warehouse. The pallets will contain the address of the customer and the number the pallet is.
Example 30 pallets would be 1 of 30 then print 2 0f 30 then print 3 of 30 then print
how I can put in a varible pallet total and have it print the all sheets up to the given number?
2)if i click on a macro the output in the above cells should be as follows"
"Login to database"
3)if i run the same macro for the 2nd time the output should be:
"Are you done with the reporting?"
4)if i run for 3rd time the output should be:
"Logoff from database"
5)Say example if i run the same macro for 1o times i should get 10 different outputs....if i run for the 11th time the output should get repeat from 1st....
6)Output need not be in order....random also fine...
If possible gimme 2 different codes:
1)Output should be in the order wise from 1-10 and get started from1 again