Coding Multiple Cases In VBA
Mar 24, 2009I am writing VBA code to do the following:
IF A1 > 50 then print array C1:E7
I have this code and it works.
ADVERTISEMENT
Single Coding Line Into Multiple Using Underscore
Feb 10, 2010Single coding line into multiple using underscore
How can i break this up
Coding A Userform To Create Multiple Entries
Mar 18, 2008I've been trying to code this user form so that when the appropriate command button is pushed, the Inventory Number is populated the amount of times indicated by the "multiplier" number.
I haven't gotten very far successfully. Mostly runtime errors.
Here is a screen shot of my table, along with my non working code.
Private Sub cmdfront_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Scanned")
VBA Coding - Macro Moving Data With Multiple Variables?
Jul 17, 2013I'm working on a complex spreadsheet and I'm working on a complex spreadsheet system for pulling and measuring data. My VB programming skills are about minimal/average, so you may see me on here asking various questions . In any case, what I'm trying to do is create isolation macros for "Kickback" data (erroneous). I'm trying to remove data with certain criteria and isolate it on a separate "kickback" sheet for one for taking a second look at. I've made the easy macro of creating a new spreadsheet:
Sub Create_Kicbacks_Sheet()
' Create_Kicbacks_Sheet Macro
' Creates "Kickbacks" sheet for invalid information.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Kickbacks"
Sheets("Kickbacks").Select
End Sub
This coding works correctly. The problem area I'm finding is the sorting data. My goal is to look at Columns A and B for certain criteria and either leave it alone, move it to the "Kickbacks" sheet or delete (due to not being necessary in data calculations). Basically, here's a synopsis of what I'm looking for:
if Column A = Y and Column B = Mandatory -> Leave Alone
if Column A = Y and Column B = Best Efforts -> Move Row to Kickbacks
if Column A = Y and Column B = Empty Cell -> Move Row to Kickbacks
if Column A = Empty Cell and Column B = Mandatory -> Move Row to Kickbacks
if Column A = Empty Cell and Column B = Best Efforts -> Delete Row
Here's the code I have in excel (modified from one I found online)... Which only is doing some of what I want it to do:
Sub Moveto_Kickbacks()
Dim r As Range, LR As Long
With Sheets("Data")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Set r = .Range("A2").Resize(LR - 1)
.Range("A1").AutoFilter field:=1, Criteria1:=""
.Range("B1").AutoFilter field:=2, Criteria1:="Mandatory"
[code]....
Coding For Offset Cell References With Multiple Row Headings With Merged Cells
Jan 4, 2010how to code to specify a cell in which to enter data into a spreadhseet, when the heading contains only one row.
In the attached example the headings contain multiple rows with merged cells, is there any way of overcoming this so that the entries are placed in the correct cells?
Calculating Age Of Open Cases
May 27, 2009I've spent some time looking through previous threads and found some help, but can't quite get what I need.
I am just trying to look at the age of open cases in a report. I want to know in hours (and maybe minutes) not including weekends. I have the created date and time in J2 and "Now" date and time in K2.
I've used the following that I got from another thread: =NETWORKDAYS(J2,K2-1+MOD(K2,1)-MOD(J2,1))
But it seems to be only giving me hours in 24 hour increments. For example a case raised on 22/5/09 gives a time of 72.00 this morning.
Matching Cases And Data?
Oct 14, 2013I have data collected at 2 time points. Participants have a unique alphanumeric ID that is the same at both times. I need to merge the data into one sheet where each case has all of the information from time 1 and time 2. There are many more cases at Time 1 (nearly 2000) than at time 2 (around 340).
I have prepared an example of what I need (see below), but have lots (around 300 at each time point) more variables than what has been put in the sheet.
Example.xlsx
IF Function For Last Occurring Unique Cases?
Mar 26, 2014I have an excel file that lists individuals who came into the clinic on a given day. I am interested in figuring out the amount of unique individuals in a given month/overall. As this is a working datasheet, I would like a formula that would automatically update this information for me. Here is an example of the file that I am working with: unique case example.xlsx
Specifically, I want an IF function that could place an 'x' in column E depending on if the name in column C is a unique instance. But if the name appears several times in the list, I would also like this function to place an 'x' in column E next to the last occurring unique instance. For example, the name 'Hanna D' occurs 3 times in the spreadsheet I have attached, but I want the 'x' to be next to the most recent incident, when she was screened, as opposed to when she was not contacted (missed/doc forgot).
I am not sure if you can even define a unique case in the IF function, let alone the last occurring unique case.
I am not interested in conditional formatting (highlighting duplicate cases and then placing an 'x' next to the most recent highlighted case) because my spreadsheet has 300+ individuals and it is very time consuming to manually move the 'x' when the same individual appears at a later time.
Multipal IF Formatting Cases In Presize Coloum
Nov 29, 2009I have one excel file. I want calculate no of days in coloum ( L16 & L17)
If Coloum C3 is 400 & C4 is NORMAL then automatically calculate no of days in L16 & L17 as per J16, J17 (hrs) & K16, K17 (running). we highlighted in yellow. refer attach file for ur ready ref.
Examine Random Numbers Of Cases Every Week
Feb 25, 2014I have to examine a random number of cases every week. I used to have a little app that had a spot to ask me how many random numbers I need. So lets say this week I need to examine 15 cases, I'd enter in 15. Then it would ask me a range for the random numbers. So, lets say there are 250 cases, so I'd type in 250. Then it would spit 15 unique random numbers in sequential order. So, then I could look at me list of 250 case and review those 15 that the random number generator spit out.
Well, we just upgraded our computers to Win 7 and that little app does not work anymore. I've been playing around in Excel, and I've gotten pretty close to getting something thrown together, but I can't get it to work just right...
Find The MODE Of Cases/pallet By Item Number
Aug 28, 2008One has the master data in it ( Item #Lot#/Cases per pallet/date ) = 30K rows
Second is the list of item #'s .
I need to find the MODE of cases/pallet by item number.
Excel 2010 :: Convert Eaches To Case And Cases To Eaches
Nov 8, 2012I'm using excel 2010, WindowsXP.
I'm trying to create a table which coverts eaches to cases and cases to eaches depending which value is entered.
For example, if the warehouse associate fills in the EACH column, with 24, the CASE column will show 2 (using 12ea/case). If instead the warehouse associate fills in 2 in the CASE column, the EACH column will show 24. The idea is that the associate can fill in either value and it will convert. The problem is that I'm getting a circular reference error.
A1
B1
EACH
CASE
24
2
=B1*12
=A1/2
VBA Coding
Mar 18, 2008I am trying to code a macro to run down through a column and hide the row if it finds a cetain marker (i.e. the letter "n") in the column, and then move onto the next one.
View 9 Replies View RelatedCoding Months
May 22, 2007My spreadsheet calculates the date on a daily basis.
I would like to create a code in my spreadsheet which uses the year and month in question.
First, 2006 is is designated as a "3"
2007 "4"
2008 "5"
2009 "6" etc
Don't worry about anything prior to 2006.
Next, the months are represented by single digits. So, January = 1, February = 2, March = 3, April =4, etc. September is 9, and October = 10 but all the numbers over 9 have to be reduced to a single digit, so October = 1+0=1, November =1+1= 2, December =1+2= 3.
Now, the formula for the Month is:
Year + current Month
So, May 2007 = 4 + 5 = 9
June 2007 = 4 + 6 = 10 = 1
July 2007 = 4 + 7 = 11 = 2,
January 2008 = 5 + 1 = 6, etc
Coding A Macro In VBA
Apr 3, 2008Is there a way of coding a macro in VBA that allows you, with one macro, to enter text in one cell and at the same time enter a value to the cell on it's right?
I don't want to name the cells, but I wondered if you could add to the code a piece of code that basically said "Select the cell to this cell's right and add the value x".
Optimizing Coding
Jun 3, 2008I have made this vba to go out and look at 2 different locations which contain simular folder names.
The script then writes the folder names in Column A, followed by the Size of both the folders from the 2 locations. The script is comparing the second location folder name to what the first process put in Column A so the folder names will be aligned with the file sizes. It is also converting the file sizes from bytes to the most appropriate one.
The problem I am having here, is once I put the second part in for the second folder, the script is either taking forver (More than an hour) or is freezing. So I am not sure if I put it in a loop somewhere or what exactly is happening. I am just wondering if I can get some help troubleshooting and optimizing this code....
Lock Vba Coding
May 1, 2007How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"
View 9 Replies View RelatedDropdown Box Coding To Another Cell
Dec 24, 2012I have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.
View 2 Replies View RelatedCoding To Order Stock
May 8, 2014I am looking to create coding that once stock gets to a particular level a text box pops up to alert the user that more stock needs to be ordered and ideally I would like once the user selects ok for it to take them directly to the appropriate email template.
However, I don't want the text box to come up while we are waiting on the stock to be ordered.
i.e our current minimum stock level before placing our next order is 10,000 units. Order is placed and can be received within 10 - 14 days. During this time our 10,000 units will be used but I don't want an alert to pop up to remind the user to place an order as this action will already have been carried out.
Dynamic Range In Vba Coding
Nov 2, 2008I'm trying to write a code which would place formulas in the row just below the my range (normally a matrix of figures) to sum the column in that just above that. Here's what I've got so far but it doesn't work.
View 6 Replies View RelatedDeactivate Coding Not Working?
Sep 3, 2009I have placed the following code in my spreadsheet but it's not working!
the code after option explicit (now at the bottom) works, (thanks to Andy on here!) but all the disabling the right click and cut/copy/paste etc doesn't.
Coding For Changing Worksheet Name
Oct 26, 2009when the accounts exported to excel, all the tabs shown in the file are generated. However, i need to change tab '3' to tab '10' name to the name stated in tab 'menu'. eg tab 1 need to be renamed as 'BB' (not BB/Bunut), tab 2 to 'GEN' (not GEN/General), etc. All must be capital letter.
View 5 Replies View RelatedUsing Index And Match In VBA Coding
Jun 3, 2014I have table where i have to find a value based on 2 criterias. So i have used in Excel the below formula:
=INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7
Can You Use VBA Coding To Sort By More Then 3 Criteria
Nov 28, 2006I was wondering if there is a workaround for sorting in Excel by MORE THEN 3 CRITERIA?
I have seen a couple of solution doing 2 seperate sorts but was wondering if you can write code to expand on Excels limited sorting capabilities?
Color Coding Formula
May 14, 2007i'm trying to figure out a formula that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
Macro - Color Coding
Jun 13, 2007I am trying to figure out how to run a macro for 1 particular workbook in an excel spreadsheet. I don't want it run on any of the other workbooks in that file, just the 1. I can't use conditional formatting because I need more than 3 values (if statements).
This is what one example of data in a cell and below is the color I'd like it to turn when I run the macro:
T: 6/1/07
A: 6/8/07
Anything that has a A: (which means an actual date it happened) I'd like the cell to turn blue. If there is an RT: (which means revised target) I'd like the cell to turn red which means it missed it's target date, and has been revised. If it has a TBD I'd like the cell to turn pink (or yellow or any color really). All other cells are just white. I don't know where to find the color codes in excel as well. Other samples of what cells look like are below.
Here is another example:
T: TBD
And final example:
T: 6/4/07
RT: 6/15/07
Vba Coding For A Simple Clock
Sep 15, 2008where can I get vba coding for a simple clock.
I would like the clock to be digital rather than analogue if possible
VBA Or Other Coding For Looking Up File Date
Dec 15, 2008I am trying to learn how to use it in some of my Excel application. I am just wondering if someone would be willing to help what I am thinking would be a short string of code.
What I have is a list of files that I have compiled along with a name and description in cells B3 and C3 respectively. In cell E3 what I have is the path to that file which is linked so that I can click on it to access the file. Is there any way that I can write some kind of simple code to look up the last modified date of that file whose path is specifed and place it in cell D3.
Colour Coding Cells
Jun 27, 2006Is it possible to select a number of cells that are say yellow when blank but once someone type "N" or "No", they change to blue and if "Y" or "Yes" they change to green. I tried conditional formatting but it does not change the blank range to yellow until I have entered something into it.
View 6 Replies View RelatedMost Efficient Coding Method
Apr 25, 2007In each VBA book I've read the authors will write their code like this:
nStart = UBound(GenArray1)
nEnd = UBound(GenArray2)
Redim preserve NewArray(nStart To nEnd)
Instead of the less line method of:
Redim preserve NewArray(UBound(GenArray1) To UBound(GenArray2))