Basic Use Of IF Function/Formula
Jun 4, 2008In the formula =IF(" cell # "","") what does the "","" mean
View 4 Replies
ADVERTISEMENT
Tap Into Access Visual Basic Function
Sep 9, 2008Is it possible to have Excel tap into an Access database and call up a function?
I have an Excel sub that looks like so
Basic IF Function With Multiple Logical Tests
Jul 10, 2014Basically, I'm trying to calculate a pooled z-score from z-scores already stored in different cells.
Each individual z-score are calculated like this :
D1=IF(A1=999,999,((A1-10)/3)).
They are derived from standard scores with a mean of 10 and a SD of 3. I use 999 to mark missing values and this work flawlessly for single z-score cells.That way, if A1 is a missing value (999), Excel returns a value of 999.
Now here's my problem. How do I tell Excel to ignore 999 values if I am pooling multiple z-scores together?
Let's say A1, B1 and C1 are single z-scores. I want D1 to be the pooled z-score result, but I want it to ignore A1,B1 and/or C1 if their value is 999. For instance, if A1=+2.0 , B1 = 999 and C1= -1.50, I want my D1 cell to ignore B1 in the calculation.
I've tried : D1=IF(A1,B1,C1=999,999,(LC5+LI5+IK5)/3)) but it sure doesn't work...
Basic IF Function. Return Cell Value Or Show Blank
Mar 13, 2008For example if I were to take the data in cells d3($358), d4($321), d5($130), d6($82) and skip a cell to now make the same data into cells d3, d5, d7, d9. Next, the data in cells f3, f4, f5, f6 would be merge into the blank cells of d4, d6, d8, d10.
The final result would look like column B. I have over two years of sales data in two separate columns that I need to merged into one column. Is there easier way without a simple cut and paste one cell at a time as this would take an enormous amount of time to complete? I’m not that good with VBA codes so a formula works good, but if VBA is the way to go then tell how to enter it on my worksheet.
Microsoft Visual Basic: Compile Error: Sum Or Function Not Defined
Mar 24, 2006I receive: "Microsoft Visual Basic Compile error:
Sum or Function not defined" after I have run a macro. I recorded the macro.
The macro code is: ..
Formula To Decrease A Margin, In Connection With Increasing Basic Value
Oct 8, 2008I'm trying to build a formula to form a price-list. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B). Example:
Basic value is $50, my price is $75 (50% margin)
Basic value is $100, my price is $130 (30% margin)
Basic value is $150, my price is $172,5 (15% margin)
And so on...
I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:
Using VBA Code In Visual Basic: Develop A Stand Alone Program In Visual Basic
Jun 3, 2006I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
Inserting New Line With ONLY Formula From The Line Above - Basic VBA / Macro
Apr 30, 2013I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
Basic Workbook And Mac
Mar 13, 2007I have been working on an app for a while now and it works for all our PC users. We have some folks in corporate who have macs and the thing just falls on it's face. So, I have taken out the vba on the thisworkbook start up bits in hopes that it can just open and go from there. This is also problematic. So in search on the web, it looks like Macs do not like VBA. So I decided that since the main page entry is just standard formulas, I would just remove all the modules, forms and macros out of the workbook so the whole problem side could be avoided. Well no such luck. When I open it, it asks about macros. If I disable them, it tells me that the macro can not be disabled in 4.0. I built the app in 2000 as it is the lowest version in the office. But this really shouldn't matter because there are no macros in it to start with.
So, my question is... How can I make a very basic, mac safe version from my PC version. I can draw up some VBA to open the mac version and suck in the info I need and save it to PC land.
Basic Check Box
Jul 18, 2007I am using a user form in excel VB.
I have read from the help file and online that in order to tell if a checkbox is checked or not you use the code
chk1.CheckBoxState
When I click the checkbox it doesn't work, says data memeber not found
if it helps i am using the code in the ch1.click sub, if that matters
(I want to make if so if the check box is clicked, some text boxes are no longer enabled, the only thing i have trouble wth is the fact i can't tell if its been checked or not)
Basic And Add-ins Information
Oct 3, 2006Need little bit of information concerning excel. Where is the whiteboard located. Second, how can i highlight a cell or multiple cells. Finally, i need the Data Analysis Plus add-in. Any idea where can i download in from.
View 3 Replies View RelatedBasic Functions
Mar 28, 2007I used to use VB and VBA years ago for fun, now I have a project at work and I havent touched it since 1999 at least. I am trying to program a function that I can call and pass it a text variable. It will search through excel for that text, and return a cell as a Range. Thats where I run into problems. Heres the
Function lookFor(text As String)
lookFor = Columns("B:C").Find(text, After:=rCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Address
End Function
'...then later in the code...
Private Sub search_idBTN_Click()
'...
Dim rCell As Range
Set rCell = lookFor(searchTXT)
'...
End Sub
I think it has something to do with the variable type, its expecting a range and it gets a string or vice versa or something.
2007 - Visual Basic
Jan 23, 2008how I can get to the visual basic toolbar in excel 07. In previous versions there was a visual basic toolbar and it allowed you to add buttons, ect. Where is this in 2007?
View 9 Replies View RelatedPut Together A Basic Spreadsheet To Track
Sep 15, 2008I'm trying to put together a basic spreadsheet to track offensive plays for high school football. I'd like a formula that will change the LOS (Line of Scrimmage) to reflect the yards gained on the previous play. The problem is that you'd probably need negative numbers up to 49, and then back down to 0.
View 10 Replies View RelatedBasic Password Protection
Oct 25, 2009I want to be able to put password protection on a button in a spreadsheet. It doesn't need to be secure, as it's just to prevent accidental changes by 4 year olds.
My plan was to store the password value on a hidden worksheet, as I don't think there's a way to retain the value of a VBA variable when a workbook is closed. Is there a neater way to do it?
Incorporating An IF Statement To Basic SUM
Dec 3, 2009I have the below formula to make a simple calculation:
=SUM(AC1880+AJ1880)
I would like to adapt the formula to ask IF AG1880 is empty or not. If AG1880 is empty then I would like the formula to NOT make the calculation, but instead return "--" (nothing).
If it contains information, then I want the calculation (AC1880+AJ1880) to be made. If AG1880 contains information it will be text in a text format.
Visual Basic And Images
May 6, 2006Is there a code you can type in to VBA to allow a picture in a spreadsheet to change when a parameter is specified. For example i have a picture of a timber beam i drew in excel. and i wondered if i could set it up so that when the user inputs the length of beam they are using, the diagram alters to show the length specified.
View 2 Replies View RelatedVisual Basic To Access Web
Aug 4, 2007My company "makes widgets". Each "type of widget" is associated in our internal database with a record locator (five alpha characters). Our company website can also use that record locator to pull up a webpage specifically dedicated to that widget. In addition to the webpage itself, the website also creates an expanded URL which contains additional information about that widget.
I'd like my macro to take a list of record locators, open up the company website related to each specific widget, pull back the expanded URL, and do some text management with the URL to populate other parts of the spreadsheet.
For example, if my record locator is "ABCDE", my macro would go to the website, "http://www.widgets.com/ABCDE". The site then redirects to an expanded URL: "http://www.widgets.com/whole-bunch-of-data-about-the-ABCDE-widget". I'd hope to write a macro which pulls back the expanded URL and does some magic with the "whole bunch of data" part. I've been told that the MSHTML library would be useful for a project of this type. I've been able to connect my spreadsheet to that library, but then I'm not sure what to do with it.
Vlookup Formula And Function To Combined With Another Formula?
Oct 22, 2013i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow VLOOKUP(B4:B225,'D:Salary[Data.xls]AUG'!A$1:F$65536,6,FALSE))
the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.
Data files will contain of these:
a:Employee ID
b:employee name
c:Employee salary/Overtime
Master files will contain of these:
a:Employee ID
b:Emplyee Name
c:Employee Nett Salary (that will be dragging from Data files)
is there any formula that i can used to combined with my vlookup formula?
Basic Syntax / Type Mismatch
Jul 15, 2014I am trying to accomplish the following: iterate through rows of a selection, delete row if the first column cell is empty or the second column cell equals 0.
So far I have:
[Code] ........
I am getting a type mismatch error at my if statement.
Lost Visual Basic Project
Oct 24, 2008Have a project going and have had a disturbing trend develop. I'm working in Excel 2003. Upon opening the file and enabling macros, I started receiving File/Path errors, at which point it would shut down the application. When opened, I have a splash that would come up, but it wouldn't go to the next userform. At this point I would get the error message and the file would be closed. If I then reopened the file, disabled macros, saved under a different file name, and then reopened with macros enabled, everything worked fine, until I shut it down and then tried to reopen, at which point the same error would occur.
Lately I have simply been getting the Microsoft Excel dialogue box telling me an unexpected error occurred and it had to be shut down. I'm at a loss for what is causing this. The last time this happened, when Excel recovered the file, it told me a repair had been done, and the repair was to basically delete all of the VBA code from the file.
Visual Basic Compiler - Odd Performance
Nov 23, 2009I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.
View 4 Replies View RelatedChanged Cursor While Using Visual Basic
Jan 6, 2010I'm sure this is something I've accidently pressed while using VB. My text cursor has changed from the usual 'I' shape to a black box shape. Now instead of adding text where I'm typing it now types over previous work. Is there an easy fix for this.
View 2 Replies View RelatedHow To Pass Variable Value In Visual Basic
Sep 26, 2013How do you pass a variable value in Visual Basic?
I have a variable called LastRow
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Would like to pass the value in my Do While loop
Option Explicit
Sub IncValue()
Dim MyCell As Range
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Do While MyCell
Basic VBA Userform Action Chooser
Nov 30, 2006i've been searching for nearly two hours and can't find a site with an example of this.
I just want to know the code to create a userform that calls various macros based upon which radio button is selected.
ie. IF number1 selected then call macro1()
IF number2 selected then call macro2()
Microsoft Visual Basic Error 400
May 2, 2007My code here...
Sub ClearSheet()
Worksheets("Summary").Activate
If Range("CustInfo") = False Then
Range("ICompany, IPhone, IFax, IContact, ICell, IEmail, IAddress, IPOBox, ICity, IState, IZip").Select
Selection.ClearContents
Else
Range("IJobDescription").Select
Selection.ClearContents
End If
For I = 1 To 5
Range("Qty" & I).Select
Selection.ClearContents
Next
End Sub
keeps giving me an error 400. I have reinstalled xl just to see if it was related toan error I recieved earlier relating to VBE6.dll. This still works in another workbook but it doesn't work in this one. All I did was change the Range names and cleaned it up a bit.
Table Showing A Basic RSA Algorithm
Apr 8, 2008I wonder could anyone help me with a problem i'm having, I have a table showing a basic RSA algorithm in excel, for example,
M 13 2197 19 8893887173 13 M
N 14 2744 5 788125 14 N
etc etc,
How the numbers are derived isn't important, what i want to do is create a visual basic form, to allow a user to input a letter, for example M in a text box, click a button, and then the 5 related digits are shown.
So the user inputs M into a text box clicks a button, then 13, 2197, 19, 8893887173 and 13 is output to 5 other text boxes.
Basic Random Number Generator
Oct 9, 2008how to create a basic random number generator within an excel spreadsheet?
Basic Lower limit = 0
Basic Upper limit = 100
Visual Basic Opens Automatically
Oct 19, 2009I have a VB problem, and it's driving me nuts.
Whenever I run a macro on excel the Microsoft VB editor opens and shows me what the macro looks like. It also runs the macro.
Now, I would very much like it to keep running macros, but I really don't need to see my code every time. It gets really annoying, and I need to run about 40 or 50 different macros on one sheet. How can I make it go away?
PS! It's Excel 2007 for students, Norwegian version.
Basic Dart Scoring Spreadsheet
Jan 15, 2010I'm creating a basic Dart scoring spreadsheet, and its been going absolutely fantastic, but then I was asked to create the Sets and Legs, thats when I got stuck.
I'm looking for a formula (or anything) that will add a Set when the Legs reach the number 3, and then again but make it 2 if that player gets another 3 legs. If you know darts you will understand what I mean.
