Adjusting If -Then Statement In Code
Dec 8, 2009
I am trying to make some additions to this code. Basically what it does is copy some data from another sheet. Now there are two types of copying here.
1. A ‘call value’ from another sheet is copied to this sheet.
2. The formula from the previous cell in the column is copied. (eg. Formula in C3 is copied to C4, formula in D3 is copied to D4, and so on)
So the macro works fine for all cases EXCEPT the initial entry, which will be in Row3, that’s where the formulae begin. So I need some kind of an If-Then statement.. to say if A3 is empty then do the following, If not then do something else....
View 12 Replies
ADVERTISEMENT
Oct 3, 2008
below is some code from a spreadsheet I am working on,,,,,, as you can see it adds items to a dropdown list in a combo box. This may require an update at another time,,,,, is it possible for the list to be updated from a list in the spreadsheet, therfore preventing people from accessing the code
View 4 Replies
View Related
Mar 26, 2014
I saw the below code in another thread that does almost what I need it to do. The only thing is that I need to only select certain worksheets, not all. Is there an adjustment I can make to this code or is there something I can do differently?
Each worksheet has a table on it as well, is there a code I could use to just combine certain tables?
Sub debit1()
'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Dim ws As Worksheet
[Code]......
View 7 Replies
View Related
Apr 5, 2009
Here's a nice easy one for anybody whose used excel for more thaan a week unlike me, I'm using the following code to access a popup calendar:
View 2 Replies
View Related
Oct 17, 2012
I have a worksheet that is automatically updated based on actions in other sheets. There are 10 columns of data (A-J) Headings are on row 6 and data starts on row 8. I have created 3 buttons to sort worksheet by client column (B), year-end column (E) and to bring it to original order (by numbers in column A). I have created the following codes that I have assigned to each button however it does not work.
Sub Macroclient()
Columns("A:J").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
[Code]....
View 1 Replies
View Related
Jul 21, 2009
I am trying to use the formula below in a macro to produce the same result, but I am having trouble with the coding.
View 4 Replies
View Related
Sep 27, 2009
On Sheet1 I have a macro link. On the Setup sheet, in cell C14, I have the name I want to display in the link on Sheet1. The wording is changeable in the Setup sheet. This all works fine.
Now, I want to modify it. I want an if statement at the beginning of the macro that says. If Setup, cell c14 is blank, I simply want the macro to stop and do nothing. If Setup sheet, cell c14 is not blank, I want the macro to continue as normal.
View 2 Replies
View Related
May 12, 2006
I have developed (plagiarized) the following code but it is very slow. how I could speed this up? Eventually, the range may encompass more rows, and there are three separate ranges this code will act upon, so how to speed things up.
Sub DeleteBlanks1()
Dim i, Endrow As Long
Endrow = Range("K2000").End(xlUp).Row
Application. ScreenUpdating = False
For i = Endrow To 2 Step -1
If Cells(i, 11).Value = "" Then
Range(Cells(i, 8), Cells(i, 13)).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
View 7 Replies
View Related
Apr 29, 2014
Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.
Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.
If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...
My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.
What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.
Test.xlsm
View 3 Replies
View Related
Mar 27, 2014
I'm trying to convert my nested if statement below into vba code.
=IF(L27=15,"15+", IF(AND(L27>=5,L27
View 1 Replies
View Related
Aug 27, 2013
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER()
Select Case Range("F4").Value
Case "Medewerker"
Range("S2") = "M"
Case "Interview"
Range("S2") = "I"
Case "Data"
Range("S2") = "D"
Case "Observatie"
Range("S2") = "O"
End Select
End Sub
View 4 Replies
View Related
Oct 29, 2013
I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.
Here is my code
cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear
Set rs = New ADODB.Recordset
[Code] ........
This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.
View 1 Replies
View Related
Jun 18, 2009
Example6.xlsx .i just cant remember what it is im meant to be using as i havnt done it since school. basicly on the attached spreadsheet, there are 2 tabs. the common column is Contract number, if the data in the contract number column matches then i need the figures for 20/40/TEU to show against the Special aggreement by code tab.
View 3 Replies
View Related
Mar 7, 2014
I am trying to create the following function but I cannot seem to get it working correctly.
[Code] .....
View 3 Replies
View Related
Apr 18, 2008
I am trying to figure out a formula for the boss, but I just cannot wrap my mind around what I need to do. I’m sending out an S.O.S.! If you can figure this out, please respond.
=MIN(b4*12-25%,b4*12-500) + MIN(c4*12-25%,c4*12-500) fills in the cell with the correct answer, but not quite the answer that I need. Let me illustrate:
The first part of the formula is to take b4 times 12 minus 25% --if this amount is less than or equal to 500 then use the amount. If it is more than 500 then use 500.
(b4*12-25%)
234.25 * 12 = 2811 – 25% = 2811
2811 - 25% = 702.75 500 Since this amount is more than 500, 500 should be used instead. ~~this is actually the amount that I want to show in the cell, the amount saved~~
2811 - 500 = 2311 ~~this is the answer that is showing up in the cell, which is not quite what I need. ~~
I want to find the answer to this question: b4 times 12 equals ?, subtract 25% or 500 from that, insert this answer into cell.
One of my biggest problems with this is that I can’t even figure out how to say what it is that I need. The formula is doing exactly what I'm telling it to do and I can't even figure out what it is that isn't working. This formula is confusing my brain…!
View 9 Replies
View Related
Dec 10, 2012
How Auto Resize (height) cell to fit text? (macros, formulas, setting)? One can make the height of the cell is reset according to the amount of text?
ej..jpg
View 4 Replies
View Related
Jan 30, 2014
I'm writing a code to adjust the height of the row based on the wraped lines. I want it to be "16.5 * (number of lines)".
First I counted the number of lines in each cell. I wrote at D1 the formula to calculate the lines at A1:
=LEN(A1)-LEN(SUBSTITUTE(A1;CHAR(10);""))+1
Then I wrote this vba code to adjust the height:
Sub AdjustHeight()
Dim x As Double
x = 1
For Each Cell In Range("A1: A11")
Cell.RowHeight = 16.5 * CELL(D"x")
x = x + 1
Next Cell
End Sub
How do I do that multiplication? How can I tell it to multiply 16,5 with the value of the cell D"x"? Is this the best way?
View 2 Replies
View Related
Jun 11, 2007
How would you adjust the network days formula so that Wednesday is considered a non-working day?
View 9 Replies
View Related
Oct 22, 2008
I have a table upon which I wish to perform vlookups, however I need to be able to adjust the range that the vlookup searches to include any data that may be added in the future without having to go into the sheets and change the vlookup.
Example
Name DOB
Tom 01/01/81
Dick 02/02/82
Harry 03/03/83
if someone then adds
Fred 04/04/84
I would like the vlookup to automatically accommodate the extra entry without needing any changes.
I've seen this done on data validation with the following formula:
=OFFSET($a$1,0,0,MATCH(REPT("z",255),$A:$A))
It only needs to do new rows, not columns.
View 9 Replies
View Related
Jun 29, 2006
I am using the =today() function to places todays date in a cell...how can I get the next to cells to automatically display dates that are 2 months and 4 months later without having to change them manually every day. FWIW, this is for an certification expiration chart.
View 4 Replies
View Related
Feb 4, 2014
I have the following formula....
=IF(B6<1,"",IF(I6<1,"PENDING",IF(J6>0,J6-I6,"PENDING")))
Where B6 = Job Number
Where I6 = Date Received
Where J6 = Date Completed
This calculates calendar days but now I need it to calculate only business days.
Not sure where within this equation I should insert the NETWORKDAYS function
View 4 Replies
View Related
Apr 6, 2009
This should be simple but I can't get it...any help appreciated. See sample attached
View 6 Replies
View Related
Oct 21, 2009
I'm working on a spreadsheet to rank stores based on how they perform in certain metrics. These metrics are weighted, and occasionally a metric for a store will get waived. I'm having trouble figuring out how to handle this without making a custom formula for each occurrence.
View 7 Replies
View Related
Jan 28, 2010
I have the a file that is downloaded from AS400. The time column is presented in 24 hours format in column D. For example : 92114 means 9.21 am. I need a formula to adjust the date in column B based on the following criteria:
1) If the time is after 2.00 pm, adjust to the next working day taking into account public holidays in column H
2) If the time is before 2.00 pm, remain the same day
3) if the time is after 2.00 pm and it is Friday, adjust to the next working day taking into account public holidays in column H
The correctly adjusted date is in column E.
View 2 Replies
View Related
Sep 17, 2010
I am trying to figure out if something here is even possible. I have an order form and a price list. I get different pricing based on the quantity I purchase. I would like to have the price auto populate based on the quantity entered. The price will be on another sheet.
I couldn't find a lot of info but I also may not know where to look.
example:
if (b2<62) then price is ($sheet2.b2)
if (b2<124) then price is ($sheet2.c2)
if (b2<372) then price is ($sheet2.d2)
Is this even possible to put into a formula?
View 6 Replies
View Related
Jan 30, 2013
I currently have a tab that is contains a macro driven query pulling data from an access database. However, I have selected the option to add new rows for new data which in turn causes my sumtotal at the bottom of the sheet to be over ridden by this new data. My question is, could there be a way to have the total row move down as new rows are added from the query (formatting and all)?
View 1 Replies
View Related
Oct 11, 2013
I've got a Table with 3 columns. (Goods, Amounts, Type). Every column has similar filter. Can I from the second and third filter remove the single item selection (red square below).
View 2 Replies
View Related
Dec 14, 2007
I am created a very simple account plan for colleagues in my team which is proving to be very poular with the exception of one thing.
I created the plan which is around 30 sheets on my laptops highest resolution setting. 1400 x 1050. Only one other colleague has this res available so of course when the rest of the team open it, it looks huge.
I need help with this as I'm still very new to excel, although learning very fast!
My preference for finding a fix for this would be to insert a scroll bar on each sheet that, when scrolled, changes the zoom of the page between a range of 50% and 100%. This may not be the best way of doing it but I'm trying to make the sheets as simple to use as possible as some of my colleagues are only just compuetr literate, let alone excel competent!
View 9 Replies
View Related
May 27, 2006
i do not know or can seem to find out is how i can program the team list in the table to adjust each team position auto-maticly relevant to each teams points scored
IE 1st 2nd 3rd 4th etc in a vertical league list
View 9 Replies
View Related
Jul 5, 2006
I have a macro on a workbook that when I hit the button it asks for the date of a register sales journal that I want to import. Then it asks for the cash in drawer amount. Then it puts in all of the data into the sales spreadsheet. Now I want to use the on time method to bypass the button and the entry of the date, and even get rid of the cash in drawer. I want to make the macro run at 9:30 every night. I want it to use the date on the computer to tell it which date to find? Here is the code I'm using! button macro
' Button2_Click Macro
Sub Button2_Click()
ImportData
End Sub
Here is the main macro
Function FileExists(sFile As String) As Boolean
Dim iLen As Integer
On Error Goto NO_FILE
FileExists = True
iLen = FileLen(sFile) ................
View 9 Replies
View Related
