My following code doesn't work:

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.

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

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.

- Vba equivalent of SQL's IN function?
- VBA and Macros for Microsoft Excel Question
- Problem with VBA and Macros for Microsoft Excel book.
- Equivalent of MOD function in Access query expressions?
- Non-integer scrollbar
- VBA Equivalent of ASP 'Execute' Command
- VBA Equivalent of FIND or SEARCH Function
- VBA equivalent of GET.CHART.ITEM
- IF Formula Query for Integers and Non-Integers
- Excel VBA Average calculation for variable length
- VBA and Macros for MS Excel - Chapter 12 PivotTables
- Fourth Non Vba Conditional Format
- Email automatic in Excel with VBA to edit
- '#define' equivalent in VBA?
- VBA to check for existence of a DSN
- Macro equivalent of Control-Home is -- ?
- Interpolate non-integer values in a look-up table
- VBA Code for non easy IF Formula
- VBA equivalent of Javascript continue
- VBA equivalent of match
- Predicting values for non-linear set of data
- VBA MOD function
- Referencing a non-integer or generating a consecutive integer
- Subtotal Ranges Of Varying Size

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!

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?

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

Thanks in advance.

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...

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

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

Thanks for your help!

Magnivy

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

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,

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!

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!

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

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:See sample file http://www.srands.co.uk/exoftable3.xlsShellExecute 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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: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?

Which would cause the line in the original code to be evaluated as:Then a 'main' code of:

Is something like this possible with VBA?or

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 = 100Const TOLLERANCE_IS_FIXED as Boolean = FALSE Const TOLLERANCE as Single = 0.05Dim 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>

Thanks

Can anyone point me to a bit of VBA that checks a user's PC for the

existence of a DSN?

Cheers

GPO

Thanks for any assistance available.

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

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

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

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.

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!

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

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.

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