Free Microsoft Excel 2013 Quick Reference

Determine Pagebreak Address Using Vba

How can I determine the row address of an existing xlPageBreakFull, using VBA.

I've searched the VBA help files, the database here at OZgrid and can't find the answer. I know the code to add pagebreaks, remove them, etc. but darned if I can work out the code to determine the location of an existing page break.

Someone, help, PLEASE

Thanks

Ted................


I'm using a pivot table to display some calculated values that represent employee utilization for each month in a year. I'm trying to determine how to use VBA to automatically, or on a button click, hide rows where all the values in the year are 0. Attached is as close of a reproduction as I can get. Basically the data is defined in decimals by the week, then I use custom fields in the pivot table to subtract the value from 1. In the month display I average the weeks, as well as display them as percentages.

Right now I've got some conditional formatting set that simply changes the font color to white for any value that is '0'. This is to remove the extraneous numbers in a quick and easy way since we don't care about anyone who has 0 availability for a given month or week. We also don't care about anyone who has 0 availability for the entire year, which is why I would like to completely hide their row if their entire year is 0.

My understanding of filtering within a pivot table is that you can only associate a value filter with one column. Therefore if I tried to use a value filter to not show 0's in the Sum of January column, the filter would remove Ned and Patrick, even though I only want to remove Patrick.

This question is similar to mine, but instead of one cell I want it to apply to the range of cells. Maybe something like IF SUM of January throught April = 0 , then hide the row? The main problem I'm having is translating that code and making it work for me. Let me know if you can help!

How can I, using VBA, determine where a pagebreak takes place?
....if it is posible.

I have a bunch of conditional formats using relative addressing that read as follows (turning on the bold font if any of these is true):

------
Condition 1
Formula Is =OR(N7=O7,N7=P7)

Condition 2
Formula Is =OR(AND(N7=6,O7=0),AND(N7=0,O7=6))

Condition 3
Formula Is =OR(AND(N7=6,P7=0),AND(N7=0,P7=6))
------

However each cell has different relative addresses that they refer to (each on their own line).

The original reason that I made the addressing relative is because I wanted to copy these formats to many other places where they had to all refer to the cells on their own line, which I have done.

But now I am trying to write code to have Excel test to see if one of these conditional formats is actually active or not, and this code evidently needs the conditional formats to have absolute addressing, otherwise the code will not work (I've tested this and it seems to be true).

In order not to have to go through all of the conditional formats and change them manually, I would like to have a VBA function that can do this automatically.

In short I would like to have a something similar to Dave Hawley's 'MakeAbsoluteorRelative' function (http://www.ozgrid.com/forum/showthre...979#post227979) that will make all of the addressing whatever I want it to be, but inside of all of the conditional formatting only (not in the cells themselves as Dave's excellent code does)?

I just want to use a macro to change the addressing format of the conditional formatting in ALL of the cells that I highlight, from Relative to Absolute or from Absolute to Relative (or any of the two combinations of relative/absolute or absolute/relative), just as Dave's macro does.

I know how to 'manually' change from relative to absolute, etc, and I know how to copy so that the references are correct. What I want to do, I could do manually but it would take a very long time and will not help me in the future. Any help would be greatly appreciated.

Is there a way to programmatically determine (i.e., using
VBA code) which rows and/or columns are outlined on an
Excel spreadsheet?

I have spreadsheets with three different groups of columns
outlined (one level of outline demotion each - no
overlap). The specific columns that are outlined change
based on the data. I would like to write VBA code to 1)
programmatically determine the groups of outlined columns,
and then, 2) reduce the number of outlined columns in one
of the groups (the third one).

Example using R1C1 reference notation: Outlined
columns: 1, 5-12, and 19-40

My goal is to reduce the third outline group by the first
three columns of the group. In this case group 19-40
would have columns 19-21 promoted by using code like
Range(Cells(1, 19), Cells(1, 21)).Select
Selection.Columns.Ungroup

It is easy to promote the columns manually, but it is a
pain to do over and over again at the end of an otherwise
automated process (VBA code) that is formatting the
spreadsheet.

For the curious - why do I want to promote three columns
programmatically? VBA code is used to extensively format
an Excel file including adding lookup table data as new
worksheets, adding formulas that use the VLOOKUP function,
a macro button and code "behind" the button to provide
sort options, borders, outlining to hide some cost history
columns and future projections columns so a window of data
around the current month data is displayed and the report
is constrained to one print page wide on 11" wide by 17"
high tabloid size paper at about 64% reduction, etc. The
data includes hours and dollars totals for labor,
material, travel, etc. A "values only" summary of the
totals rows is created as a separate (11" wide by 8-1/2"
high when printed) "Summary" sheet. Additional
calculations are added to the summary sheet
programmatically. Some columns at the right side of the
data are not needed on the summary sheet. These columns
are deleted. This leaves the summary sheet "viewable
print data" not as wide as the area available. Removing
three columns from the hidden outline of the future months
fills the page and shows more data.

The kind of outlining I am referring to has to do with how
to show or hide detail data (i.e., rows and/or columns) in
an outline. If you enter this phrase, "Show or hide
detail data in an outline", in the Help Answer Wizard you
will see how outlining is done manually.

I want to programmatically determine which columns
are "outlined" (i.e., can be shown by a click on a "+"
button in the margin of the spreadsheet outside the grid
area or hidden easily by a click on a "-" button in the
margin of the spreadsheet outside the grid area. I assume
there is a collection where this data is stored (something
like columns.outline or some such).

Thanks for any help.

I would like to send single worksheets as HTML mails to various single mail
recipients, getting their SMTP address from a table in the workbook. Can
this be done using VBA?

Hello,

I have an address string that I need to split up into three cells using vba with the mid, right, and left functions. The problem that I'm having is that I can't seem to formulate the functions in which they can handle different forms of the address.

For example,

New York, NY 23658 should return New York in one cell, NY in the next, and 23658 in the next.

New York, NY should return New York in one cell, NY in the next, and nothing in the next because there isn't a zip code.

The code I have for the first example is:

    
Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 1) = Trim(Left(CityStateZip,Find(" ",CityStateZip)
Len(CityStateZip) - 10))
            Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 2) = Trim(Mid(CityStateZip, Len(CityStateZip) - 8, 4))
            Sheet1.Cells(i, n + UBound(Selection.Value, 2) + 3) = Trim(Right(CityStateZip, 5))
This code works. I just need to amend it for the second example.

Thanks in advance,

I need to solve a number of non linear equations using VBA (specifically this is for estimation of the equilibrium composition of an gas mixture using Gibbs free energy minimization which will have at least 10 non lin. equations to solve). The problem is that the code I have (from "EXCEL FOR SCIENTISTS & ENGINEERS by E.Joseph Billo) using gaussian jordan elimination/matrix pivoting, it produces a zero pivot & thus an error when the matrix elements are divided by the pivot.

Can anyone suggest how I augment the code to overcome this problem (division by zero pivot term) or has anyone else a better code for non linear equations solver. CODE BELOW

''''''''''''''''''''CODE'''''''''''''''''''''''''''''''
Option Explicit
Option Base 1

Const ConvergenceTolerance = 0.00000001
Const IncermentNumericalDifferentiation = 0.000000001
Const Iterations = 50
Const expon = 2.718281828

Function SimultEqNL(equations, Variables, constants)
' Newton iteration method to find roots of nonlinear simultaneous equations
' Example:
' w^3 + 2w^2 + 3w + 4 = +12.828
' w.x + x.y + y.z = -3.919
' w^2 + 2w.x + x^2 = +1
' w + x + y - z = -3.663
'
' WHERE: constants = [12.828,-3.919,1,-3.663];

'On Error Resume Next

Dim i As Integer, j As Integer, k As Integer, N As Integer
Dim Nlterations As Integer
Dim R As Integer, C As Integer
Dim VarAddr() As String, FormulaString() As String
Dim con() As Double, A() As Double, B() As Double
Dim V() As Double
Dim Y1 As Double, Y2 As Double
Dim tolerance As Double, incr As Double

N = equations.Rows.count
k = Variables.Rows.count

If k = 1 Then k = Variables.Columns.count
If k <> N Then SimultEqNL = CVErr(xlErrRef): Exit Function
' Use the CVErr function to create user-defined errors in user-created procedures.
' For example, if you create a function that accepts several arguments and normally returns a string,
' you can have your function evaluate the input arguments to ensure they are within acceptable range.
' If they are not, it is likely your function will not return what you expect.
' In this event, CVErr allows you to return an error number that tells you what action to take.

ReDim VarAddr(N), FormulaString(N), V(N), con(N)
ReDim A(N, N + 1), B(N, N + 1)

tolerance = ConvergenceTolerance 'Convergence criterion.
incr = IncermentNumericalDifferentiation 'Increment for numerical differentiation.
Nlterations = Iterations

For i = 1 To N
VarAddr(i) = Variables(i).Address
' i.e. VarAddr(1) = $A$11
Next i

' Initial values
For i = 1 To N
con(i) = constants(i).Value
' Put the initial guesses into vector V()
V(i) = Variables(i).Value: If V(i) = 0 Then V(i) = 1
Next i

For j = 1 To Nlterations
' Create N x N matrix of partial derivatives.
For R = 1 To N ' n = equations.Rows.count
For C = 1 To N
' Formulastring is formula in which all but one variable in each equation is replaced by current values.
FormulaString(R) = Application.ConvertFormula(equations(R).Formula, xlA1, xlA1, xlAbsolute)
' xlA1 = Use xlA1 to return an A1-style reference. xlR1C1 = Use xlR1C1 to return an R1C1-style reference
' xlAbsolute = Convert to absolute row and column style

' ConvertFormula method used to convert cell references from A1 reference style to R1C1 reference style
For i = 1 To N
' Debug.Print FormulaString(R)
If i <> C Then FormulaString(R) = Application.Substitute(FormulaString(R), VarAddr(i), V(i))
' Substitutes new_text for old_text in a text string.
' FormulaString(R) = the reference to a cell containing text for which you want to substitute characters.
' Replace the address reference with the actual variable value i.e. $B$5^3 + 2*$B$5^2 + 3*$B$5 + 4-$R$5+3-1^3
' i = C means its on the diagonal of the matrix
Next i
' V() = vector of current variable values
If IsError(Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 + incr)))) Then MsgBox "ERROR IS FORMAULA EVALUATION"
If IsError(Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 - incr)))) Then MsgBox "ERROR IS FORMAULA EVALUATION"

' Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), (V(C) + incr)))
' ' value of the equation at the current variable figure i.e. instead of evaluating the equation at VarAddr(C) evaluate at V(C)*(1+incr)
' Y1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), (V(C) - incr)))
' A(R, C) = (Y2 - Y1) / (2 * incr)
Y2 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 + incr)))
' value of the equation at the current variable figure i.e. instead of evaluating the equation at VarAddr(C) evaluate at V(C)*(1+incr)
Y1 = Evaluate(Application.Substitute(FormulaString(R), VarAddr(C), V(C) * (1 - incr)))
A(R, C) = (Y2 - Y1) / (2 * incr * V(C))
'
' Derivatives i.e Taylor Series approx (numerical differentiation) central difference:
' y' = (y,i+1 - y,i-1) / 2h where y' = derivative of y, h = step size
' F(x+h) = F(x) + hF'(x) thus F'(x) = [ F(x+h)-F(x) ] / h
Next C
Next R

'Augment matrix of derivatives with vector of constants.
For R = 1 To N
FormulaString(R) = Application.ConvertFormula(equations(R).Formula, xlA1, xlA1, xlAbsolute)
For C = 1 To N
FormulaString(R) = Application.Substitute(FormulaString(R), VarAddr(C), V(C))
Next C
If IsError(Evaluate(FormulaString(R))) Then MsgBox "ERROR IN FORMULA FO Augment matrix of derivatives"
A(R, N + 1) = con(R) - Evaluate(FormulaString(R))
Next R

For i = 1 To N
If Abs((A(i, N + 1)) / V(i)) > tolerance Then GoTo Refine
Next i

SimultEqNL = Application.Transpose(V)
Exit Function

Refine: Call GaussJordan3(N, A, B)
'Update V values
For i = 1 To N
V(i) = V(i) + A(i, N + 1)
Next i
'Debug.Print j, "", V(1), V(2), V(3), V(4) ', V(5), V(6), V(7), V(8), V(9)
Next j
' Exit here if no convergence after a specified number of iteration
SimultEqNL = CVErr(xlErrNA)

End Function
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Sub GaussJordan3(N, AugMatrix, TempMatrix)
Dim i As Integer, j As Integer, k As Integer, L As Integer, LL As Integer, P As Integer, M As Integer, MM As Integer, MMM As Integer, MMMM As Integer
Dim pivot As Double, temp As Double, arr1() As Variant, arr2() As Variant, x As Integer, y As Integer
Dim determine As Integer, xx As Integer, yy As Integer, cntr As Integer, fudgefactor As Double
determine = 0
x = UBound(AugMatrix, 1): xx = UBound(TempMatrix, 1) ' =Number of ROWS
y = UBound(AugMatrix, 2): yy = UBound(TempMatrix, 2) ' =Number of COLUMNS' =Number of COLUMNS
' Debug.Print x, y, xx, yy
ReDim arr1(1 To 1, 1 To y): ReDim arr2(1 To 1, 1 To y)

For k = 1 To N
' Locate largest matrix element, use as pivot.
pivot = AugMatrix(k, k): P = k

For L = k + 1 To N ' loop each row

If Abs(AugMatrix(L, k)) < Abs(pivot) Then GoTo EndOfLoop

pivot = AugMatrix(L, k)

P = L
EndOfLoop: Next L ' next row
'Debug.Print pivot
' Swap rows
For j = 1 To N + 1
temp = AugMatrix(k, j)
AugMatrix(k, j) = AugMatrix(P, j)
AugMatrix(P, j) = temp
Next j

' Normalize pivot row
For j = 1 To (N + 1)
'If pivot = 0 Then MsgBox "PIVOT is ZERO"
If pivot = 0 Then Debug.Print "****",
TempMatrix(k, j) = AugMatrix(k, j) / pivot
Next j

' Do the Gauss elimination.
For i = 1 To N
If i = k Then GoTo EndOfLoop2
For j = 1 To N + 1
TempMatrix(i, j) = AugMatrix(i, j) - AugMatrix(i, k) * TempMatrix(k, j)
Next j
EndOfLoop2: Next i
For i = 1 To N
For j = 1 To N + 1
AugMatrix(i, j) = TempMatrix(i, j)
Next j
Next i
Debug.Print k, pivot
Next k
End Sub

How do I determine my computers IP address through VBA in Excel

Does anyone knows how can i do this???

Hi,

I have a situation where i have headers in a file but just because there is a header does not mean there is any corresponding data in that col. I was keying off if just the header was present then i need to do "something" but now i need to key off two things:

1) Header present
2) If there is anydata in the col when a header is present.

Question: How to use vba to determine (quickly) if there is any data in a given column.

I know how to use vba code to determine the color index of the standard 56 colors in excel. Is there a way to determine the name of these 56 colors?

Thanks,

Trebormac

How can I use vba to determine what the associated image viewing program is
?

e.g. AcdSee, Microsoft Picture Viewer etc

Regards & TIA

How to distinguish different data sets with unknown ranges?

For example: Row 1 to Row 56 belongs to Company A. And Row 57 to Row XX belongs to Company B and so on until 100 companies. For each company it has different range of data and we could not determine the number of the companies and the name as well. How could we use VBA to read and distinguish the name of the company in order to perform calculation for each company?

Hey all,

You can use the InStr function to determine the position of a character in a string. However, what if you want to determine the position of the character, which is used twice in the string? For example:

abc def & abc xyz & abc a

I would want to know the position of the first & and also the position of the second & using VBA.

Thanks for response

Hello everybody,
I am using the code below to obtain my pc's mac address. It is good at windows xp. However it is useless in windows 7. Is there anybody who can help me. Thank you all.

	VB:
	
 
    Dim objVMI As Object 
    Dim vAdptr As Variant 
    Dim objAdptr As Object 
    Dim adptrCnt As Long 
    Dim adres As String 
    adres = "" 
    Set objVMI = GetObject("winmgmts:" & "." & "rootcimv2") 
    Set vAdptr = objVMI.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True") 
    For Each objAdptr In vAdptr 
        If Not IsNull(objAdptr.MACAddress) And IsArray(objAdptr.IPAddress) Then 
            For adptrCnt = 0 To UBound(objAdptr.IPAddress) 
                If Not objAdptr.IPAddress(adptrCnt) = "0.0.0.0" Then 
                    GetNetworkConnectionMACAddress = objAdptr.MACAddress 
                    Exit For 
                End If 
            Next adptrCnt 
             'MsgBox "Your MAC Address is: " & GetNetworkConnectionMACAddress
            adres = GetNetworkConnectionMACAddress 
        End If 
    Next 
    GetMACAddress = adres 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello All:

I need help creating a VBA code that finds the last column within the print area viewed in Page Break View. The tricky part is that one of the cells in the last used column has text that overflows into 10 adjacent cells. This can be seen in the picture below (and also in the attached Excel file). The critical overflowing cell is highlighted in yellow.

book3.jpg

For my purposes, I cannot use the built-in column autofit feature. Also, for this worksheet, I have the page setup to print to 1 page wide by 1 page tall in landscape orientation.

Using VBA code, how can I determine that Column S needs to be the right boundary of my print area?

Any help is greatly appreciated!

Book3.xls

Hi,

I have a folder (C:userdatafiles) with a list of files such as:

userdata-01-01-2012.xls
userdata-01-02-2012.xls
userdata-01-03-2012.xls
userdata-01-04-2012.xls

How can I use vba to to open the file with the latest date? I cant use "last modified date" as the files could possibly be opened and changed at later dates. I need to be able to determine the latest date based upon the filename.

Many thanks

I have an Excel file in which a worksheet contains 365 identical tables except for the date.

I wish to make all these tables individual print areas i.e end up with 365 print areas.

I wish to do this automatically using VBA code.

My only attempt to was use the following code on each table using a loop.


	VB:
	
 PrintArea() 
     
    Application.ScreenUpdating = False 
     
    Worksheets("Sheet1").Activate 
     
    For t = 1 To 13141 Step 36 
         
        Range("A1")(t).Activate 
         
        ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address 
         
    Next t 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I used Step Into to view the operation of the code one line at a time.

Unfortunately as soon as the second table became a print area the first table print area was deselected
As soon as the third table became a print area the second table print area was deselected and so on.

The result of running the code was simply to make the last table a print area.

Any suggestions please ?

I am quite willing to upload a copy of the file if this at all possible.

Regards

Flynne

Hi,

I'm new to VBA programming. Hope someone can provide some tips on how to implement cell formulas using VBA codes.

I have trouble writting codes for column H and column J:
Basically, Col H is the difference of Max and Min in Col D,E,F,G,H.
Col J determines pass/fail status by comparing with specifications.

Worksheet is attached.

Thanks in advance.

What is the determinant for using VBA code, as opposed to placing formula's in the worksheet?

I am a little confused as to which way to go. Does using many formula's slow a multi sheet workbook down? or is it a safer option, in regard to not having your app tampered with by curious users, to use code?

Thanks

Hi all,
This is a great site and I have learnt alot about VBA, by reviewing this site, however I am probably asking a really silly question but I am stuck in this nifty spreadsheet with one simple thing. What I would like to do is transpose say column A to start at say address B1 and continue through to say z1. using vba, without having to address each cell.
Ie.
column A column B column C column D
A s1 s2 s3
S1
S2
S3
etc.

Thanks in advance
nounei

Hi,

I'm creating a formula for a cell using the range.address method in vba. I then use vba to copy this formula across many other cells. The only problem is that the range.address method returns a non-relative reference (ie "$A$1).

Sample code:
Code:

Is there someway to get rid of the "$" easily when returning .address?

Thanks,
Matt

I want to create a an addition to the Excel menu bar that is basically a very general menu using VBA. This menu will ultimately be a list of worksheets that I want the program to open when selected. The number of items in this menu is variable and will change over time. I have written code that will read in the the list of items to be displayed in the menu from either a text file or from another Excel worksheet. The problem is, I don't know how to create a general method to open a selected item. In general, you give a unique subroutine for each menu item to run when selected. I want to be able to adjust the number of items in the menu without re-writing any code. I could set all the menu item's associated subroutines to the same location, but I would still need some way to determine which item in the list was selected. Does anyone have an suggestions about how to either dynamically create the necessary subroutines or how to pass the menu item that was chosen to a general subroutine? If anything I've written doesn't make sense I will be happy to clarify. Any help is appreciated.

Hi there,

I'm using VBA in excel and want to do the following as I would have done in a worksheet:
I have simplified my example tremendously, but I think this is adequate.

suppose cell A1 has the number 2
suppose the cell B1 has the number 3

in cell A2 in excel I would type "=sum(A1,B1)"

in vba I want to do the same, BUT I don't know the actual cell address and I want the ability to copy to other cells using relative addressing.
So far I am able to code:
variable_x="=" & Cells(ProductLineRow(1), r1USD).Address & "+" & Cells(ProductLineRow(1), r2USD).Address

(The range(cells) just hasn't been working for me. Not sure why.)

So my equation is great, except the result is not relative. It gives me back =$A$1+$B$1 in my cell. I want to see =A1+B1.
I tried doing .address.indirect but vba doesn't like that either. Not sure of correct syntax.

Any thoughts how to do this?

Thanks.

I should preface this by explaining that I know nearly nothing of VBA. I have an issue that I'm trying to resolve, and all my clues are leading me to the impression that using VBA is the only way to accomplish my task.

I am trying to integrate the ability to launch a program with command line arguments by clicking on a cell. More specifically, I'm trying to launch vncviewer, and point it to an IP address.

the command line would look like:

c:program filesultravncvncviewer.exe 192.168.XXX.XXX

The only text the cells will have in them is the IP address to the machine with which I would like to initiate a connection, and I'm hoping theres a way to automatically pull that information from the cell and run it in the command. Is there a way to do this? Better yet, is there a relatively simple way to do this?

Thanks,

Tyler