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]....
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...................
I want to find the row position in an array.
1 200 CAR 001
2 230 CAR 002
3 400 TRUCK 001
4 200 BUS 001
5 250 CAR 003
6 250 BUS 002
7 200 BUS 003
8 730 CAR 004
in 4th column it should say that this is "n"th position of 3rd column value.
ex.
in 4th row 3rd column 1st "BUS" appears. hence column 4 is 1
in 6th row 3rd column 2nd "BUS" appears. hence column 4 is 2
in 7th row 3rd column 3rd "BUS" appears. hence column 4 is 3
tell me how to automate this
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.
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. :-)
View 9 Replies View RelatedI'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.
=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})
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.
I am trying to copy or edit a cell thats has the following formula (see below), I keep getting this message "You cannot change part of an Array!"
=If(ROW($A2:$A8)-ROW(A2)+1>COUNT(W2:W8),"",INDEX($A:$A,SMALL(W2:W8,ROW(INDIRECT("1:"&ROWS(A2:A8))))))
I have a column of numbers. (column U)
I have a column with Yes or No (Y/N) (column R)
I want to total up numbers in column U that have a Y in column R.
I can change y/n to 1 or 2 if that is easier (1=yes, 2=no)
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.
View 5 Replies View RelatedHow to change part of an array value?.If i try to change array value "Microsoft Excel" popups a dialog stating "You cannot change part of an array."
Somebody have mention to use "Ctrl+Shift+Enter" but this will change entire array value.So I need a change only a specific cell value.
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.
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.
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.
View 7 Replies View RelatedI'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.
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.
View 10 Replies View RelatedI 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
Here is the code I'm working on:
I want this code to replace the value of s with the value of a cell.
sub Macro2()
Dim s As String
s= value of (cell F2)
Range("I8").Select
ActiveCell.FormulaR1C1 = "='[(s).xlsm]Payroll Computation '!R8C11"
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?
I need a macro in a excel for the below scenario:
1)i have merged cells from A1:B3
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
2)Output should be random....
script to replace consecutive data like this:
(RX 40.1.22) (RX 40.1.23)
with this:
(RX 40.1.22-23)
I'm trying to get rid of repetitive references in a Worksheet I have. These references have "" instead of "()" but the won't show up in the post.
i want the formula to be used in excel to make acounter in another column for the values the occured repetitively
like to have these two column.
ex:
Oper No
Work Center
10 Paintsh
20 Paintsh
30 Paintsh
40 Paintsh
50 Paintsh
60
Paintsh
70 Paintsh
80 Paintsh
90 Paintsh
here the operation no is the counter for the repeated work center whose name is paint sh