Free Microsoft Excel 2013 Quick Reference

Equivalent of VBA Mod fn for NON-Integer arguments

My following code doesn't work:

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
, where D(count) is of type 'Double' as well.

Q1. Is it true that Excel VBA's Mod fn can only take integer arguments?

Q2. Is there an equivalent of this Mod fn that works on floating point numbers?

Thanks.


Hi all,

I'm new to vba programming (and new to this forum!). I do a little SQL and SAS programming though, and both of those languages have an "IN" function. To demonstrate:

In SQL, I can code something like:
SELECT row WHERE city IN(miami, dallas, LA, NY)

Which can also be written:
SELECT row WHERE city=miami or city=dallas or city=LA or city=NY

Is there a VBA equivalent of the IN function? For instance, if I want to check if the active cell is in column 1,2,4, or 7, can I code something like:
If ActiveCell.Column IN(1,2,4,7) Then (do something)?

or do I have to do it the long way and code:
If ActiveCell.Column = 1 OR ActiveCell.Column=2 OR ActiveCell.Column=4 OR ActiveCell.Column=7 Then (do something)

Thanks!

Okay, I am currently working my way through the April 2004 printing of
VBA and Macros for Microsoft Excel. However, I keep running into the same problem and it's starting to become extremely perturbing. On page 150 and 151 in chapter 8:Event Programming, there are two worksheet level event codes which are given as examples. My question is, how the heck do you implement these????? I can reconstruct the data, I can write the code, and I know how to do it with a different code, but it would be nice to be able to do it with a higher level of code if I am ever going to learn. Soooo, could someone please explain how exactly you go about implement these sub routines?

Don’t even know if this is the right place to ask this question, but I just received a copy of VBA and Macros for Microsoft Excel and downloaded the support files. I hit a wall right off. On page 21, preparing to record the macro, item 4 instructs to select ‘Invoice.txt.’. I had selected all files and when that brought up what I thought was all the files, guess what was missing? That’s right, ‘Invoice.txt.’. Does anyone know what I’m doing wrong, and so early in the book?

This same question was posted on 1/23 by WSIDER under “Missing Sample Files”, with no answer.

Thanks in advance.

Hi everybody, would you know an Access equivalent of the XL MOD function?
I have a field X, which is a number. I want to create 2 expressions in Access that would give me the following result: Expr1, closest multiple of 30 going down, and Expr2, closest multiple of 30 going up.
Ex: for X=183, Expr1 would be 180, Expr2 would be 210
For X=27, Expr1=0, Expr2=30
etc.
Thanks in advance...

I have an x-y (scatter) graph with non-integer, all positive x values and a
scrollbar set with an upper and lower limit. As I scroll, a vertical cursor
is moved through the chart but only to integer values. I'd like the vertical
cursor bar to move to each of the actual non-integer x values in my data as
I move the scrollbar.

Can anyone point me to VBA code or a formula solution to alter the scrollbar
so that it can handle non-integers?

My thanks in advance for any suggestions.

Cheers! Brad

Is there a VBA equivalent of ASP's 'Execute' command. I'd like to use such
a command to dynamically declare variables at run-time.

Here's a sample of ASP 'Execute' code.

---------------------------
varID = fp_rs("ID")
varName = fp_rs("Name")
varOccupation = fp_rs("Occupation")

for i = 1 to numFields
Execute("var" & arr_fieldnames(i) & " = fp_rs(""" & arr_fieldnames(i) &
""")"
next

---------------------------

Thanks,
Wayne C.

Is there a VBA equivalent of the FIND or SEARCH worksheet functions?

Thanks for your help!

Magnivy

Is there a VBA equivalent of GET.CHART.ITEM?

I have an 'area' type chart and I'd like to have two arrows that can be
moved and then based on those arrows another chart is generated just
using the values between the two arrows.

So something like this

| _____|_______ | /
| _ / | | /
| / ___/ | | /
|/ |/ ____|/____/
-----------------------------------------
1 2 3 4 5 6 7 8 9 10 11

So a new chart is generated using the values between 5 and 10.

Now after reading parts of Stephen Bullen's book
www.oaltd.co.uk/ProExcelDev I've been able to do this using pixels, but
I'd like a safer method. The GET.CHART.ITEM can do what I'd like for a
column type chart, but the link to the MS KB article is dead so I don't
know what all GET.CHART.ITEM takes.

Anyways, is there a better way to do this than pixels? Will
GET.CHART.ITEM work? Does somebody have documentation for
GET.CHART.ITEM?

Thanks
James

Hi,

I have a worksheet for working out percentages for College Basketball spreads in relation to handicaps and in short I am looking to minimise the amount I need to do manually.

In short all I am struggling to make work is how it determines the percentages for calculating a tie.

If the Handicap equals a non-integer (e.g. 1.5 - it's usually a half of something) the percentage = 0%.

I have the handicap in cell A8. What I am struggling to think of is a suitable IF criteria to force this. The cell for the tie% is B11 and the Game Power Rating difference needs to be positive which I have forced in C8.

If the handicap in A8 is an integer the formula I will add is:

NORMDIST((A8+0.5),C8,10,TRUE)-(NORMDIST((A8-0.5),C8,10,TRUE))

I've probably missed a bracket somewhere above but am confident in the formula.

I am thinking the easiest way of doing this is through an IF function but cannot think of a suitable IF criteria to sort determine an integer from a non-integer.

The rest of the sheet I have reduced down to the point where you just need to add team names, ratings and a handicap.

I'd be grateful for any advice anyone can offer (and am happy to attach the sheet).

Thanks in advance,

Hello,
I'm trying to calculate the average for a specific, variable number of cells in a column. The number of cells to be calculated is given by a value in another cell.
Result should look like the following:
e.g. cell value = 4
So first 4 cells (non-blank) of column A used for average calculation. The result is written in column B.
I'm looking for VBA code. Thanks!

Just a note to let you know that I have been tasked with a project that has been time-consuming, trying to teach people how to maintain an XL database, data integrity, then generate reports (through Pivot Tables, then reformatting, etc.) - all the while tyring to actually do it. Not a pretty sight!

However, today I had time to step back and look at the reports, the Pivot Tables, and the needs. By following the concepts in Chapter 12 in VBA and Macros for MS Excel I have been able to already complete half of the project, just building code step by step. I plan to finish the rest today/tomorrow, and then let the non-XL users try it out, see if they can break it.

So, thank you, thank you, thank you!

Hi there,

I had a familiar question the other day - is it possible to have four (cell shading) conditional formats?

Naturally, thinking of VBA (click here for Dave's solution), I said yes, but then I thought of a non VBA way.

Here is the scenario...

The shading of a cell had to be either red, orange, green based on three criteria, else be lavender.

...and so I did this

coloured the cell lavender and then simply used Excel's Conditional Formatting to meet the other three criteria. This meant that if none of the three conditions were meet, the default (lavender) colour resulted.

HTH

Robert

My spreadsheet needs VBA code edit, to send EMAILS to rows, that are NOT BLANK in the worksheet RESULTS, excluding the HEADER.

See sample file http://www.srands.co.uk/exoftable3.xls

Issue1: To get VBA code working edited (See code below, 9th line, For r = 2 To 2 'Needs editing so that data in 'not blank' rows only is included. Also r = row, not to be confused with column r) to return email for only the 2nd row (Row after HEADER) entry only, on the RESULTS page. However the number of not blank rows is a variable, and will depend upon the rows that meet the criteria in 'WORKSHEET' in column U.

Issue2: If I expand the row range to a full page upto row 51, many BLANK emails are generated (Because of blank rows, the auto-generated fields would be BLANK).
I don't know what VBA code to use instead though.
In formula's for the 'RESULTS' page I would use a command that checks if the row is not blank, something like =IF(AND(A2=0),"",'email command')WHAT IS THE VB EQUIVALENT OF SOMETHING LIKE THIS?
For this spreadsheet the number of RESULTS will be unknown depending on the information/data available, hence I want to include NOT BLANK entries from rows 2 to 51.

PURPOSE OF DESIRED SOLUTION:
CODE NEEDS EDITING, JUST TO COUNT NOT BLANK ROWS IN THE WORKSHEET 'RESULTS':


	VB:
	
 ShellExecute Lib "shell32.dll" _ 
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ 
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ 
ByVal nShowCmd As Long) As Long 
Sub SendEMail() 
    Dim Email As String, Subj As String 
    Dim Msg As String, URL As String 
    Dim r As Integer, x As Double 
    For r = 2 To 2 'Needs editing so that data in 'not blank' rows only is included
         '       Get the email address
        Email = Cells(r, 10) 
         
         '       Message subject
        Subj = "Your car for sale.  " & Cells(r, 1).Text & "." 
         '       Compose the message
        Msg = "" 
        Msg = Msg & "Dear " & Cells(r, 11) & "," & vbCrLf & vbCrLf 
        Msg = Msg & "I like your car, the " & Cells(r, 1).Text & "." & vbCrLf & vbCrLf 
        Msg = Msg & "Please call me back.  " 
        Msg = Msg & "It is " & Cells(r, 2).Text & "." & vbCrLf & vbCrLf 
         
        Msg = Msg & "Cheers " & vbCrLf & vbCrLf 
        Msg = Msg & "Stephan Rands" & vbCrLf 
        Msg = Msg & "07772000679" & vbCrLf 
        Msg = Msg & "[EMAIL="mail@srands.co.uk"]mail@srands.co.uk[/EMAIL]" 
         
         
         '       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") 
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") 
         
         '       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg 
         '       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus 
         '       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02")) 
        Application.SendKeys "%s" 
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
See sample file http://www.srands.co.uk/exoftable3.xls

TheWORKSHEET 'RESULTS', has the VISUAL BASIC code called 'Send EMail'. Obviously to view 'RESULTS' worksheet VISUAL BASIC code, View, Tools Bars, Visual Basic, then on the Toolbar press the play symbol (R/H arrow), Step into.

Or to play the MACRO of the rows that meet all criteria in 'WORKSHEET', shown in 'RESULTS', View, Tools Bars, Visual Basic, then on the Toolbar press the play symbol (R/H arrow), Run.

Cheers

Stephan

Cross threads:
http://www.excelforum.com/excel-prog...html?p=2762981
http://www.mrexcel.com/forum/showthr...18#post3122118

Does VBA support an equivalent to C's "#define" for inline replacement?

For example I have a section of code, where a tollerance is allowed, and the tollerance could be either a fixed tollerance or a percentage tollerance, that I would like to be able to change at a later date:

Dim Overall_Diameter as Long
Dim Max_Diameter as Long

<some code>
Overall_Diameter = 3.45 * some_other_variable
Max_Diameter = Overall_Diameter TOLLERANCE
<some code>
Then at the top of the file I could have:
Which would cause the line in the original code to be evaluated as:
Alternatively I could have at the top of the file:
Which would cause the line in the original code to be evaluated as:
Is something like this possible with VBA?
Otherwise I can only see doing it with booleans and a 'value' parameter, so requiring code something like this:

Const TOLLERANCE_IS_FIXED as Boolean = TRUE
Const TOLLERANCE as Long = 100
or

Const TOLLERANCE_IS_FIXED as Boolean = FALSE
Const TOLLERANCE as Single = 0.05
Then a 'main' code of:

Dim Overall_Diameter as Long
Dim Max_Diameter as Long

<some code>
Overall_Diameter = 3.45 * some_other_variable
if (TOLLERANCE_IS_FIXED)
  Max_Diameter = Overall_Diameter + TOLLERANCE
else
  Max_Diameter = Overall_Diameter * TOLLERANCE
end if
<some code>
but as I have several of these tollerances for different things, and each one is in multiple places in the file the first method would be tidier and easier to up-keep..... if it is possible?

Thanks

XL 2000
Can anyone point me to a bit of VBA that checks a user's PC for the
existence of a DSN?

Cheers

GPO

What's the VBA/macro equivalent of Control-Home? When I do the Record Macro thing, it yields "Range("A1").Select". Which is great, but it doesn't move the spreadsheet to show the upper left-most portion ...

Thanks for any assistance available.

Hi,

I have a table of x and y values and I wish to be able to look up an x value and get the corresponding y value.

However the x values I look up will not necessarily be the same as the ones in the look-up table so I would like to be able to linearly interpolate between them and get a linearly interpolated y value. This I have seen done before.

However I also need the table to work for irregularly spaced, non-integer values in the look-up table.

For example:

x_________y
34________54
35________56
76.9976___76.6798
85.78_____83.653
99.7897___78.678

e.g. I wish to look-up y values for
x________________y
34.5_____________55 etc
81.678
93.456
38.577

Thanks,

T

Hay

I hope that you will understand my problem

I have a IF formula that check if “F13” cell is "+" if not do calculation (F13-E13) and stop, if yes go to the previous line and check if the “F12” is "+" if not do calculation (F12-E12) and stop, if yes continuo to the previous cell and so on until you find cell without “+”

The EXCEL IF formula is
=IF(F13="+",IF(F12="+",IF(F11="+",IF(F10="+",IF(F9="+",IF(F8="+",E8-F8),F9-E9),F10-E10),F11-E11),F12-E12),F13-E13)

I'm looking for VBA code with FOR to run and do the same without the limitation of 7 if inside if.

Thanks in advanced
Shark Man

Is there an equivalent of the continue used in javascript to skip a for loop?

I have a number of if then conditions to be met and rather than nest them I would prefer to skip to the next loop if the condition isn't met.

ie:

For n = 1 to 10

If (condition1 = false) Then
' skip anything that doesn't meet 1st criteria
' this is where I would use continue in javascript
GoTo nextloop
End If

' If it gets to this point I know 1st condition has been met

If (condition2 = false) Then
' skip anything that doesn't meet 2nd criteria
GoTo nextloop
End If

' If it gets to this point I know 1st and 2nd conditions have been met

nextloop:
Next n

Hello all,

I am currently employing a conditional format to a column
of cells, whose text colors are contingent on a set of conditions

=IF((J98<>"")*(J98<>" ")*(ISNA(MATCH(J98,$J$1:J97,0))),FALSE,TRUE)
'Magenta strikethrough text

=IF(AND(F98=1,G98<>"Yes"),TRUE,FALSE)
'Red Text

=IF(OR(A98="Table 7",A98="table 7",A98="Table7",A98="table7"),
TRUE,FALSE)
'Regular, black text

Unfortunately, if any user selects a cell they want to edit in this
column (this is supposed to happen) without double-clicking on it
(hence the cell is active, but the cursor is not) & pastes data in - it
removes all of the conditional formatting for that cell.

I figured that the most sensible way around this is to write VBA
code that would handle this, but I'm not sure how to translate the
above conditions into VBA. Any help on this would be much appreciated.

Thanks.

I am working on interpolating some numbers. I have a column of names, 1 column of an incomplete set of numbers, and a 2nd column of complete numbers. I need to predict values for the incomplete column of numbers using the surrounding non-linear set of data.

For instance, the "Column 1" value for "Test 4" needs to be predicted based on B2:B8 and C2:C8. (see file)

Setting up the Forecast() function for every interval works, but is very time consuming. I need a formula that will be able to find the correct interval. This data is not linear, so I need it to be using the correct intervals.

Please see attachment for more information

Any help would be greatly appreciated! Thanks!

Hola,
I know how to find the reminder of two given numbers - however the VBA Mod function is limited in returning only integers.
I thought I can use the worksheet MOD function but I could not.
In a sheet, when I try: =MOD(12.34,1) I get: 0.34
In VBA - when I try: B = WorksheetFunction.Mod(12.34, 1) I get error 438.
Why ?
I am aware of using: B = 12.34 - Int(12.34) but I wonder about MOD.
Thanks, Al

Hi all, first time posting here so go easy please. I've searched around for the type of formula I think I need, but I can't seem to either search for the right phrasing or I might have an incorrect idea of going about it. Basically this is the setup:

I have two sheets (Sheet1, Sheet2).

Sheet 1 contains 2 columns of information/numbers (1.1 and 1.2)

Sheet 2 contains the 1 column I'm interested in having the formula (2.1).

In columns 1.1 and 1.2, I have integers as well as non-integers (rational and irrational numbers). These integers and non-integers are in a semi-continuous series (always increasing, never decreasing). An example would be 1, 4, 7, 7.8, 10, 12.35, 15, etc. with column 1.2 being a staggered version of column 1.1 (in place of 1, it starts with 4, then 7, 7.8, 10, 12.35, 15, etc.).

In column 2.1, I need a formula that will examine the cell above the cell containing the formula (say the formula is in A3, have it examine A2) and compare A2's content to columns 1.1 and 1.2. If it can find the number, have it +1 to A2's content, if not, have it compare A2 to A1+1. If it is true, then again +1 to A2's content. But I also need it to somehow fill in the gaps of integers and recognize when there is a non-integer close to A2's content to substitute the non-integer into the cell, i.e. from 1 to 4 the formula need to fill in for 2 and 3 in the cells.

Ideally A1=1, then A2 should = 2, A3=3, A4=4, etc until you hit 7.8. I need A8=7.8 somehow, and then for it to resume the "integer filling" with A9=8, A10=9, A11=10, etc. until it hits 12.35 where it pulls that instead (A14=12.35).

I'm trying an argument with the IF, ISNUMBER, and MATCH functions, but I'm not sure how to do the "integer filling" and non-integer referencing. I either end up with none of the non-integers being referenced (A8 would just be 8, A14 would just be 14, etc) or I end up with a reference to the relative cell location (A8 = Sheet1!A8 which is not what I need) without the "integer filling" between cells.

I know this all sounds very convoluted, but I've attached an example. I'm trying to use this formula for a large group of data (say 120 - 180 different integers and non-integers), at least it's large to me. Any insight would be greatly appreciated.

Hi,

I have a worksheet that imports data from a text file which vary's in size each time from 100 to 1000 rows. I want to subtotal only rows with data. The current macro I use has a set range up to 1000 rows which creates the grand total at the bottom of the page and requires deletion of the blank rows.

Is there a way for VBA to check for non-blank rows instead of the pre-defined range and set the sub-total function to only calc those rows?

Thanks for any help on this.

KC